在多个条件查询，怎么动态生成sql语句

[party620]

有3个查询条件，如果用if判断的话，得进行8次if判断，有没有什么方法，可以根据条件动态生成sql语句

[Ryan1221]

如果有3个查询条件，就为3个查询条件都赋个默认值，这个默认值不影响查询结果，如果查询条件存在则替换默认值

[Leon_He2014]

3个条件为何是八次？ 楼主可以用动态sql的方式。

declare @str varchar(1000)='';
declare @i int =0;
set @str='select * from master.dbo.spt_values where 1=1'
if @i=0
set @str=@str+' and [type]=''p'''

Exec (@str)

[昵称90天可改一次]

详细点 发出来

[party620]

引用 1 楼 Ryan1221 的回复:

如果有3个查询条件，就为3个查询条件都赋个默认值，这个默认值不影响查询结果，如果查询条件存在则替换默认值

都有默认值“-全部-”，但这个默认值不在值里面

[party620]

(CraneInfo为表明；ctype,Company，RegionId为表字段)

if exists (select * from sys.objects where name='up_getCraneInfoSource')
drop proc up_getCraneInfoSource
go
create proc up_getCraneInfoSource
@type varchar(255),@firm varchar(255),@province varchar(255),@regionId varchar(255),@start varchar(255),@end varchar(255)
as
if (@type='-全部-' and @firm='-全部-' and @province='-全部-')  --加载页面数据源
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm='-全部-' and @province='-全部-')  --按类型查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where ctype=@type)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm!='-全部-' and @province='-全部-')   --按公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province ='-全部-')   --按类型，公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按类型，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按类型， 公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and  Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
	

[party620]

引用 2 楼 stublue 的回复:

3个条件为何是八次？ 楼主可以用动态sql的方式。

declare @str varchar(1000)='';
declare @i int =0;
set @str='select * from master.dbo.spt_values where 1=1'
if @i=0
set @str=@str+' and [type]=''p'''

Exec (@str)

你看下我的sql语句（在5楼），查询条件可以同时存在，而你给我的应该是只能按一个条件来

[Leon_He2014]

引用 6 楼 lyx_1094562530 的回复:

[quote=引用 2 楼 stublue 的回复:] 3个条件为何是八次？ 楼主可以用动态sql的方式。

declare @str varchar(1000)='';
declare @i int =0;
set @str='select * from master.dbo.spt_values where 1=1'
if @i=0
set @str=@str+' and [type]=''p'''

Exec (@str)

你看下我的sql语句（在5楼），查询条件可以同时存在，而你给我的应该是只能按一个条件来[/quote]

declare @str varchar(1000)='';
declare @i1 int =0;
declare @i2 int =0;

set @str='select * from master.dbo.spt_values where 1=1'
if @i1=0
set @str=@str+' and [type]=''p'''

if @i2=0
set @str=@str+' and low >100'

--多少个条件就多少个if
Exec (@str)

[zhainanchunchun]

很想说一句 干嘛不在代码里封装查询条件呢？ 一定要放在数据库中吗？

[csl_1022]

动态拼接sql语句吧 2楼已经给出答案了

[csl_1022]

declare @sWhere varchar(4000) set @sWhere = '' if (rtrim(ltrim(@No)) <> '') begin set @sWhere = @sWhere + ' and GoodsNo like ' + '''' + '%'+ @No + '%'+ '''' end if (rtrim(ltrim(@Name)) <> '') begin set @sWhere = @sWhere + ' and GoodsName like ' + '''' + '%'+ @Name + '%'+ '''' end set @sWhere='select * from table where 1=1 '+@sWhere exec(@sWhere) 给你一段例子 参照这个写吧

[Andy__Huang]

引用 5 楼 lyx_1094562530 的回复:

(CraneInfo为表明；ctype,Company，RegionId为表字段)

if exists (select * from sys.objects where name='up_getCraneInfoSource')
drop proc up_getCraneInfoSource
go
create proc up_getCraneInfoSource
@type varchar(255),@firm varchar(255),@province varchar(255),@regionId varchar(255),@start varchar(255),@end varchar(255)
as
if (@type='-全部-' and @firm='-全部-' and @province='-全部-')  --加载页面数据源
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm='-全部-' and @province='-全部-')  --按类型查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where ctype=@type)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm!='-全部-' and @province='-全部-')   --按公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province ='-全部-')   --按类型，公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按类型，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按类型， 公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and  Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
	

不需要写得那么麻烦

select * 
from 
(select *,ROW_NUMBER() over (order by ccode) as num 
from CraneInfo 
where Ctype=case when @type<>'-全部-' then @type else Ctype end
	and  Company= case when @firm<>'-全部-' then @firm else Company end
	and RegionId=case when @province <>'-全部-' then  @regionId else RegionId end
)as craneInfo2 
where num between @start and @end

[软件高手_2016]

引用 12 楼 hdhai9451 的回复:

[quote=引用 5 楼 lyx_1094562530 的回复:] (CraneInfo为表明；ctype,Company，RegionId为表字段)

if exists (select * from sys.objects where name='up_getCraneInfoSource')
drop proc up_getCraneInfoSource
go
create proc up_getCraneInfoSource
@type varchar(255),@firm varchar(255),@province varchar(255),@regionId varchar(255),@start varchar(255),@end varchar(255)
as
if (@type='-全部-' and @firm='-全部-' and @province='-全部-')  --加载页面数据源
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm='-全部-' and @province='-全部-')  --按类型查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where ctype=@type)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm!='-全部-' and @province='-全部-')   --按公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province ='-全部-')   --按类型，公司查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and Company=@firm)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm ='-全部-' and @province !='-全部-')   --按类型，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type ='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
else if(@type !='-全部-' and @firm !='-全部-' and @province !='-全部-')   --按类型， 公司，区域查询
begin
	select * from (select *,ROW_NUMBER() over (order by ccode) as num from CraneInfo where Ctype=@type and  Company=@firm and RegionId=@regionId)as craneInfo2 where num between @start and @end
end
	

不需要写得那么麻烦

select * 
from 
(select *,ROW_NUMBER() over (order by ccode) as num 
from CraneInfo 
where Ctype=case when @type<>'-全部-' then @type else Ctype end
	and  Company= case when @firm<>'-全部-' then @firm else Company end
	and RegionId=case when @province <>'-全部-' then  @regionId else RegionId end
)as craneInfo2 
where num between @start and @end

[/quote] 感觉这么写非常好