create table user
(
id int primary key,
name varchar(10),
pwd varchar(20)
)
go
create proc SP_Test
@id int,
@name varchar(10),
@pwd varchar(10)
as
begin
declare @sql varchar(4000)
set @sql='select id,name,pwd from [user] where 1=1 ';
if(ISNULL(@id,'')<>'')
--set @sql=@sql+' and id like %'+@id+'%'';
set @sql=@sql+ ' AND id like '+@id;
if(ISNULL(@name,'')<>'')
set @sql=@sql+' and name like ''%'+@name+'%''';
if(ISNULL(@pwd,'')<>'')
set @sql=@sql+' and pwd like ''%'+@pwd+'%''';
exec(@sql);
end
go
exec SP_Test 1,'',''為什麼執行存儲過程後出錯,錯誤信息:
訊息 245,層級 16,狀態 1,程序 SP_Test,行 12
將 varchar 值 'select id,name,pwd from [user] where 1=1 AND id like ' 轉換成資料類型 int 時,轉換失敗。
(
id int primary key,
name varchar(10),
pwd varchar(20)
)
go
create proc SP_Test
@id int,
@name varchar(10),
@pwd varchar(10)
as
begin
declare @sql varchar(4000)
set @sql='select id,name,pwd from [user] where 1=1 ';
if(ISNULL(@id,'')<>'')
--set @sql=@sql+' and id like %'+@id+'%'';
set @sql=@sql+ ' AND id like '+@id;
if(ISNULL(@name,'')<>'')
set @sql=@sql+' and name like ''%'+@name+'%''';
if(ISNULL(@pwd,'')<>'')
set @sql=@sql+' and pwd like ''%'+@pwd+'%''';
exec(@sql);
end
go
exec SP_Test 1,'',''為什麼執行存儲過程後出錯,錯誤信息:
訊息 245,層級 16,狀態 1,程序 SP_Test,行 12
將 varchar 值 'select id,name,pwd from [user] where 1=1 AND id like ' 轉換成資料類型 int 時,轉換失敗。
declare @sql varchar(4000)
set @sql='select id,name,pwd from [user] where 1=1 ';
if(ISNULL(@id,'')<>'')
--set @sql=@sql+' and id like %'+@id+'%'';
set @sql=@sql+ ' AND id like '''+@id +''';
declare @sql varchar(4000)
set @sql='select id,name,pwd from [user] where 1=1 ';
if(ISNULL(@id,'')<>'')
--set @sql=@sql+' and id like %'+@id+'%'';
set @sql=@sql+ ' AND id like '+cast(@id as varchar); --这里要将int类型的变量转换为varchar类型
if(ISNULL(@name,'')<>'')
set @sql=@sql+' and name like ''%'+@name+'%''';
if(ISNULL(@pwd,'')<>'')
set @sql=@sql+' and pwd like ''%'+@pwd+'%''';
exec(@sql);