怎么根据if条件 来加where条件。比如说selec*...... ............ .... ....... form user if(@name='') begin where age=@age end else if(@age="") begin where name=@name end else begin where name=@name and age=@age end这个意思,不知道怎么实现
String sql="Select * from 表名 Where 1=1 "; if(@name!=null) { sql+=" and name= '"+@name+"'"; } if(@age!=0) { sql+=" and age="+@age; } ...... 看明白了吗
declare @sql varchar(2000) set @sql='selec*......form user' if(@name='') begin set @sql=@sql+'where age=@age' end else if(@age="") begin set @sql=@sql+'where name=@name' end else begin set @sql=@sql+'where name=@name and age=@age' end exec(@sql)
if 1=1 begin select * from t where X=@y end else if 1=0 begin slect * x wehre y=@x end
而且代码量非常庞大,只是where条件不同
额 SORRY 晕了
意思是能否通过判断,动态生成Where条件啊
不知道可不可以create procedure selectInfo @name,@age begin if name='' select * from user where age=@age else if age='' select * from user where name=@name else select * from user where name=@name and age=@age end
create procedure selectInfo @name char(10),@age int begin if name='' select * from user where age=@age else if age='' select * from user where name=@name else select * from user where name=@name and age=@age end
/* 定义表结构 */ DECLARE @USER TABLE(ID INT,Name NVARCHAR(20),Age INT)/* 构造表数据 */ INSERT INTO @USER(ID,Name,Age) SELECT 1,'NAME1',21 UNION ALL SELECT 2,'NAME2',22 UNION ALL SELECT 3,'NAME3',23 UNION ALL SELECT 4,'NAME4',24 UNION ALL SELECT 5,'NAME5',25/* 构造查询参数 */ DECLARE @NAME NVARCHAR(20),@AGE INT/* @NAME为NULL,@AGE不为NULL,按照@AGE查询 */ SELECT @NAME = NULL,@AGE = 23 SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* @NAME为NULL,@AGE不为NULL,按照@AGE查询 */ SELECT @NAME = 'NAME2',@AGE = NULL SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* 都为NULL,查所有 */ SELECT @NAME = NULL,@AGE = NULL SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* 都不为NULL,查所有条件 */ SELECT @NAME = 'NAME1',@AGE = 21 SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)
/* 定义表结构 */ DECLARE @USER TABLE(ID INT,Name NVARCHAR(20),Age INT)/* 构造表数据 */ INSERT INTO @USER(ID,Name,Age) SELECT 1,'NAME1',21 UNION ALL SELECT 2,'NAME2',22 UNION ALL SELECT 3,'NAME3',23 UNION ALL SELECT 4,'NAME4',24 UNION ALL SELECT 5,'NAME5',25/* 构造查询参数 */ DECLARE @NAME NVARCHAR(20),@AGE INT/* 按@AGE查询 */ SELECT @NAME = NULL,@AGE = 23 SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* 按@NAME查询 */ SELECT @NAME = 'NAME2',@AGE = NULL SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* 查询所有,相当于没有WHERE筛选 */ SELECT @NAME = NULL,@AGE = NULL SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* 都不为NULL,查所有条件 */ SELECT @NAME = 'NAME1',@AGE = 21 SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)
var sql="Select * from 表名 Where 1=1 "; if(@name!=null) { sql+=" and name= '"+@name+"'"; } if(@age!=0) { sql+=" and age="+@age; } 这个方式可以。
create proc [dbo].[proc_SCQianLiao] @C1 varchar (20), @C2 varchar (100), @C5 varchar (30), @C6 varchar (100), @C28 varchar (20), @C33 varchar (20) asselect InterID as '内码', C1 as '工程编号', C2 as '设备名称', C5 as '物料编号', C6 as '物料名称', C7 as '物料规格', C8 as '物料型号', C9 as '物料品牌', C11 as '单位', C13 as '总需求数量', C14 as '需求日期', C15 as '备注', C26 as '发料日期', C27 as '发料数量', C28 as '发料员', C29 as '尚欠数量', C32 as '生产地点', C33 as '生产组别' from requirement where C29>0 and (@C1 = '' OR C1 like '%' + @C1 + '%') and (@C2 = '' OR C2 like '%' + @C2 + '%') and (@C5 = '' OR C5 like '%' + @C5 + '%') and (@C6 = '' OR C6 like '%' + @C6 + '%') and (@C28 = '' OR C28 like '%' + @C28 + '%') and (@C33 = '' OR C33 like '%' + @C33 + '%') 同时多少个条件都可以。
selec*...... ............ .... ....... form user where (@name='' and age=@age) or (@age='' and name=@name) or (name=@name and age=@age)
declare @sql varchar(2000) set @sql='selec *......form user' if(@name='') begin set @sql=@sql+' where age='+@age end else if(@age="") begin set @sql=@sql+' where name='''+@name+'''' end else begin set @sql=@sql+' where name='''+@name+''' and age='+@age end exec(@sql)
用or,比如 ... where (@name = '' or [Name] = @name) and (@age = '' or [Age] = @age)
仅给思路,语法不一定正确:... var @sql=""; if(@name!='') begin @sql=" and name=@name" end if(@age!="") begin @sql=@sql+" and age=@age" end@sql="where"+ mid(@sql,5) ...
dim isWhere as booleanif condition then if isWhere then sql += else sql += when + isWhere =true end if end if
嗯,要用case 字段名 when 你判断的值 then 执行你的sql语句 when 你判断的值 then 执行你的sql语句 ...end
还没结啊,LZ如果想少写点的话,可以用临时表 select...into #temp from.... f(@name='') begin select * from #temp where age=@age end else if(@age="") begin select * from #temp where name=@name end else begin select * from #temp where name=@name and age=@age end
String sql="Select * from 表名 Where 1=1 ";
if(@name!=null)
{
sql+=" and name= '"+@name+"'";
}
if(@age!=0)
{
sql+=" and age="+@age;
} ......
看明白了吗
set @sql='selec*......form user'
if(@name='')
begin
set @sql=@sql+'where age=@age'
end
else if(@age="")
begin
set @sql=@sql+'where name=@name'
end
else
begin
set @sql=@sql+'where name=@name and age=@age'
end
exec(@sql)
begin
select * from t where X=@y
end
else if 1=0
begin
slect * x wehre y=@x
end
不知道可不可以create procedure selectInfo
@name,@age
begin
if name=''
select * from user
where age=@age else
if age=''
select * from user
where name=@name else
select * from user
where name=@name and age=@age
end
create procedure selectInfo
@name char(10),@age int
begin
if name=''
select * from user
where age=@age else
if age=''
select * from user
where name=@name else
select * from user
where name=@name and age=@age
end
WHERE条件不同 可以吧where段放进一个单独的存储过程里 到时候传递参数就行了。
DECLARE @USER TABLE(ID INT,Name NVARCHAR(20),Age INT)/* 构造表数据 */
INSERT INTO @USER(ID,Name,Age)
SELECT 1,'NAME1',21 UNION ALL
SELECT 2,'NAME2',22 UNION ALL
SELECT 3,'NAME3',23 UNION ALL
SELECT 4,'NAME4',24 UNION ALL
SELECT 5,'NAME5',25/* 构造查询参数 */
DECLARE @NAME NVARCHAR(20),@AGE INT/* @NAME为NULL,@AGE不为NULL,按照@AGE查询 */
SELECT @NAME = NULL,@AGE = 23
SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* @NAME为NULL,@AGE不为NULL,按照@AGE查询 */
SELECT @NAME = 'NAME2',@AGE = NULL
SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* 都为NULL,查所有 */
SELECT @NAME = NULL,@AGE = NULL
SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* 都不为NULL,查所有条件 */
SELECT @NAME = 'NAME1',@AGE = 21
SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)
DECLARE @USER TABLE(ID INT,Name NVARCHAR(20),Age INT)/* 构造表数据 */
INSERT INTO @USER(ID,Name,Age)
SELECT 1,'NAME1',21 UNION ALL
SELECT 2,'NAME2',22 UNION ALL
SELECT 3,'NAME3',23 UNION ALL
SELECT 4,'NAME4',24 UNION ALL
SELECT 5,'NAME5',25/* 构造查询参数 */
DECLARE @NAME NVARCHAR(20),@AGE INT/* 按@AGE查询 */
SELECT @NAME = NULL,@AGE = 23
SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* 按@NAME查询 */
SELECT @NAME = 'NAME2',@AGE = NULL
SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* 查询所有,相当于没有WHERE筛选 */
SELECT @NAME = NULL,@AGE = NULL
SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)/* 都不为NULL,查所有条件 */
SELECT @NAME = 'NAME1',@AGE = 21
SELECT * FROM @USER WHERE Name = ISNULL(NULLIF(@NAME,''),Name) AND Age = ISNULL(NULLIF(@AGE,0),AGE)
var sql="Select * from 表名 Where 1=1 ";
if(@name!=null)
{
sql+=" and name= '"+@name+"'";
}
if(@age!=0)
{
sql+=" and age="+@age;
}
这个方式可以。
@C1 varchar (20),
@C2 varchar (100),
@C5 varchar (30),
@C6 varchar (100),
@C28 varchar (20),
@C33 varchar (20)
asselect
InterID as '内码',
C1 as '工程编号',
C2 as '设备名称',
C5 as '物料编号',
C6 as '物料名称',
C7 as '物料规格',
C8 as '物料型号',
C9 as '物料品牌',
C11 as '单位',
C13 as '总需求数量',
C14 as '需求日期',
C15 as '备注',
C26 as '发料日期',
C27 as '发料数量',
C28 as '发料员',
C29 as '尚欠数量',
C32 as '生产地点',
C33 as '生产组别'
from requirement where
C29>0 and
(@C1 = '' OR C1 like '%' + @C1 + '%') and
(@C2 = '' OR C2 like '%' + @C2 + '%') and
(@C5 = '' OR C5 like '%' + @C5 + '%') and
(@C6 = '' OR C6 like '%' + @C6 + '%') and
(@C28 = '' OR C28 like '%' + @C28 + '%') and
(@C33 = '' OR C33 like '%' + @C33 + '%')
同时多少个条件都可以。
selec*......
............
....
.......
form user
where (@name='' and age=@age) or (@age='' and name=@name) or (name=@name and age=@age)
set @sql='selec *......form user'
if(@name='')
begin
set @sql=@sql+' where age='+@age
end
else if(@age="")
begin
set @sql=@sql+' where name='''+@name+''''
end
else
begin
set @sql=@sql+' where name='''+@name+''' and age='+@age
end
exec(@sql)
...
where (@name = '' or [Name] = @name) and (@age = '' or [Age] = @age)
var @sql="";
if(@name!='')
begin
@sql=" and name=@name"
end
if(@age!="")
begin
@sql=@sql+" and age=@age"
end@sql="where"+ mid(@sql,5)
...
if isWhere then
sql +=
else
sql += when +
isWhere =true
end if
end if
select...into #temp from....
f(@name='')
begin
select * from #temp where age=@age
end
else if(@age="")
begin
select * from #temp where name=@name
end
else
begin
select * from #temp where name=@name and age=@age
end