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'