create PROCEDURE [dbo].[ProcShowUser] ( @fd_UserType int, @fd_UserSitatus int ) AS declare @str varchar(100) set @str='' if (@fd_UserType!=0) --modify 这一句 set @str=' fd_UserType= '''+ltrim(@fd_UserType)+''' and ' if (@fd_UserSitatus!=0) set @str=@str+' fd_UserSitatus= '+@fd_UserSitatus+' and ' select * from tb_users where ''+@str+''+ 1=1 +''order by fd_RegTime desc
create PROCEDURE [dbo].[ProcShowUser] ( @fd_UserType int, @fd_UserSitatus int ) AS declare @str varchar(100) set @str='' if (@fd_UserType!=0) --modify 这一句***************************************************** set @str=' fd_UserType= '''+ltrim(@fd_UserType)+''' and ' if (@fd_UserSitatus!=0) set @str=@str+' fd_UserSitatus= '+@fd_UserSitatus+' and ' select * from tb_users where ''+@str+''+ 1=1 +''order by fd_RegTime desc
create PROCEDURE [dbo].[ProcShowUser] ( @fd_UserType int, @fd_UserSitatus int ) AS declare @str varchar(100) set @str='' if (@fd_UserType!=0) set @str=' fd_UserType= '+@fd_UserType+' and ' if (@fd_UserSitatus!=0) set @str=@str+' fd_UserSitatus= '+@fd_UserSitatus+' and ' @str='select * from tb_users where '+@str+ '1=1 order by fd_RegTime desc ' exec(@str)
create PROCEDURE [dbo].[ProcShowUser] ( @fd_UserType int, @fd_UserSitatus int ) AS declare @str varchar(100) set @str='' if (@fd_UserType!=0) set @str=' fd_UserType= '+cast(@fd_UserType as varchar)+' and ' if (@fd_UserSitatus!=0) set @str=@str+' fd_UserSitatus= '+@fd_UserSitatus+' and ' @str='select * from tb_users where '+@str+ '1=1 order by fd_RegTime desc ' exec(@str)
if (@fd_UserType!=0) set @str=' fd_UserType= '+@fd_UserType+' and ' if (@fd_UserSitatus!=0) set @str=@str+' fd_UserSitatus= '+@fd_UserSitatus+' and ' select * from tb_users where ''+@str+''+ 1=1 +''order by fd_RegTime desc 改为if (@fd_UserType!=0) set @str=' fd_UserType= '+cast(@fd_UserType as varchar(10))+' and ' if (@fd_UserSitatus!=0) set @str=@str+' fd_UserSitatus= '+cast(@fd_UserSitatus as varchar(10))+' and ' exec('select * from tb_users where 1=1 '+@str+' order by fd_RegTime desc ')
所有INT型的要转为字符型才能跟其它字符串拼接.
' fd_UserType= '+@fd_UserType 因为前一个是字符串,而后面是数字,所以在拼接的时候会把前面的类型转换成后面的,所以nvarchar转换int失败 同样 ' fd_UserSitatus= '+@fd_UserSitatus 也是如此 create PROCEDURE [dbo].[ProcShowUser] ( @fd_UserType int, @fd_UserSitatus int ) AS declare @str varchar(100) set @str='' if (@fd_UserType!=0) set @str=' fd_UserType= '+CAST(@fd_UserType AS NVARCHAR(3))+' and ' if (@fd_UserSitatus!=0) set @str=@str+' fd_UserSitatus= '+CAST(@fd_UserSitatus AS NVARCHAR(3))+' and ' select * from tb_users where ''+@str+''+ 1=1 +''order by fd_RegTime desc
create PROCEDURE [dbo].[ProcShowUser] ( @fd_UserType int, @fd_UserSitatus int ) AS declare @str varchar(1000) set @str='select * from tb_users where 1=1 ' if (@fd_UserType!=0) set @str=@str+' and fd_UserType= '+rtrim(@fd_UserType) if (@fd_UserSitatus!=0) set @str=@str+' and fd_UserSitatus= '+rtrim(@fd_UserSitatus) set @str=@str+' order by fd_RegTime desc' exec(@str) go
(
@fd_UserType int,
@fd_UserSitatus int
)
AS
declare @str varchar(100)
set @str=''
if (@fd_UserType!=0)
--modify 这一句
set @str=' fd_UserType= '''+ltrim(@fd_UserType)+''' and '
if (@fd_UserSitatus!=0)
set @str=@str+' fd_UserSitatus= '+@fd_UserSitatus+' and ' select * from tb_users where ''+@str+''+ 1=1 +''order by fd_RegTime desc
(
@fd_UserType int,
@fd_UserSitatus int
)
AS
declare @str varchar(100)
set @str=''
if (@fd_UserType!=0)
--modify 这一句*****************************************************
set @str=' fd_UserType= '''+ltrim(@fd_UserType)+''' and '
if (@fd_UserSitatus!=0)
set @str=@str+' fd_UserSitatus= '+@fd_UserSitatus+' and ' select * from tb_users where ''+@str+''+ 1=1 +''order by fd_RegTime desc
create PROCEDURE [dbo].[ProcShowUser]
(
@fd_UserType int,
@fd_UserSitatus int
)
AS
declare @str varchar(100)
set @str=''
if (@fd_UserType!=0)
set @str=' fd_UserType= '+@fd_UserType+' and '
if (@fd_UserSitatus!=0)
set @str=@str+' fd_UserSitatus= '+@fd_UserSitatus+' and ' @str='select * from tb_users where '+@str+ '1=1 order by fd_RegTime desc '
exec(@str)
(
@fd_UserType int,
@fd_UserSitatus int
)
AS
declare @str varchar(100)
set @str=''
if (@fd_UserType!=0)
set @str=' fd_UserType= '+cast(@fd_UserType as varchar)+' and '
if (@fd_UserSitatus!=0)
set @str=@str+' fd_UserSitatus= '+@fd_UserSitatus+' and ' @str='select * from tb_users where '+@str+ '1=1 order by fd_RegTime desc '
exec(@str)
set @str=' fd_UserType= '+@fd_UserType+' and '
if (@fd_UserSitatus!=0)
set @str=@str+' fd_UserSitatus= '+@fd_UserSitatus+' and ' select * from tb_users where ''+@str+''+ 1=1 +''order by fd_RegTime desc
改为if (@fd_UserType!=0)
set @str=' fd_UserType= '+cast(@fd_UserType as varchar(10))+' and '
if (@fd_UserSitatus!=0)
set @str=@str+' fd_UserSitatus= '+cast(@fd_UserSitatus as varchar(10))+' and ' exec('select * from tb_users where 1=1 '+@str+' order by fd_RegTime desc ')
同样 ' fd_UserSitatus= '+@fd_UserSitatus 也是如此
create PROCEDURE [dbo].[ProcShowUser]
(
@fd_UserType int,
@fd_UserSitatus int
)
AS
declare @str varchar(100)
set @str=''
if (@fd_UserType!=0)
set @str=' fd_UserType= '+CAST(@fd_UserType AS NVARCHAR(3))+' and '
if (@fd_UserSitatus!=0)
set @str=@str+' fd_UserSitatus= '+CAST(@fd_UserSitatus AS NVARCHAR(3))+' and ' select * from tb_users where ''+@str+''+ 1=1 +''order by fd_RegTime desc
(
@fd_UserType int,
@fd_UserSitatus int
)
AS
declare @str varchar(1000)
set @str='select * from tb_users where 1=1 '
if (@fd_UserType!=0)
set @str=@str+' and fd_UserType= '+rtrim(@fd_UserType)
if (@fd_UserSitatus!=0)
set @str=@str+' and fd_UserSitatus= '+rtrim(@fd_UserSitatus) set @str=@str+' order by fd_RegTime desc' exec(@str)
go