SQL201 – Stored Procedure Results

Posted on by slowder

I’m working my way through the last of the 201 lessons. Today we’re going to dive back into stored procedures. This time I want to cover the three ways you can get data back out. You can simply use a SELECT statement to return a record set, or you can define one or more of your procedure’s parameters as an OUTPUT variable. The last method you can use is a return code. The return code can seem like a limited way of returning data, but if you think your return codes through before implementing the procedure, you can pull off some useful tricks!

Record Sets

This is the most common way I’ve seen people pull data out of a stored procedure. If you wrap a SELECT statement into a stored procedure the record set is returned. The problem with using this method is, you have to be careful in how the code calling the stored procedure will deal with multiple record sets.

Most of the time, people will call a stored procedure and expect only one record set. But look at the query below

CREATE PROCEDURE usp_ExampleWithMultiplerecord sets
AS
BEGIN
SELECT '1' AS ID, 'This is the first record set.' as Message

SELECT '2' AS ID, 'This is the second record set.' as Message
END
GO
EXEC usp_ExampleWithMultiplerecord sets

When you run this stored procedure you get two different record sets. If you were calling this from a webpage, if you don’t handle the results right, you might only see the first record set.

This gets even trickier if you do a DML (Data Manipulation Language) query and then a SELECT.

CREATE PROCEDURE usp_ExampleWithDMLAndQuery
AS
BEGIN
DECLARE @results TABLE ( ID INT, Message VARCHAR(255) )

INSERT INTO @results
SELECT '1' AS ID, 'This is the first record set.' as Message
SELECT '2' AS ID, 'This is the second record set.' as Message

SELECT * FROM @results
END
GO
EXEC usp_ExampleWithDMLAndQuery

This time you only see one record set, but look at the messages in SSMS. Notice you see a message:

"2 rows affected"

Consider the webpage example again. If your webpage doesn’t interpret the results just right, you won’t see the two rows. You’ll keep seeing a NULL record set. That’s because your page is interpreting the “2 rows affected” as the first record set. The common workaround is to add a SET NOCOUNT ON statement to the beginning of your stored procedure, but that’s just a band-aid. The real solution is to look at how you’re interpreting the stored procedures results in the web page (or application layer).

Output Parameters

After a while of using just record sets to return data from stored procedures, I see people “upgrading” their code to use output parameters. It’s very easy to set up a stored procedure to use an output parameter, you simply add the keyword OUTPUT after the parameter’s data type, and you’re set. The only trick to know is when you call the stored procedure, you have to set up a variable outside the stored procedure to hold the returned value, and you have to then identify that variable as an OUTPUT variable in order for the SQL interpreter to pass the values off to your variable. Let’s work through an example now.

CREATE PROCEDURE usp_multiplyTwoNumbers
@input1 INT
, @input2 INT
, @result INT OUTPUT
AS
BEGIN
SET @result = @input1 * @input2
END
GO

After running this code, you have a stored procedure that will multiply the two input values, and store the result in an OUTPUT parameter called @result. Now, that we have the stored procedure, let’s use it.

DECLARE @currentResult INT

EXEC usp_multiplyTwoNumbers 2, 2, @currentResult OUTPUT

SELECT 'The result is " + CONVERT(VARCHAR(255), @currentResult)

We multiplied 2 * 2 and stored the result in @currentResult. We then showed the results along with some explanatory notes. We could use the variable any way we wish. The value is there and accessible until the script ends, or you reach a GO statement. Then you go outside the scope of that variable.

With some time and practice, you’ll find all sorts of useful ways to use OUTPUT parameters. Things get really interesting once you consider table variables!
Return Codes

The last way I know of getting data out of a stored procedure is return codes. A return code is an set of integers you define in your stored procedure. I use them to indicate different types of errors or warnings that can occur when a stored procedure is run.

Let’s look at one of the examples from BOL:

USE AdventureWorks;
GO
IF OBJECT_ID('Sales.usp_GetSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.usp_GetSalesYTD;
GO
CREATE PROCEDURE Sales.usp_GetSalesYTD
@SalesPerson nvarchar(50) = NULL, -- NULL default value
@SalesYTD money = NULL OUTPUT
AS

-- Validate the @SalesPerson parameter.
IF @SalesPerson IS NULL
BEGIN
PRINT 'ERROR: You must specify a last name for the sales person.'
RETURN(1)
END
ELSE
BEGIN
-- Make sure the value is valid.
IF (SELECT COUNT(*) FROM HumanResources.vEmployee
WHERE LastName = @SalesPerson) = 0
RETURN(2)
END
-- Get the sales for the specified name and
-- assign it to the output parameter.
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.EmployeeID = sp.SalesPersonID
WHERE LastName = @SalesPerson;
-- Check for SQL Server errors.
IF @@ERROR <> 0
BEGIN
RETURN(3)
END
ELSE
BEGIN
-- Check to see if the ytd_sales value is NULL.
IF @SalesYTD IS NULL
RETURN(4)
ELSE
-- SUCCESS!!
RETURN(0)
END

What we’ve defined here is a set of return codes that indicate where in the code we’ve gone wrong. We can now call this stored procedure like this:

DECLARE @returnCode INT
, @resultSalesYTD INT;

DECLARE @inputSalesPerson VARCHAR(50) = NULL;

EXEC @returnCode = EXEC Sales.usp_GetSalesYTD
@SalesPerson = @inputSalesPerson
, @SalesYTD = @resultSalesYTD OUTPUT

And we can programatically know how the stored procedure did, based on the value of @returnCode.

  • If @returnCode is 0, then we know the stored procedure executed successfully.
  • If we get a 1, then we know we didn’t pass in a valid LastName for the sales person.
  • If we get a 2, then we know that LastName doesn’t exist in the HumanResources.vEmployee view.
  • If we get a 3, we know we got some other SQL error.
  • If we get a 4, then we know the @salesYTD is going to be NULL.

Of course we would know if there was some other SQL error, since we didn’t implement any error trapping. So @returnCode 3 is superfluous. We could test for @result codes 2 and 4 other ways too, but I can think of none that wouldn’t require you to consider the output on the application side of things.

Conclusion

As always, knowing when to use each of the three options is key. Practice with them, and you’ll learn when each option is the best solution for the task at hand. As usual, if you have any questions send them in…I’m here to help!

Leave a Reply

Your email address will not be published. Required fields are marked *