SQL201 - Stored Procedure Results
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!
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:
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).
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.
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!
