locked
stored procedure sql server with return RRS feed

  • Question

  • hi

    please is it necessary to use 'return' if I was asked to do à strored procedure that return value.

    please give me an example if yes.

    an tell me how can I execute It.

    Thank you

    Tuesday, June 19, 2012 9:51 AM

Answers

  • You don't normally use return to return a value you want  other than for error handling purpose. RETURN can also used to igore your rest of code inside of your stored proc by using condition.

    Normally, we use the output variable to output a value from inside of a stored procedure. try the very simple example below:

    Create Proc usp_TestOutPut
    @OutValue int output
    AS
    Begin
    
    	Select @OutValue = 12345;
    	
    
    End
    
    Go
    
    Declare @MyValue int;
    
    Exec usp_TestOutPut @outValue = @MyValue output;
    
    Select @MyValue;
    please pay attention to how you assign the OUTPUT value to a variable, it is indeed at the right side of the = sign

    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!



    Tuesday, June 19, 2012 10:14 AM
  • In my example,

    CREATE PROCEDURE TESTPROC 
    @Input int, 
    @Output VARCHAR(20) OUTPUT 
    AS 
    BEGIN 
    
      IF(@Input = 1) 
      BEGIN 
    
         SET @Output = 'YES'; 
         RETURN 
      END
    
      SET @Output = 'NO' 
    
    END

    If the @Input parameter is not 1 then the Execution will go up to SET @Output = 'NO' and then the @Output value will be returned as NO. Please let me know if you still have any doubt on this.


    Thanks,
    Ram

    • Marked as answer by Iric Wen Wednesday, June 27, 2012 8:29 AM
    Tuesday, June 19, 2012 12:14 PM

All replies

  • Yes, to return a single value from an SP and use it at the calling module you will need to apply the RETURN statement at the end of the SP body.

    Check this:

    create procedure abcd
    as
    begin
     -- Your code
     return (1234)
    end
    go

    DECLARE @id INT
    EXEC @id = abcd
    SELECT @id
    go

    drop procedure abcd
    go

    If you want to return multiple values you can also use OUTPUT clause with parameters.

    Check this blog link: http://sqlwithmanoj.wordpress.com/2011/03/23/using-output-parameters-in-stored-procedures/


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011


    Tuesday, June 19, 2012 10:11 AM
  • You don't normally use return to return a value you want  other than for error handling purpose. RETURN can also used to igore your rest of code inside of your stored proc by using condition.

    Normally, we use the output variable to output a value from inside of a stored procedure. try the very simple example below:

    Create Proc usp_TestOutPut
    @OutValue int output
    AS
    Begin
    
    	Select @OutValue = 12345;
    	
    
    End
    
    Go
    
    Declare @MyValue int;
    
    Exec usp_TestOutPut @outValue = @MyValue output;
    
    Select @MyValue;
    please pay attention to how you assign the OUTPUT value to a variable, it is indeed at the right side of the = sign

    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!



    Tuesday, June 19, 2012 10:14 AM
  • Please refer to this MSDN http://msdn.microsoft.com/en-us/library/ms188655.aspx

    You will learn about Output and Return to return values from Stored Procedure.

    Cheer!

    Tuesday, June 19, 2012 10:14 AM
  • thank you

    Tuesday, June 19, 2012 12:03 PM
  • RETURN is not mandatory in a procedure. If you use that then the execution will be terminated at the stage and all the output values will be returned back. Also if you want to return the values inside a particular condition you can use RETURN.

    CREATE PROCEDURE TESTPROC @Input int, @Output VARCHAR(20) OUTPUT AS BEGIN IF(@Input = 1) BEGIN SET @Output = 'YES'; RETURN END

    SET @Output = 'NO' END


    In the above example if the @Input value is 1 then the execution will be stopped inside the IF condition and the @Output will be returned as 1.

    Thanks,
    Ram

    Tuesday, June 19, 2012 12:04 PM
  • so if I was asked to do à strored procedure that return value I must do that
    Tuesday, June 19, 2012 12:05 PM
  • No, Its not really needed. If you don't use the RETURN in any place then the execution will go till the end of the procedure and the values will be returned at the end.

    Thanks,
    Ram

    Tuesday, June 19, 2012 12:07 PM
  • In my example,

    CREATE PROCEDURE TESTPROC 
    @Input int, 
    @Output VARCHAR(20) OUTPUT 
    AS 
    BEGIN 
    
      IF(@Input = 1) 
      BEGIN 
    
         SET @Output = 'YES'; 
         RETURN 
      END
    
      SET @Output = 'NO' 
    
    END

    If the @Input parameter is not 1 then the Execution will go up to SET @Output = 'NO' and then the @Output value will be returned as NO. Please let me know if you still have any doubt on this.


    Thanks,
    Ram

    • Marked as answer by Iric Wen Wednesday, June 27, 2012 8:29 AM
    Tuesday, June 19, 2012 12:14 PM