现在要做的是一个查询功能,有三个查询条件分别对应三个字段,我想在存储过程里判断,如果当一个传入的参数不为空时就搜索该字段,要怎么做啊,我这样做的不行。
create proc selectuserinfo
/*获取用户列表*/
@username varchar(50),
@userstate varchar(50),
@iscost varchar(50)
asset nocount on
declare @usernamesql varchar(100)
declare @userstatesql varchar(100)
declare @useriscost varchar(100)
if(@username<>'')
begin
@usernamesql=' and [UserName] like '%'+@username +'%''
end
if(@userstate<>'')
begin
@userstatesql=' and [UserStat]='+@username+''
end
if(@iscost <>'')
begin
@useriscost =' and [iscost]='+@iscost +''
end
exec(select * from [tbluser] where 1=1 '+@usernamesql+ @userstatesql + @useriscost)
create proc selectuserinfo
/*获取用户列表*/
@username varchar(50),
@userstate varchar(50),
@iscost varchar(50)
asset nocount on
declare @usernamesql varchar(100)
declare @userstatesql varchar(100)
declare @useriscost varchar(100)
if(@username<>'')
begin
@usernamesql=' and [UserName] like '%'+@username +'%''
end
if(@userstate<>'')
begin
@userstatesql=' and [UserStat]='+@username+''
end
if(@iscost <>'')
begin
@useriscost =' and [iscost]='+@iscost +''
end
exec(select * from [tbluser] where 1=1 '+@usernamesql+ @userstatesql + @useriscost)
解决方案 »
- 有关触发器问题
- 管理SQL Server 2000的日志增长 (总结)
- 用数字替换查出的英文字母!!!!在线等呀!
- 关于三个数据库之间同步的问题,请大家帮忙,3号就要用了,急
- 救救我吧
- 如何编写语句把ORACLE数据导入到SQL Server中
- 有关NULL的一个问题!!!..进来看看..知道的说一下!!!!
- --------------这个sql语句应该如何写?---------------
- 帮帮我,关于在JBuilder中连接MS SQL Server2000的问题!!!???
- 求一SQL语句?
- 如何批处理执行sql文件,以及如何切换用户,我用的是sqlserver 2005
- 求一条SQL搜索语句,高手进来帮帮忙,在线等。
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
select * from [tbluser] where 1=1 and [UserName] = isnull(@username,[UserName]) and [UserStat] = isnull(@userstate,[UserStat]) and [iscost]=isnull(@iscost,[iscost])
/*获取用户列表*/
@username varchar(50),
@userstate varchar(50),
@iscost varchar(50)
as改成:create proc selectuserinfo
/*获取用户列表*/
@username varchar(50)='',
@userstate varchar(50)='',
@iscost varchar(50)=''
as
create proc selectuserinfo
/*获取用户列表*/
@username varchar(50),
@userstate varchar(50),
@iscost varchar(50)
as
declare @usernamesql varchar(100)
declare @userstatesql varchar(100)
declare @useriscost varchar(100)
declare @where varchar(500)
if(@username<>'')
set @where=' and [UserName] like ''%'+@username +'%'''
if(@userstate<>'')
set @where=' and [UserStat]='''+@username+''''
if(@iscost <>'')
set @where =' and [iscost]='''+@iscost +''''
exec('select * from [tbluser] where 1=1 '+@where)
/*获取用户列表*/
@username varchar(50),
@userstate varchar(50),
@iscost varchar(50)
asset nocount on
declare @usernamesql varchar(100)
declare @userstatesql varchar(100)
declare @useriscost varchar(100)
select @usernamesql ='',@userstatesql ='',@useriscost=''
if(@username<>'')
begin
@usernamesql=' and [UserName] like ''%'+@username +'%'''
end
if(@userstate<>'')
begin
@userstatesql=' and [UserStat]='''+@username+''''
end
if(@iscost <>'')
begin
@useriscost =' and [iscost]='''+@iscost +''''
end
exec ('select * from [tbluser] where 1=1 '+@usernamesql+ @userstatesql + @useriscost)
/*获取用户列表*/
@username varchar(50)='',
@userstate varchar(50)='',
@iscost varchar(50)=''
asset nocount on
declare @usernamesql varchar(100)
declare @userstatesql varchar(100)
declare @useriscost varchar(100)
if(isnull(@username,'')<>'')
begin
set @usernamesql=' and [UserName] like '''+'%'++@username +'%'+''''
end
if(isnull(@userstate,'')<>'')
begin
set @userstatesql=' and [UserStat]='''+@username+''''
end
if(isnull(@iscost,'') <>'')
begin
set @useriscost =' and [iscost]='''+@iscost +''''
end
exec('select * from [tbluser] where 1=1 '+@usernamesql+ @userstatesql + @useriscost)
COALESCE ( expression [ ,...n ] ) 如COALESCE(f1,f2,f3,'VOID')
即是如下意思:
如果f1不为空则返回f1,否则,
如果f2不为空则返回f2,否则,
如果f3不为空则返回f3,否则,
返回'VOID'可以有N多个参数,简捷又明了。
变量付值要用 set 变量=xxxx
一般来说,''没有什么意思,要在进入数据库时进行限制.
另外还有,' ', ' ', 等一个或多个空格或TAB符的情况,也等在进入数据库时作NULL处理.
(前几天,在我们的应用系统中的关键字段中混进了一个TAB符号,害得很多业务无法正常进行,差不多花了三个人天的功夫才找到问题所在)