alter procedure cp_product
@fieldType varchar(50),
@keywords varchar(50)
as
begin
declare @sqlstr varchar(2000)
-- set @sqlstr='select ' + @a +'=title from product where ' +@fieldType+' like'+'''%'+@keywords+'%'''
set @sqlstr='select title from product where '+@fieldtype+' like ''%'+@keywords+'%'''
exec(@sqlstr)
end
execute cp_product 'title','a'
上面这段代码运行正常,输出结果也正确,可是我加了个输出参数,运行也不提示错误,返回为空,不知道怎么回事?代码如下
alter procedure cp_product
@fieldType varchar(50),
@keywords varchar(50),
@a varchar(50) output
as
begin
declare @sqlstr varchar(2000)
-- set @sqlstr='select ' + @a +'=title from product where ' +@fieldType+' like'+'''%'+@keywords+'%'''
set @sqlstr='select '+ @a +'=title from product where '+@fieldtype+' like ''%'+@keywords+'%'''
exec(@sqlstr)
end 运行存储过程
declare @b varchar(50)
execute cp_product 'title','a',@b output
select @b as a
@fieldType varchar(50),
@keywords varchar(50)
as
begin
declare @sqlstr varchar(2000)
-- set @sqlstr='select ' + @a +'=title from product where ' +@fieldType+' like'+'''%'+@keywords+'%'''
set @sqlstr='select title from product where '+@fieldtype+' like ''%'+@keywords+'%'''
exec(@sqlstr)
end
execute cp_product 'title','a'
上面这段代码运行正常,输出结果也正确,可是我加了个输出参数,运行也不提示错误,返回为空,不知道怎么回事?代码如下
alter procedure cp_product
@fieldType varchar(50),
@keywords varchar(50),
@a varchar(50) output
as
begin
declare @sqlstr varchar(2000)
-- set @sqlstr='select ' + @a +'=title from product where ' +@fieldType+' like'+'''%'+@keywords+'%'''
set @sqlstr='select '+ @a +'=title from product where '+@fieldtype+' like ''%'+@keywords+'%'''
exec(@sqlstr)
end 运行存储过程
declare @b varchar(50)
execute cp_product 'title','a',@b output
select @b as a
declare @b varchar(50)
set @b=null
execute cp_product 'title ', 'a ',@b output
select @b as a
alter procedure cp_product
@fieldType varchar(50),
@keywords varchar(50),
@a varchar(50) output
as
begin
declare @sqlstr nvarchar(2000)
set @sqlstr= 'select @b=title from product where '+@fieldtype+ ' like ' '% '+@keywords+ '% ' ' '
exec sp_execute @sqlstr,N'@b varchar(50) output',@a output
end
set @TransactorID=0
print(@TransactorID)
exec sp_executesql N'select @TransactorID=5 ',N'@TransactorID int out ',@TransactorID out
print(@TransactorID)
execute cp_product 'title','w',@b output
select @b as a
消息 214,级别 16,状态 2,过程 sp_execute,第 1 行
过程需要类型为 'int' 的参数 '@handle'。