http://www.sqlservercentral.com/blogs/dknight/2008/12/08/using-result-set-as-variable-in-stored-procedure/

Printed 2014/04/19 05:40PM

Using Result Set as Variable in Stored Procedure

2008/12/08

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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.