Execute a Stored Procedure Within a Query

Downloads125824.zip

Stored procedures are typically executed with an EXEC statement. However, you can execute a stored procedure implicitly from within a SELECT statement, provided that the stored procedure returns a result set. The OPENROWSET function is key to this technique, which involves three steps.

Step 1. Enable the Ad Hoc Distributed Queries Option

By default, SQL Server doesn’t allow ad hoc distributed queries using OPENROWSET. Thus, the first step is to enable the Ad Hoc Distributed Queries configuration option on the SQL Server machine from which you’ll be executing the query. If the option is disabled on your machine, you can use the EnableOption.sql script to enable it. You can download this script and the other code discussed here by clicking the Download the Code Here button near the top of the page. After running EnableOption.sql, you can confirm that the option is enabled by looking at the script’s output. It should include the statement Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1.

Step 2. Create the View

The next step is to create a view that provides the same result as the stored procedure you want to execute in queries. You can then use that view in a SELECT statement. I created the sp_ConvProc2View stored procedure in Listing 1 to transform stored procedures into views.

Listing 1: sp_ConvProc2View.sql

-- BEGIN CALLOUT A CREATE PROCEDURE sp_ConvProc2View   (@procName varchar(80), @viewName varchar(80)) -- END CALLOUT A AS BEGIN   DECLARE @TSQLStmt nvarchar(500)   SET NOCOUNT OFF -- BEGIN CALLOUT B   SET @TSQLStmt = N'CREATE VIEW ' + @viewName +      N' AS SELECT * FROM ' +     'OPENROWSET ( ' + '''' + 'SQLOLEDB' + '''' + ',' +     '''' + 'SERVER=.;Trusted_Connection=yes' + '''' + ',' +     '''' + 'SET FMTONLY OFF EXEC ' + @procName + '''' + ')' -- END CALLOUT B   EXEC sp_executesql @TSQLStmt   SET NOCOUNT ON END GO

 As callout A in Listing 1 shows, sp_ConvProc2View requires two parameters: the name of the stored procedure you want to transform into a view (@procName) and the name you want to give that view (@viewName). It passes those parameters to the OPENROWSET function, which it executes with dynamic SQL. Within a string, dynamic SQL is slower than ordinary SQL and more prone to SQL injection attacks. Because of the latter, you need to make sure that any parameters you pass to sp_ConvProc2View (and hence OPENROWSET) are properly edited to minimize SQL injection attacks.

As callout B in Listing 1 shows, sp_ConvProc2View uses the SQLOLEDB provider along with a trusted connection (Windows authentication) to connect the default SQL Server instance (SERVER=.) and run OPENROWSET against it. If you want to use a named instance, you need to replace the period with the name of the instance or modify the code so that the instance's name is provided by a parameter to the procedure. The SET FMTONLY OFF statement at the end of callout B ensures that the results (and not just the metadata) will be output.

Let's look at a couple of examples of how to use sp_ConvProc2View. Note that the view specified with the @viewName parameter must not already exist. If it does, an error will occur.

Suppose you want to transform the sp_lock system stored procedure (which provides information about locks) into a view named v$Lock. In this case, you'd run

EXEC sp_ConvProc2View  @procName = 'sp_Lock',  @viewName ='v$Lock'

If you want to create a view named v$Session from the sp_who system stored procedure (which provides information about current users, sessions, and processes), you'd run

EXEC sp_ConvProc2View  @procName = 'sp_who',  @viewName ='v$Session'

Step 3. Use the View in a SELECT Statement

After you create the view, you can use it in a SELECT statement. You can make the SELECT statement as simple or complex as needed. I'll show you three examples that use the v$Lock and v$Session views created in step 2. Note that the code in these examples is case sensitive.

Example 1: A simple SELECT statement. If you just want to see the locks in all the currently active sessions, you can run the statement

SELECT * FROM V$Lock

You can add a WHERE clause if you want to filter or group data. For example, if you want to see only those sessions running a background task, you can run the code

SELECT * FROM v$Session  WHERE Status LIKE '%background%'

Example 2: A complex SELECT statement. To obtain more in-depth information, you can join views in a SELECT statement, thereby implicitly running multiple system stored procedures at the same time. For example, you can join the v$Lock, v$Session, and sys.objects views to obtain detailed information about the currently active sessions that contain locks with code such as

SELECT S.status,S.dbname,S.cmd,O.name,  L.Type,L.Mode,L.StatusFROM v$Session S JOIN v$Lock L ON  S.spid = L.spid JOIN sys.objects OON L.ObjId = O.object_id

For each locked process in a session, this query returns:

Example 3: A SELECT statement whose results are loaded into a table. You can use code that creates and executes a SELECT statement, then loads the SELECT statement’s results into an output table. I created the sp_OutputAndFilterResults stored procedure for this purpose. After creating and executing a SELECT statement, it loads the results into a temporary output table. This stored procedure takes three parameters, which are case sensitive:

Using this information, sp_OutputAndFilterResults creates a SELECT statement, assigning it to the @TSQL variable. Then, if there’s a filter condition, the stored procedure adds it to the SELECT statement. After using PRINT to display the command in @TSQL for debug purposes, the stored procedure dynamically executes that command.

To execute sp_OutputAndFilterResults, you use code such as

EXEC sp_OutputAndFilterResults  @ViewName ='v$Session',  @OutputTable = 'output_sess' ,  @WhereClause = 'DBname = ''master'''SELECT * FROM output_sess

This code returns all the currently active sessions that are using the master database and stores them in a temporary table named output_sess.

Putting It All Together

You can combine the code in steps 2 and 3 into one script. For example, the script in Listing 2 transforms the sp_who stored procedure into the v$Session view, uses that view in a SELECT statement to find the currently active sessions that are using the master database, and stores that information in the output_sess table.

Listing 2: SampleScript.sql

EXEC sp_ConvProc2View   @procName = 'sp_who',   @viewName ='v$Session' EXEC sp_OutputAndFilterResults      @ViewName ='v$Session',      @OutputTable = 'output_sess' ,      @WhereClause = 'DBname = ''master''' GO SELECT * FROM output_sess

This script assumes that:

All the code I discussed here works on SQL Server 2008 and SQL Server 2005. See also, "Query Performance Tuning."