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
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
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.