Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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).

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)

AS

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.

CustomerPONumber

1

PO10324111289

2

PO16414129790

3

PO10150121946

4

PO16211136640

5

PO3654183134

Comments

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.