Exporting Stored Procedure Results into Tables Dynamically with T-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 

/********************************************************************** 
 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)
 MODIFIED BY  : VENKAT CHAITANYA KANUMUKULA 
 EXAMPLE 1	  : exec [XportStoredProc]  'StoredProcedureName 2,2' ( 2 Integer Params)
 EXAMPLE 2	  : exec [XportStoredProc]  'StoredProcedureName 1,1,''''Test''''' ( Integer and Varchar Params)
 EXAMPLE 3	  : exec [XportStoredProc]   StoredProcedureName    OR
				exec [XportStoredProc]  'StoredProcedureName'
			    This object can accept 'n' Number of Parameters.
 **********************************************************************/
 
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)
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'

BEGIN
-- This will drop table if exists and recreates it with the extension ProcedureName + '_Report'
-- Tested and Executed.
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)) + ''')'

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' 
--PRINT @SQL
EXEC sp_executeSQL @SQL
-- ServerOption Turned off
EXEC sp_serveroption  @Server,'DATA ACCESS','FALSE'
 
END