How to return string value from the stored procedure

Question

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  gt 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

User · Answer

Use SELECT or an output parameter  More can be found here   http   www sqlteam com forums topic asp TOPIC ID 100201

User · Answer

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  gt 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

User · Answer

change your  return  str1  present in the string      to   set  r    str1  present in the string

[sql] How to return string value from the stored procedure

Examples related to sql

Examples related to sql-server-2008