The purpose of the article is to provide technical means by which a programmer can combine executing a stored procedure from within a SELECT statement providing the stored procedure returns a result set and usually the execution of a stored procedure is done via an exec statement. In the article I'll show method of running a stored procedure implicitly by executing a SELECT statement.

The article also provides means of loading the procedure results to a temporary table with the use of another stored procedure that creates the temporary table and holds it in memory until the application drops the table. I used the OPENROWSET set function with the stored procedure name as a parameter for the function.

OPENROWSET is a special set function used for ad-hoc queries on another connection. The input for OPENROWSET is another query, object or stored procedure. Here I use it with a connection to the same SQL Server source but with a stored procedure as the data source parameter in order to produce a result in a query and not in an exec clause.

The first thing to do is to enable the 'Ad Hoc Distributed Queries' configuration parameter in order to enable ad hoc queries that use the OPENROWSET set function and in order to do it, please execute the following code on your server.

Script A – a Script to enable the 'Ad Hoc Distributed Queries' configuration parameter:

use master
go
exec sp_configure 'allow updates',1
go
reconfigure with override
go
exec sp_configure 'Ad Hoc Distributed Queries',1
go
reconfigure  with override

After running this script confirm that the parameter value is changed to 1 (instead of 0) the output of the script should be (if parameters were set do the SQL Server default Values):

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.

Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

The Procedure Itself

I coded a procedure called sp_ConvProc2View. This stored procedure accepts the Procedure's name and the newly created view name. It used dynamic TSQL (EXEC function) in order to create the view definition. The view is must not exist prior to the procedure execution (or an error will occur).

Script B - Here is the script of the sp_ConvProc2View source code. The procedure code is compiled inside the application DB to be used.

Create procedure sp_ConvProc2View
       ( @procName varchar(80),  @viewName varchar(80))
 as
 -- -------------------------------------------------------------
 -- Procedure name: sp_ConvProc2View
 -- Sp Author : Eli Leiba
 -- Date 08-2009
 -- Description : created a view with same result as the sp
 --  the view can be used in a SELECT statement
 -- ------------------------------------------------------------
 begin
   declare @TSQLStmt varchar(500)
   set nocount off
   -- create the CREATE VIEW tSQL statement.
   -- An OPENROWSET operator is used on the local server
   -- (. means the local SQL server )
   -- using SQLOLEDB provider along with a trusted connection
   -- (windows authentication)
   --  SET FMTONLY off ensures that the results will be output
   -- (not just the metaData)
   -- the EXEC storedProcedure finishes the OPENROWSET parameter.
   set @TSQLStmt = 'CREATE VIEW ' + @viewName + ' AS SELECT * FROM '  +
     'OPENROWSET ( '+ '''' +'SQLOLEDB' + ''''+ ','  +
     '''' + 'SERVER=.;Trusted_Connection=yes'+ '''' +',' +
     '''' + 'SET FMTONLY OFF EXEC ' + @procName + ''''+ ')'
   -- now , we dynamically execute the statement
   exec (@TSQLStmt)
   set nocount on
 end
go

Examples

Here are a few examples for how to use this technique in order to convert system stored procedures Execution like sp_lock and sp_who to view definitions (Oracle Style) V$LOCK and V$SESSION.

Here are two examples for using the sp_ConvProc2View stored procedure. In The first example, I create a view called 'V$LOCK' that operates like the system stored procedure SP_LOCK by using the procedure described in Script B

Example A script:

exec sp_ConvProc2View @procName = 'sp_Lock',   @viewName ='V$LOCK'

By doing a SELECT * FROMV$LOCK we obtain the results of SP_LOCK and the we can use Where clause of group by clause to filter or group the data to our needs.

In a second example: Creating a view called v$Session from the execution for sp_who (to select all running processes)

Example B script:

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

A sample usage for the view could be:

select * from v$Session  where status like '%background%'

We can now Join the view (thus implicitly running both system stored procedures at the same time and get significant information regarding locks and session together.

Here is an example in the following script:

Example C script:

 -- Example of Join between V$LOCK, V$SESSION and sys.objects to
 -- join together to session status, database name , statement type,
 -- name of object locked, lock type mode and status in a single query
SELECTS.status,S.dbName,S.cmd,O.name, L.TYPE,L.mode,L.status
FROM  V$SESSION S,V$LOCK L, sys.objects O
 where L.OBJID = O.object_id and 
S.SPID = L.SPID

Here is a sample result on my Server:

Status           DBNAME CMD         NAME            TYPE      MODE    STATUS
runnable         TESTDB SELECT      v$Session       TAB       IS      GRANT
runnable         TESTDB SELECT      V$LOCK          TAB       IS      GRANT

OUTPUTING results to an output table

This part deals with generalizing the procedure by creating an output table and also a filtered WHERE clause for the view after its creation. To that purpose I code a helping procedure call sp_OutputAndFilterResults.

The procedure gets the view name, output table name and the filter condition where clause (null if empty). The Select into/Bulk copy option should be on for the procedure. The output table name should be new (if a table by that name exists it will be dropped by the stored procedure). The procedure generated a dynamic SELECT * into <table name> from <View Name> WHERE <where filtering condition string>. And then executes it dynamically.

Here is the procedure code and an example of how to use it in combination with my first examples.

Script C – The sp_OutputAndFilterResults source code:

 -- =============================================
 -- =============================================
 -- Author:        Eli Leiba
 -- Create date: 08-2009
 -- Description:  
 -- =============================================
 CREATE PROCEDURE sp_OutputAndFilterResults
       -- Add the parameters for the stored procedure here
       @ViewName varchar(50) ,
       @OutputTable varchar(50),
     @WhereClause varchar(50)= NULL
 AS
 BEGIN
    -- Declare local variables for the procedure
     declare @dropTSQL varchar(100)
     declare @TSQL     varchar(500)
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;
     -- IF OUTPUT TABLE name exists then drop it.
    -- (Check it by doing a select on sys.Objects system table).
     IF EXISTS
       (SELECT *from sys.Objects
         wherename = @OutputTable and
              TYPE_DESC = 'USER_TABLE')
     BEGIN
        SET @dropTSQL = 'drop table ' + @OutputTable
        EXEC(@dropTSQL)
     END
     -- NOW CONSTRUCT THE TSQL select into statement
     -- Here is the BASIC SELECT INTO
     SET @TSQL = 'SELECT * into ' + @OutputTable
                 + ' FROM '+ @ViewName
     -- IF where exists the add where clause
     if @WhereClause IS NOT NULL 
     BEGIN
         SET @TSQL = @TSQL + ' WHERE '+ @WhereClause
     END
     -- Now execute the statement
     print (@TSQL)
     EXEC (@TSQL)
     -- Now set nocount off again at the exit
     -- point of the stored procedure.
     SET NOCOUNT OFF;
 END
GO

Script D - Here is an example of how to use the procedure

-- create the v$session view from sp_who procedure
exec sp_ConvProc2View @procName = 'sp_who',@viewName ='v$Session'
-- Check for all the sessions of database master by doing a filter.  Create an output table called  output_sess
exec sp_OutputAndFilterResults
      @ViewName ='V$SESSION'  ,
      @OutputTable ='output_sess' ,
      @WhereClause ='DBname = ''master'''
 -- now check the output_sess output table for results.
 SELECT * FROM output_sess

Conclusion

The method I've shown here is a way to take a stored procedure that usually is executed by using exec statement and transform its execution to a query from a view thus enabling a query with join to other tables or view with the procedure's result set.

Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 18 years experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies.