SQL 如何从存储过程返回字符串值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6450194/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return string value from the stored procedure
提问by Pearl
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'
I am executing the above stored procedure. I am getting the following error :
我正在执行上面的存储过程。我收到以下错误：
Msg 245, Level 16, State 1, Procedure S_Comp, Line 8 Conversion failed when converting the varchar value 'Amruthanot present' to data type int.
消息 245，级别 16，状态 1，过程 S_Comp，第 8 行将 varchar 值“Amruthanot present”转换为数据类型 int 时转换失败。
Please do help me resolving this
请帮我解决这个问题
回答by Lieven Keersmaekers
You are placing your result in the RETURNvalue instead of in the passed @rvalue.
您将结果放在RETURN值而不是传递的@r值中。
From MSDN
来自MSDN
(RETURN)Is the integer value that is returned. Stored procedures can return an integer value to a calling procedure or an application.
(RETURN)是返回的整数值。存储过程可以向调用过程或应用程序返回一个整数值。
Changing your procedure.
改变你的程序。
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'
Calling the procedure
调用程序
DECLARE @r VARCHAR(100)
EXEC S_Comp 'Test', @r OUTPUT
SELECT @r
回答by Binil
change your
改变你的
return @str1+'present in the string' ;
to
到
set @r = @str1+'present in the string'
回答by trendl
Use SELECT or an output parameter. More can be found here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100201
使用 SELECT 或输出参数。更多信息可以在这里找到：http: //www.sqlteam.com/forums/topic.asp?TOPIC_ID=100201
