if @BrandName is not null set @strSql = 'BrandName= ' +@BrandName else set @strSql = '!'if @Compatibilty is not null set @strSql = @strSql + 'and Compatibilty = ' + @Compatibilty if @Classification is not null set @strSql = @strSql + 'and Classification = ' + @Classification...... if left(@strSql,1) = '!' and len(strSql) > 1 set @strSql = right(@strSql, len(@strSql) -1)if len(strSql) > 1 set @strSql = 'select * from AAFinishProduct where ' +@strSql
使用 if @BrandName is null begin set @strSql='' end else begin //楼主这个字段应该是字符型,所以,要处理的话,应该加上引号的。 set @strSql='BrandName='' '+@BrandName +''''
end
if @BrandName is not null set @strSql=''BrandName='+@BrandName else set @strSql=''
if 条件 begin if 条件2 begin 方式1 end else begin 方式3 end end else begin 方式2 end
变量赋值可以使用: set 变量1=变量2+变量3 或者 select 变量1=变量2+变量3
谢谢各位,我做了如下,但出现错误: Server: Msg 2812, Level 16, State 62, Line 55 Could not find stored procedure 'select * from AAFinishProduct where BrandName=OEM and Compatibilty =and Classification=and SubClass='.代码: alter procedure usp_AAFinish_Search@BrandName nvarchar(50), @Compatibilty nvarchar(50), @Classification nvarchar(50), @SubClass nvarchar(50), @Active bitasdeclare @strSql nvarchar(100)set @strSql=''--SELECT * from [AAFinishProduct] where 'if @BrandName is not null set @strSql = 'BrandName=' +@BrandName +' ' else set @strSql='' if @Compatibilty is not null begin if @strSql is not null set @strSql=@strSql +'and Compatibilty ='+ @Compatibilty else set @strSql='Compatibilty = '+ @Compatibilty endif @Classification is not null begin if @strSql is not null set @strSql=@strSql +'and Classification='+ @Classification else set @strSql='Classification='+ @Classification endif @SubClass is not null begin if @strSql is not null set @strSql=@strSql +'and SubClass='+ @SubClass else set @strSql='SubClass='+ @SubClass endif len(@strSql) > 1 set @strSql = 'select * from AAFinishProduct where ' +@strSqlexec @strSql GOexec usp_AAFinish_Search 'OEM','','','','0'
用is not null判断就可以了
当然出错啦,@BrandName,@Compatibilty,@Classification,@SubClass 是字符串,在存储过程中要加引号“'”,不然就会出现你上述错误了“select * from AAFinishProduct where BrandName=OEM and Compatibilty =and Classification=and SubClass=”,你不发觉查询语句里的Compatibilty =and Classification=and SubClass=,少了Compatibilty ='' and Classification='' and SubClass=''
create procedure usp_AAFinish_Search --创建存储过程@BrandName nvarchar(50), --参数 @Compatibilty nvarchar(50), @Classification nvarchar(50), @SubClass nvarchar(50), @Active bit as declare @strSql nvarchar(1000) --定义一个娈量来存储条件 set @strSql=case isnull(@BrandName,'') when '' then '' else 'BrandName='''+@BrandName+'''' end +case isnull(@Compatibilty,'') when '' then '' else ' and Compatibilty ='''+@Compatibilty+'''' end +case isnull(@Classification,'') when '' then '' else ' and Classification='''+@Classification+'''' end +case isnull(@strSql,'') when '' then '' else ' and SubClass='''+@SubClass+'''' end +case when @Active isnull then '' else ' and Active='+cast(@Active as varchar) end if @strsql<>'' and left(@strsql,4)=' and' select @strsql=right(@strsql,len(@strsql)-4) go
前面有一点小错误 --创建存储过程 create procedure usp_AAFinish_Search @BrandName nvarchar(50) ,@Compatibilty nvarchar(50) ,@Classification nvarchar(50) ,@SubClass nvarchar(50) ,@Active bit as declare @strSql nvarchar(1000) set @strSql=case isnull(@BrandName,'') when '' then '' else 'BrandName='''+@BrandName+'''' end +case isnull(@Compatibilty,'') when '' then '' else ' and Compatibilty ='''+@Compatibilty+'''' end +case isnull(@Classification,'') when '' then '' else ' and Classification='''+@Classification+'''' end +case isnull(@strSql,'') when '' then '' else ' and SubClass='''+@SubClass+'''' end +case when @Active is null then '' else ' and Active='+cast(@Active as varchar) end if @strsql<>'' and left(@strsql,4)=' and' select @strsql=right(@strsql,len(@strsql)-4) print @strsql go--测试 exec usp_AAFinish_Search '1','aa',null,null,0 exec usp_AAFinish_Search null,'aa',null,null,null--删除存储过程 drop procedure usp_AAFinish_Search
测试结果: exec usp_AAFinish_Search '1','aa',null,null,0 的结果: BrandName='1' and Compatibilty ='aa' and Active=0exec usp_AAFinish_Search null,'aa',null,null,null 的结果: Compatibilty ='aa'
太谢谢 zjcxc(邹建) ,我还想请问一下,我现有要查到表中的所有记录,要用到条件 @strsql,要怎样写呢?是不是: select * from AFinish where + @strsql 就可以了呢?要把这句放在哪里呢?谢谢!! 在线等候
select @strsql='select * from AFinish where ' 下面是你动态生成条件的代码 .... 最后 exec (@strsql)
set @strSql = 'BrandName= ' +@BrandName
else
set @strSql = '!'if @Compatibilty is not null
set @strSql = @strSql + 'and Compatibilty = ' + @Compatibilty
if @Classification is not null
set @strSql = @strSql + 'and Classification = ' + @Classification......
if left(@strSql,1) = '!' and len(strSql) > 1
set @strSql = right(@strSql, len(@strSql) -1)if len(strSql) > 1
set @strSql = 'select * from AAFinishProduct where ' +@strSql
if @BrandName is null
begin
set @strSql=''
end
else
begin
//楼主这个字段应该是字符型,所以,要处理的话,应该加上引号的。
set @strSql='BrandName='' '+@BrandName +''''
end
set @strSql=''BrandName='+@BrandName
else
set @strSql=''
begin
if 条件2
begin
方式1
end
else
begin
方式3
end
end
else
begin
方式2
end
set 变量1=变量2+变量3
或者
select 变量1=变量2+变量3
Server: Msg 2812, Level 16, State 62, Line 55
Could not find stored procedure 'select * from AAFinishProduct where BrandName=OEM and Compatibilty =and Classification=and SubClass='.代码:
alter procedure usp_AAFinish_Search@BrandName nvarchar(50),
@Compatibilty nvarchar(50),
@Classification nvarchar(50),
@SubClass nvarchar(50),
@Active bitasdeclare @strSql nvarchar(100)set @strSql=''--SELECT * from [AAFinishProduct] where 'if @BrandName is not null
set @strSql = 'BrandName=' +@BrandName +' '
else
set @strSql=''
if @Compatibilty is not null
begin
if @strSql is not null
set @strSql=@strSql +'and Compatibilty ='+ @Compatibilty
else
set @strSql='Compatibilty = '+ @Compatibilty
endif @Classification is not null
begin
if @strSql is not null
set @strSql=@strSql +'and Classification='+ @Classification
else
set @strSql='Classification='+ @Classification
endif @SubClass is not null
begin
if @strSql is not null
set @strSql=@strSql +'and SubClass='+ @SubClass
else
set @strSql='SubClass='+ @SubClass
endif len(@strSql) > 1
set @strSql = 'select * from AAFinishProduct where ' +@strSqlexec @strSql
GOexec usp_AAFinish_Search 'OEM','','','','0'
@Compatibilty nvarchar(50),
@Classification nvarchar(50),
@SubClass nvarchar(50),
@Active bit
as
declare @strSql nvarchar(1000) --定义一个娈量来存储条件
set @strSql=case isnull(@BrandName,'') when '' then ''
else 'BrandName='''+@BrandName+'''' end
+case isnull(@Compatibilty,'') when '' then ''
else ' and Compatibilty ='''+@Compatibilty+'''' end
+case isnull(@Classification,'') when '' then ''
else ' and Classification='''+@Classification+'''' end
+case isnull(@strSql,'') when '' then ''
else ' and SubClass='''+@SubClass+'''' end
+case when @Active isnull then ''
else ' and Active='+cast(@Active as varchar) end
if @strsql<>'' and left(@strsql,4)=' and'
select @strsql=right(@strsql,len(@strsql)-4)
go
--创建存储过程
create procedure usp_AAFinish_Search
@BrandName nvarchar(50)
,@Compatibilty nvarchar(50)
,@Classification nvarchar(50)
,@SubClass nvarchar(50)
,@Active bit
as
declare @strSql nvarchar(1000)
set @strSql=case isnull(@BrandName,'') when '' then ''
else 'BrandName='''+@BrandName+'''' end
+case isnull(@Compatibilty,'') when '' then ''
else ' and Compatibilty ='''+@Compatibilty+'''' end
+case isnull(@Classification,'') when '' then ''
else ' and Classification='''+@Classification+'''' end
+case isnull(@strSql,'') when '' then ''
else ' and SubClass='''+@SubClass+'''' end
+case when @Active is null then ''
else ' and Active='+cast(@Active as varchar) end
if @strsql<>'' and left(@strsql,4)=' and'
select @strsql=right(@strsql,len(@strsql)-4)
print @strsql
go--测试
exec usp_AAFinish_Search '1','aa',null,null,0
exec usp_AAFinish_Search null,'aa',null,null,null--删除存储过程
drop procedure usp_AAFinish_Search
exec usp_AAFinish_Search '1','aa',null,null,0
的结果:
BrandName='1' and Compatibilty ='aa' and Active=0exec usp_AAFinish_Search null,'aa',null,null,null
的结果:
Compatibilty ='aa'
select * from AFinish where + @strsql
就可以了呢?要把这句放在哪里呢?谢谢!! 在线等候
下面是你动态生成条件的代码
....
最后
exec (@strsql)