Using table valued results of stored procedures

June 20, 2008 at 6:31 PMAndre Loker

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

image

- we have a (admittedly silly) stored procedure that performs some query and returns "Account" rows:

   1: CREATE PROCEDURE sp_GetAccounts
   2: AS
   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)
   8: );
   9:  
  10: -- fill it with the results from the SP
  11: INSERT INTO @results EXEC sp_GetAccounts;
  12:  
  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.

Posted in: Databases | Snippets

Tags: , ,

Pingbacks and trackbacks (1)+