Alter procedure S_Comp(@str1 varchar(20),@r varchar(100) out) as declare @str2 varchar(100) set @str2 ='welcome to sql server. sql server is a product of Microsoft' if(PATINDEX('%'+@str1 +'%',@str2)>0) return @str1+'present in the string' else return @str1+'not present'
我正在执行上述存储过程.我收到以下错误：
消息245,第16级,状态1,过程S_Comp,第8行
将varchar值“Amruthanot present”转换为数据类型int时,转换失败.
请帮我解决这个问题
解决方法
您将结果放在RETURN值中,而不是通过@rvalue.
从MSDN
(RETURN)
Is the integer value that is returned. Stored procedures can return
an integer value to a calling
procedure or an application.
改变你的程序
ALTER procedure S_Comp(@str1 varchar(20),@r varchar(100) out) as
declare @str2 varchar(100)
set @str2 ='welcome to sql server. sql server is a product of Microsoft'
if(PATINDEX('%'+@str1 +'%',@str2)>0)
SELECT @r = @str1+' present in the string'
else
SELECT @r = @str1+' not present'
调用程序
DECLARE @r VARCHAR(100) EXEC S_Comp 'Test',@r OUTPUT SELECT @r
