SELECT from a Stored Procedure

Occasionally I find myself wanting to SELECT from a SPROC in SQL Server.  Usually this is because I want to ORDER the results or filter them further with a WHERE clause.  Unfortunately, you can’t just do this:

There are several workarounds here, and the appropriate one depends mostly on whether you have any control over the use of the stored procedure, or how it works.  For example, you could choose to use a VIEW instead of a stored procedure.  Unfortunately, a VIEW usually won’t work if you need to pass parameters to your stored procedure (which I’m guessing you are).  If your sproc is literally just a wrapper for a query that has no dependencies on parameters, then yeah, you probably should just use a view, and then of course you can select from it to your heart’s content.

INSERT-EXEC

The simplest approach that doesn’t require making any changes to your perfectly good stored procedure is to declare a temporary table with the appropriate schema to match what the sproc outputs.  Then INSERT the stored procedure’s results into the temp table and SELECT from it.  An example looks like this:

This is the approach I favor when I simply need to apply a WHERE or an ORDER BY to an existing stored procedure.  There are actually quite a few other ways to share data between stored procedures or between ad hoc queries and stored procedures.  Erland Sommerskog has a nice article outline How to Share Data Between Stored Procedures that you might want to read for more options.

Erland also notes a few limitations to the above approach, which you should keep in mind.  I only use this approach for quick ad hoc queries, not for use in production code, because of the limitations involved.

  • Dave Sussman

    You might want to consider a table variable instead of a temporary table, which will avoid logging, especially useful for ad-hoc querying. I use a lot of CTEs too, although that doesn’t work for this example, and TVFs are perfect for the sharing data scenario.

  • ssmith

    Wouldn’t I need to pass in the table variable to the sproc, though? If I don’t want to change my existing sproc, does the table variable work?

  • Sonu Kapoor

    @Dave: Aren’t table variables usually only useful if you have a small table?

    @Steve: You dont need to pass the variable through your sproc. It will work the same way as you have above. The only difference would be that you are you using a table variable instead of a temp table.

  • Dave Sussman

    I’ve no idea on table variables and table size, but they aren’t logged so a large amount of data in a table variable could improve perf. I haven’t really studied this in detail though; http://www.sql-server-performance.com/…/temp_tables_vs_ has some info on large amounts of data, but it’s an old article, so might not be true with SQL 2008.

  • Akhil Gupta

    Visual Studio and SQL Server management Studio are two different entities. So it takes a lot of time when we try an interaction with the database from Visual Studio then it takes a lot of time.

    When we are using desktop applications then we do not realize this time delay, but while using web applications the time delay becomes an important factor because the database is on the remote server so a huge time delay is observed.

    Normally, the query that we write in Visual Studio is first compiled and then executed in Sql Server which then returns the results. So, this is why a time delay is observed.

    The answer to this is Stored Procedure usage. The Stored Procedure is stored in compiled format in SQL server so when we call it from Visual Studio application then it is just executed. Thus saving a lot of time.

    An illustration of proper usage of STORE PROCEDURE has been displayed in the following video. You may see it:

    http://www.visiontechno.net/…/storeprocedure.

  • David Lozano Lucas

    This works perfect to me.

  • subodhan

    Hi

    I want In a query return statement used in like ?

    How i write this query ?

    Thanks

  • keerthi

    what if a stored procedure returns multiple result sets and we want to access a the 2nd result set?Is there a way?

  • tj

    Thanks, this was helpful.