Using table valued results of stored procedures
June 20, 2008 at 6:31 PM
If a stored procedure returns a table of data, it's not as easy as one might think to work with the result. Assume the following scenario:
- we have a table Account
- we have a (admittedly silly) stored procedure that performs some query and returns "Account" rows:
1: CREATE PROCEDURE sp_GetAccounts
3: SELECT * FROM Account;
- we want to perform additional filtering & projection on the data returned by the SP, like:
1: SELECT UserName, Email
2: FROM (EXEC sp_GetAccounts)
3: WHERE Email LIKE '%.de'
4: ORDER BY UserName ASC;
This, however, does not work:
Incorrect syntax near the keyword 'EXEC'.
It seems that we cannot use the result of the SP as a normal table. But here's a little trick that will do what we want:
1: -- create a table variable (ie. a temporary in-memory table)
2: DECLARE @results TABLE (
3: ID int,
4: UserName nvarchar(50),
5: Email nvarchar(256),
6: HashedPasswod nvarchar(32),
7: Salt nvarchar(16)
10: -- fill it with the results from the SP
11: INSERT INTO @results EXEC sp_GetAccounts;
13: -- perform normal queries on the table
14: SELECT UserName, Email
15: FROM @results
16: WHERE Email LIKE '%.de'
17: ORDER BY UserName ASC;
We simply create a table variable compatible to the Account table to temporarily hold the results of the stored procedure. This table is filled using the SP (INSERTs work with SPs). We can then execute queries against that table variable as we like. By using a table variable (instead of a temporary table) everything is held in memory and we don't need to drop the table explicitly after we're done using it.
I'm not a SP guru, so if there are simpler ways to achieve the same result, drop me a comment.