Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server / SQL Server 2008

Exporting Stored Procedure Results into Tables Dynamically with T-SQL

Rate me:
Please Sign up or sign in to vote.
4.85/5 (5 votes)
11 Jan 2012CPOL4 min read 41.6K   553   14   2
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)


Written By
Technical Lead
United States United States
Mr. Kanumukula is a IT-Professional and has been in the industry since 1997.
• Around 15+ years of experience in the areas of system design & architecture, application programming, development, testing, deployments, execution with implementations & Roll-outs of business applications in cloud/on-premise.
• Experience with Dynamics 365 for F&O/Dynamics AX, D365 for Business Central/NAV,(Inc -Visual Studio.Net, X++, AL, C#, CSide, SharePoint, SQLServer, SFDC, Power-BI, Power Apps, Dataverse/Common Data Service, CRM, SSIS/SSRS/SSAS, BizTalk, IOT, IIOT, JAVA, AWS, GCP, Azure, API, etc)
• Experience in managing Operations & Data Center maintenance both on-premise & cloud hosting, infrastructure/applications assessments & build systems.
• Experience in ERP upgrades, Mulesoft API's,MS Dynamics 365,Azure administration & hosting, LCS-Life Cycle Services.
• Experience with Commitment to Quality, technical quality assurance(before, during & after development). Create partnership with project manager to give technical assistance regarding important decisions.
• Well-Versed with Agile, SCRUM & CMMI process & methodologies to support rapid iterative Quality software development.

A highly motivated, self-starter & problem solver with multi-tasking skills, Had managed and served through an established process to get the job done in a timely and efficient manner with an eye on every detail during ERP-implementations. Flexible to work under stress and have ability to prioritize workload and stay organized in a fast-paced environment.

Learned & adapted to new technologies & tools at ease, aggressively delivered tasks on-demand at a fast-pace satisfying the needs of product owners, product development, program managers, Vendors, Higher Management, Internal/External Clients.

Have a proactive & positive attitude with a willingness to do what it takes to complete the job. Self-managed work style within a team environment. Extremely detail & Customer service oriented.

Comments and Discussions

 
QuestionDynamic pivot Pin
odeddror28-Aug-14 2:37
odeddror28-Aug-14 2:37 
AnswerRe: Dynamic pivot Pin
Venkat Chaitanya Kanumukula13-Nov-14 3:53
Venkat Chaitanya Kanumukula13-Nov-14 3:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.