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 开发者_如何学Pythonserver. 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

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.

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

change your

return @str1+'present in the string' ;

to

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

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

https://learn.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.