Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

Exporting Stored Procedure Results into Tables Dynamically with T-SQL

4.85/5 (5 votes)
11 Jan 2012CPOL4 min read 42.2K   553  
T-SQL Code for Converting Stored Procedure results to Tables Dynamically

Introduction

This article is intended primarily for SQL Server database users who wanted to export their existing stored procedures output into tables with out re-coding or changing them. In real time scenario it is not possible to change all the existing stored procedure objects in a legacy database system as most of the business logic is written in Stored Procedures.

The code attached here is basically to demonstrate a method to export a stored procedure results to a table, this object is developed keeping the performance aspect in view.The goal is to create a new object in the database with a very few lines of T-SQL code which can handle this without changing the existing database design & architecture and at the same time with out using any other programming languages and tools.

Exporting the stored procedures into tables only comes when we want to use them for some kind of business intelligence and reporting purposes in our day to day life. This code will be very handy in such scenarios. After executing this in the database it could be used by different applications from a front end or by other objects in the back end to create the tables from any stored procedure of user's choice.

Background

The code uses the SQL Server Object <span style="white-space: pre-wrap; ">OPENQUERY</span> for creating tables. For more information on the <span style="white-space: pre-wrap; ">OPENQUERY</span> functionality and its options refer online documentation at the following links http://msdn.microsoft.com/en-us/library/aa276848(v=sql.80).aspx

http://msdn.microsoft.com/en-us/library/ms188427(v=SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms188427.aspx

For information on PATINDEX function refer online documentation at http://msdn.microsoft.com/en-us/library/ms188395(v=SQL.90).aspx

For information on SUBSTRING function refer online documentation at http://msdn.microsoft.com/en-us/library/ms187748(v=SQL.90).aspx

Explanation of OPENQUERY, PATINDEX, SUBSTRING and SP_EXECUTESQL is out of scope of this article.

Using the code

This XportStoredProc object will create a table in the database with Stored Procedure name being passed to it (ex: StoredProcedureName_Report). Normally a new database is created in SQL Server and all tables are exported into it, Where that database doesn't have much traffic and used like a temp database. Again this is up to the users how they want to use it.

The ServerOption should be turned on, the 'DATA ACCESS' option is required to be set to TRUE on the SQL Server. User should have privileges to execute this ServerOption object.

SQL
/******  This needs to be set before you execute.   ******/
DECLARE @Server VARCHAR(50) 
SET @Server = @@SERVERNAME 
-- This ServerOption Should be Turned on to use the OPENQUERY function.
EXEC sp_serveroption  @Server,'DATA ACCESS','TRUE'  

This XportStoredProc stored procedure object can handle 'n' number of parameters. Following are few examples demonstrated on supplying the parameters to this object.

EXAMPLE 1 : exec [XportStoredProc] 'StoredProcedureName 2,2' ( 2 Integer Params)

EXAMPLE 2 : exec [XportStoredProc] 'StoredProcedureName 1,''''Test''''' (Integer and Varchar Params)

EXAMPLE 3 : exec [XportStoredProc] 'StoredProcedureName ''''AFA'''',1,''''Afghani'''''

EXAMPLE 4 : exec [XportStoredProc] StoredProcedureName OR exec [XportStoredProc] 'StoredProcedureName'

Note : If the stored procedure is passed twice to this XportStoredProc object, it drops the table if exists and recreates it with the new set of data.

Named this utility object as 'XportStoredProc', This could be changed as per the users choice. The schema is by default set to 'dbo' in the code, if you need to set to a different schema then change it.

SQL
/****** Object:  StoredProcedure [dbo].[XportStoredProc]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[XportStoredProc]
@SRCOBJECT varchar(500) ,
@SCHEMA varchar(100)= N'dbo'
AS   

variables here which are required for this object.

SQL
DECLARE @vsSQL nvarchar(4000)
DECLARE @SQL nvarchar(4000)
DECLARE @vsServerName varchar(50) 
DECLARE @vsSPName varchar(100) 
DECLARE @vsDestDBName varchar(100) 
DECLARE @vsDestTableName varchar(100) 
DECLARE @CRLF char(2) 

The Output table name is followed with the (_Report) extension and it can be customized as per the user's choice.

SQL
/********************************************************************** 
 CREATED BY      : VENKAT CHAITANYA KANUMUKULA 
 CREATED ON      : This is created and tested in SQL SERVER 2000 and SQL SERVER 2005.
 PURPOSE      : This Object is Created for exporting a Stored Procedure results into a new Table Name(StoredProcedureName+_Report).
 COMMENTS     : The Below Proc can handle any number of parameters.
        Tested on SQL Server 8.00.194 - RTM (Desktop Engine) ,SQL Server 9.00.4035.00 - SP3 (Developer Edition)
 **********************************************************************/

SET @CRLF = char(10)

-- If @SRCOBJECT does not have Parameter then to handle it
SELECT @SRCOBJECT = @SRCOBJECT + ' '
SELECT @vsServerName = '[' + Convert(VARCHAR,SERVERPROPERTY ('ServerName') ) + ']'
SELECT @vsSPName =  Convert(VARCHAR,db_name()) + '.' + @SCHEMA + '.' + @SRCOBJECT 
SELECT @vsDestTableName = Convert(VARCHAR,db_name()) + '.' + @SCHEMA + '.' +(SUBSTRING(@SRCOBJECT+'_Report',1, PATINDEX('% %', @SRCOBJECT +'_Report')-1))+'_Report'

using the above variables the string is built now and being passed to variable @SQL.

SQL
/* This will drop the table if exists and recreates with new set of data as per the parameters passed to it. Table is created with the extension ProcedureName + '_Report' */

SELECT @vsSQL = '
IF  EXISTS (SELECT * FROM sysobjects WHERE name = (N'''+(SUBSTRING(@SRCOBJECT+'_Report',1, PATINDEX('% %', @SRCOBJECT +'_Report')-1))+'_Report'+ ''') AND xtype = (N''U''))
DROP TABLE [dbo].['+(SUBSTRING(@SRCOBJECT+'_Report',1, PATINDEX('% %', @SRCOBJECT +'_Report')-1))+'_Report]'+@CRLF+'
  SELECT * INTO '  +  @vsDestTableName  +  ' FROM OPENQUERY ' + '(' + @vsServerName + ','
SELECT @vsSQL = @vsSQL + '''SET FMTONLY OFF  EXECUTE ' +  @vsSPName  + '' 
SELECT @SQL = SUBSTRING(@vsSQL,1,LEN(@vsSQL)) + ''')'

Finally sp_executeSQL will execute it.

SQL
EXEC sp_executeSQL @SQL  

The performance of this object is really good, since it lies on the database and does the required job like creating the table design & structure along with the data in very a less time.

Conclusion

The above mentioned demonstration of steps determine that exporting the stored procedures output into tables can be achieved with a very few lines of T-SQL code without re-coding or re-designing the existing database stored procedures.

Hence it is concluded that this T-SQL Stored procedure (XportStoredProc) Object with a very minimum of programming could do the trick of extracting the output from any Stored Procedure of user's choice.

Hope this article will serve the purpose. Any suggestions or corrections are most welcome.

Points of Interest

Basically after extracting the business logic from stored procedures into tables, now it has to be exported to some kind of presentation. Then the generic automated SSIS packages which are demonstrated in my earlier articles will be very handy to finish the presentation Job.

Visit the following links here to export the tables to desired formats which were created by this XportStoredProc Object.

Dynamic_Excel.aspx

Dynamic_Html.aspx

History

10 Th Jan 2012

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)