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.
DECLARE @Server VARCHAR(50)
SET @Server = @@SERVERNAME
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.
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.
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.
SET @CRLF = char(10)
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.
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.
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
