Getting Parameters Out From a Stored Procedure–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the lesser used and known features of T-SQL are the output parameters from a stored procedure. I used one of these recently, so I wanted to blog about it.

Getting a String

I was working on part of the 2018 Advent of Code, which is a great set of programming exercises for anyone. I typically build a procedure to solve each puzzle, since that’s a common way of capturing code. If there’s a single numeric result, a RETURN code works fine.

In one puzzle, I needed to return a string. If you try this in a procedure, it won’t work.

2018-12-27 10_04_01-SQLQuery6.sql - Plato_SQL2016.sandbox (PLATO_Steve (58))_ - Microsoft SQL Server

Instead, I need another solution. I could certainly SELECT back my string, but in this case, I wanted to have this assigned to a variable. I could do that in a few ways, but decided the easiest was an OUTPUT parameter.

To add an output parameter to my procedure, I first add my variable as a regular parameter.

CREATE OR ALTER PROCEDURE dbo.StringTest
   @s VARCHAR(10)
AS
BEGIN
     SELECT @s = 'Some Code'
END
GO

Next, I add the OUTPUT keyword after the type.

CREATE OR ALTER PROCEDURE dbo.StringTest
   @s VARCHAR(10) = '' OUTPUT
AS
BEGIN
     SELECT @s = 'Some Code'
END
GO

My call to the procedure should also include the OUTPUT keyword.

DECLARE @result VARCHAR(10);
EXEC dbo.StringTest @s = @result OUTPUT;
SELECT @result;

This works fine, allowing me to pass some value back to the caller.

2018-12-27 10_08_40-SQLQuery6.sql - Plato_SQL2016.sandbox (PLATO_Steve (58))_ - Microsoft SQL Server

Not something I use often, but if I need to get some singular value back, this works.

SQLNewBlogger

This post was started at 10:00am one morning. I got back to this sentence at 10:09. That was the entire setup of the code, capturing screen shots, and writing the post. Easy for you to do as well.

Give this a try. How would you use an OUTPUT parameter?

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

5 Responses to Getting Parameters Out From a Stored Procedure–#SQLNewBlogger

  1. Yaroslav says:

    Hello Steve!
    This is my way:
    I usually use functions if there is a need to return a singular value only, besides functions can return values of different data types.
    And for procedures I use ‘return’ to return status codes.
    And, if I need more then one value to return, I use both functions and procedures with ‘output’.

  2. Three other things I think that are worth noting:

    1) You can pass more than one parameter as `OUTPUT`,
    2) You do not have to call the parameters with `OUTPUT` if you don’t want the results
    3) You don’t even have to declare the ‘OUTPUT’ parameters after all the non-output parameters (although it is good practice).

    CREATE PROCEDURE ManyOutputs @a int, @b int output, @c varchar(100) output, @d bit output
    AS
    BEGIN
    SET @b = @a + 11
    SET @c = ‘The Value of A is ‘ + CAST(@a AS varchar(5)) + ‘, and the value of B is ‘ + CAST(@b AS varchar(5))
    IF (@a % 2 = 1)
    SET @d = 1
    ELSE
    SET @d = 0
    END
    GO

    Calling this routine:

    DECLARE @bVal int
    DECLARE @cVal varchar(100)
    DECLARE @dVal bit

    EXEC ManyOutputs 1, @bVal, @cVal, @dVal
    SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal
    Returns NULL, NULL, NULL

    EXEC ManyOutputs 2, @bVal OUT, @cVal OUT, @dVal OUT
    SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

    Returns 13, “The Value of A is 2, and the value of B is 13”, 0

    EXEC ManyOutputs 3, @bVal, @cVal, @dVal
    SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

    Returns 13, “The Value of A is 2, and the value of B is 13”, 0
    (the same as the last call, because we didn’t get new values by using `OUTPUT`, so it retained the old values.)

    EXEC ManyOutputs 5, @bVal OUT, @cVal OUT, @dVal OUT
    SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

    Returns 16, “The Value of A is 5, and the value of B is 16”, 1

    • way0utwest says:

      Good points. I wasn’t trying to provide a complete tutorial, but these are good notes. You don’t need to specify output, but if you don’t, you don’t get data back.

    • I didn’t realize how effective parameter passing could be until I read your post, and so I did some testing, and found out these other things. I know your post was meant to be ‘blogging about something you recently used’, and so my reply is ‘your post got me to thinking, and here’s what I found.’

      Don’t think me an expert at this. You are light-years beyond me. Just wanted to add that you’ve encouraged me to dig and find something else new (to me, at least.) You’ve done that often for me, please keep it up!

  3. way0utwest says:

    Thanks, and that was part of the idea. I started experimenting and wrote some things up.

    You could do the same, Dan 😉

Comments are closed.