How To Return String Value From The Stored 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('

Solution 1:

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.

Changing your procedure.

ALTERprocedure S_Comp(@str1varchar(20),@rvarchar(100) out) asdeclare@str2varchar(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'elseSELECT@r=@str1+' not present'

Calling the procedure

DECLARE@rVARCHAR(100)
  EXEC S_Comp 'Test', @r OUTPUT
  SELECT@r

Solution 2:

change your

return@str1+'present in the string' ;

to

set@r = @str1+'present in the string'

Solution 3:

Use SELECT or an output parameter. More can be found here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100201

Solution 4:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/return-transact-sql?view=sql-server-ver15

Is the integer value that is returned. Stored procedures can return an integer value to a calling procedure or an application.

Syntax syntaxsql

RETURN [ integer_expression ]

it only returns integer values.