OUTPUT Parameters in Stored Procedures and RETURNing a Status

I needed to learn how to use Output Parameters in Stored Procedures within Sql Server 2005. I wanted also to do this only in Sql for starters. I also wanted to return a value to the calling code.

You can return a value from stored procedures that you can use to designate success or failure or some other status.

I’ve never used the Output parameter before, though it is apparently faster to return a value as an output parameter as a result set doesn’t have to be created (I’m not sure where i read that. But it basically said that it is a little quicker…)

Here is a small example of doing how to do this. Note the following:

  • You need to put OUTPUT after the parameter name in the stored procedure
  • To return a status value you use RETURN
  • When executing the stored procedure you need to specify OUTPUT after passing in the parameter values (for some reason ?!)
  • You need to assign the result of the stored proc to a variable to get the RETURNed value

Here is the Sql code example:

CREATE PROCEDURE [dbo].[FormatMyNameManyTimes] (
    @FirstName  nvarchar(100) = '',
    @LastName   nvarchar(100) = '',
    @DefaultResult nvarchar(200) OUTPUT
)
AS
BEGIN
    SELECT @FirstName + ' ' + @LastName
    UNION ALL
    SELECT @LastName + ', ' + @FirstName
    UNION ALL
    SELECT @FirstName + ' ' + @LastName
    UNION ALL
    SELECT @LastName + ', ' + @FirstName
    UNION ALL
    SELECT @FirstName + ' ' + @LastName
    UNION ALL
    SELECT @LastName + ', ' + @FirstName
    UNION ALL
    SELECT @FirstName + ' ' + @LastName;

    SELECT @DefaultResult = @FirstName + ' ' + @LastName;

    RETURN 404;
END

DECLARE	@return_value nvarchar(100);
DECLARE @Default nvarchar(200);

EXEC	@return_value = [dbo].[FormatMyNameManyTimes]
            @DefaultResult = @Default OUTPUT,
            @FirstName = N'Silly',
	    @LastName = N'Sausage';

SELECT  'Return Value' = @return_value;
SELECT  '@DefaultResult' = @Default;

DROP PROCEDURE [dbo].[FormatMyNameManyTimes];
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: