create proc proctext
@ID int ,
@Name VARCHAR(50)
as
declare @sql varchar(max)
set @sql='select * from UserTest where 1=1'
if(@id!='')
begin
set @sql=@sql+' and id='+@id
end
if(@Name!='')
begin
set @sql=@sql+' and Name='+@Name
end
select @sql
go错误信息:
在将 varchar 值 'select * from UserTest where 1=1 and id=' 转换成数据类型 int 时失败。
输出来的是字符串,怎么没有记录?谢谢!
@ID int ,
@Name VARCHAR(50)
as
declare @sql varchar(max)
set @sql='select * from UserTest where 1=1'
if(@id!='')
begin
set @sql=@sql+' and id='+@id
end
if(@Name!='')
begin
set @sql=@sql+' and Name='+@Name
end
select @sql
go错误信息:
在将 varchar 值 'select * from UserTest where 1=1 and id=' 转换成数据类型 int 时失败。
输出来的是字符串,怎么没有记录?谢谢!
报错?
create proc proctext
@ID int ,
@Name VARCHAR(50)
as
declare @sql varchar(max)
set @sql='select * from UserTest where 1=1'
if(ltrim(@id)!='')
begin
set @sql=@sql+' and id='+ltrim(@id)
end
if(@Name!='')
begin
set @sql=@sql+' and Name='+@Name
end
select @sql
go
@ID int ,
@Name VARCHAR(50)
as
declare @sql varchar(max)
set @sql='select * from UserTest where 1=1'
if(@id!='')
begin
set @sql=@sql+' and id='+Convert(varchar(8),@id)
end
if(@Name!='')
begin
set @sql=@sql+' and Name='+@Name
end
-- print (@sql)
exec (@sql)
goexec proctext 8,'黑马'
/*-- print (@sql)的结果为:
select * from UserTest where 1=1 and id=8 and Name=黑马*/
最后来一句 exec (@sql)
手工
@ID int ,
@Name VARCHAR(50)
as
declare @sql varchar(max)
set @sql='select * from UserTest where 1=1'
if(@id!='')
begin
set @sql=@sql+' and id='+Convert(varchar(8),@ID)
end
if(@Name!='')
begin
set @sql=@sql+' and Name='+@Name
end
exec (@sql)
go
exec proctext 8,'黑马'
错误信息,列名 '黑马' 无效。谢谢!