Return value of stored procedure

DECLARE @return_value int

EXEC   @return_value = [dbo].[SP_NAME]  @ID = 131279, ......

SELECT 'Return Value' = @return_value

• When the SP is successful, @return_value would be 0.
• Also @return_value=NULL means an error.

If we want to return an output value from the SP, we have to declare an output parameter in the SP declaration:

CREATE PROCEDURE [dbo].[SP_NAME]

@Param1 int,

....... ,

@SpCount INT OUTPUT

AS

.......

and insert it a value in the SP code.

When execute SP:

DECLARE @Count int

EXEC   @return_value = [dbo].[SP_NAME]  @ID = 131279, ......, 

              @SpCount = @Count OUTPUT