Menu
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.
Please do help me resolving this
932
Stored procedures can return an integer value to a calling procedure or an application. it only returns integer values.
You can use one or more RETURN statements in a stored procedure. The RETURN statement can be used anywhere after the declaration blocks within the SQL-procedure-body. To return multiple output values, parameters can be used instead. Parameter values must be set before the RETURN statement runs.
In SQL Server, you can use the T-SQL SUBSTRING() function to return a substring from a given string. You can use SUBSTRING() to return parts of a character, binary, text, or image expression.
After the stored procedure call, the variables will be populated with return values. If you want to have RETURN value as return from the PL/SQL call, then use FUNCTION . Please note that in case, you would be able to return only one variable as return variable.
You are placing your result in the RETURN value instead of in the passed @rvalue.
From 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 change your
return @str1+'present in the string' ; to
set @r = @str1+'present in the string' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
