SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using Result Set as Variable in Stored Procedure

I’ve been asked several times lately from non-SQL developers that are sometimes required to do some quick SQL work how to use the result set of a query as a variable in a stored procedure. My first thought was I’ll just send them a link to a website that can explain how to do it (yes… so I don’t have to!). After not finding a reference quick enough or maybe basic enough for a non-SQL developer to understand I wrote my own example (also a good excuse for a blog post).

In this example I’m using the AdventureWorksDW2008 database and creating a stored procedure that returns all customer PO numbers where the queried product was sold.

USE AdventureWorksDW2008

ALTER PROC ProductSearch

@ProductName varchar(50)


DECLARE @ProductKey int

SELECT @ProductKey = ProductKey

FROM DimProduct

WHERE EnglishProductName = @ProductName

SELECT CustomerPONumber

FROM FactResellerSales

WHERE ProductKey = @ProductKey

Looking at this query wouldn’t it be easier to just join the two tables? Of course! That’s essentially what’s being done. Like I said though this is more of just an example on how to use a result set in a variable.

Exec ProductSearch 'ML Mountain Frame-W - Silver, 38'

So if your run this procedure here are your results. Hope this helps someone that’s still learning how to work with stored procedures.












Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


Posted by Jerry Hung on 8 December 2008

The title mislead me to think this is using a multi-rows resultset in another stored procedure, which either involves table variable, global temp tables, or 2008's passing table data type

Posted by knight_devin@hotmail.com on 8 December 2008

Sorry to mislead!  My intention was to just answer a quick question asked of me.  Maybe I'll have to write another blog using multi-rows result set.

Posted by Tim Mitchell on 10 December 2008
Leave a Comment

Please register or log in to leave a comment.