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.Status FROM v$Session S JOIN v$Lock L ON S.spid = L.spid JOIN sys.objects O ON L.ObjId = O.object_id
For each locked process in a session, this query returns:
- The status of the involved process
- The name of the database used by that process
- The type of command executing the process
- The name of object that's locked
- The type of lock
- The lock mode requested
- The status of that lock request
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:
- The name of the view (@ViewName).
- The name of the output table (@OutputTable). This name needs to be unique. If a table by that name already exists, it’ll be dropped by the stored procedure.
- A filter condition (@WhereClause). Specifying a filter condition is optional. When one isn’t specified, @WhereClause is assigned a NULL value.
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:
- You already enabled the Ad Hoc Distributed Queries configuration option.
- You already ran the code to create the sp_ConvProc2View stored procedure.
- You already ran the code to create the sp_OutputAndFilterResults stored procedure.
- You deleted the v$Session object if you ran sp_ConvProc2View to transform the sp_who stored procedure into the v$Session view in step 2. (Remember that the view specified with the @viewName parameter must not already exist.)
All the code I discussed here works on SQL Server 2008 and SQL Server 2005. See also, "Query Performance Tuning."