stored procedure sql server with return

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

Answer 1

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

Answer 2

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

---

 | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

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

Answer 3

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!

Answer 4

thank you

Answer 5

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

Answer 6

so if I was asked to do à strored procedure that return value I must do that

Answer 7

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

Answer 8

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