This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Output Parameters and Return Codes in SQL Server Part three of a three-part series of blogs
This blog teaches you how to return values from your SQL stored procedures using two different techniques: return codes, and output parameters. Returning Values from Stored Procedures in SQL Using Return Codes with Stored Procedures Using Output Parameters (this blog) This blog is part of a complete SQL tutorial. You can learn about the techniques used in this blog on our Advanced SQL training course.

Output Parameters and Return Codes in SQL Server

Part three of a three-part series of blogs

This blog teaches you how to return values from your SQL stored procedures using two different techniques: return codes, and output parameters.

Returning Values from Stored Procedures in SQL
Using Return Codes with Stored Procedures
Using Output Parameters (this blog)

This blog is part of a complete SQL tutorial. You can learn about the techniques used in this blog on our Advanced SQL training course.

Posted by Andrew Gould on 26 April 2013

Using Output Parameters

Another technique for returning a value from a stored procedure is to use an output parameter. Each stored procedure can have many output parameters and each parameter can use any data type. Before discussing output parameters it might be worth reminding yourself how input parameters work.

To learn more about passing parameters to stored procedures have a look at our SQL training page, or book a place on our advanced SQL course!

Declaring Output Parameters

You declare output parameters in the definition of a procedure, in much the same way you declare input parameters. The only difference is that you add the word OUTPUT to the end. The procedure below has a single input parameter and a single output parameter:

CREATE PROC spYearBudget

(

@FilmYear INT

,@TotalBudget BIGINT OUTPUT

)

BEGIN

SET @TotalBudget =

(

SELECT SUM(FilmBudgetDollars)

FROM tblFilm

WHERE YEAR(FilmReleaseDate) = @FilmYear

)

END

Rather than using the RETURN statement, the value of an output parameter is set explicitly using the SET keyword. For the example above we could also just use the SELECT statement to set the value of the output parameter, as shown below:

CREATE PROC spYearBudget

(

@FilmYear INT

,@TotalBudget BIGINT OUTPUT

)

BEGIN

SELECT @TotalBudget = SUM(FilmBudgetDollars)

FROM tblFilm

WHERE YEAR(FilmReleaseDate) = @FilmYear

END

Capturing the Value of an Output Parameter

To capture an output parameter you need to use a variable. The example below uses a variable to store the result of the output parameter from our stored procedure:

DECLARE @Budget BIGINT

EXEC spYearBudget

1998

,@Budget OUTPUT

SELECT @Budget

When you call a stored procedure with both input and output parameters you provide values for the input parameters in the usual way. In the example above we've passed the value 1998 to the @FilmYear parameter. For each of the output parameters you specify the name of the variable you are using to hold the result followed by the word OUTPUT.

If you've decided to name the parameters when you call the stored procedure the syntax is slightly odd:

DECLARE @Budget BIGINT

EXEC spYearBudget

@FilmYear = 1998

,@TotalBudget = @Budget OUTPUT

SELECT @Budget

Normally when you assign a value to a variable the name of the variable appears to the left of the = sign. With an output parameter, however, the variable name appears to the right of the = sign.

The final line of code in our example simply displays the value of the output parameter, as shown in the diagram below:

The result of our output parameter.

Using Multiple Output Parameters

One of the advantages of output parameters over return codes is that a single stored procedure can have a number of output parameters. In the example below we've extended our stored procedure to return multiple values:

ALTER PROC spYearBudget

(

@FilmYear INT

,@TotalBudget BIGINT OUTPUT

,@AverageBudget DECIMAL(18,2) OUTPUT

,@HighestBudget BIGINT OUTPUT

,@LowestBudget BIGINT OUTPUT

,@NumberOfFilms INT OUTPUT

)

BEGIN

SELECT

@TotalBudget = SUM(FilmBudgetDollars)

,@AverageBudget = AVG(CAST(FilmBudgetDollars AS DECIMAL))

,@HighestBudget = MAX(FilmBudgetDollars)

,@LowestBudget = MIN(FilmBudgetDollars)

,@NumberOfFilms = COUNT(FilmID)

FROM tblFilm

WHERE YEAR(FilmReleaseDate) = @FilmYear

END

When we call the procedure we can now populate multiple variables and show a complete set of outputs instead of just a single value:

DECLARE @Total BIGINT

DECLARE @Average DECIMAL(18,2)

DECLARE @Highest BIGINT

DECLARE @Lowest BIGINT

DECLARE @Number INT

EXEC spYearBudget

@FilmYear = 1998

,@TotalBudget = @Total OUTPUT

,@AverageBudget = @Average OUTPUT

,@HighestBudget = @Highest OUTPUT

,@LowestBudget = @Lowest OUTPUT

,@NumberOfFilms = @Number OUTPUT

SELECT

@Total AS [Total Budget]

,@Average AS [Average Budget]

,@Highest AS [Highest Budget]

,@Lowest AS [Lowest Budget]

,@Number AS [Number of Films]

The result of calling this stored procedure is shown in the diagram below:

A neat set of outputs from a single stored procedure.

Parts of this blog
Returning Values from Stored Procedures in SQL Using Return Codes with Stored Procedures Using Output Parameters (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using Output Parameters

Declaring Output Parameters

Capturing the Value of an Output Parameter

Using Multiple Output Parameters

Head office

London

Manchester