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.