Dynamic Table from stored procedure

Question

I'm trying to create a temp table from stored procedures, from this link

In the string he defines the sql server version. Our clients have different types of sql servers, from 2005 until 2012.

String: 'SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;','EXEC getBusinessLineHistory'

How can I use that command independently from sql server plataform

score 0 · Accepted Answer · answered Mar 12 '14 at 19:51

The OPENROWSET creates a dynamic link to a remote server.

http://technet.microsoft.com/en-us/library/ms190312.aspx

You can create a dynamic TSQL call to a dynamic link with changing parameters. Below is sample code. This can be converted into a store procedure with a @my_Server passed as a parameter.

Please note, this does not support multiple calls at the same time since only one table exists.

You can not use a local temp table since there might be a scoping issue with EXEC calling sp_executesql inside a stored procedure.

These are things you will need to research.

-- Set the server info
DECLARE @my_Server SYSNAME;
SET @my_Server = 'Server=(local)\SQL2008';

-- Clear the staging table
truncate table STAGE.dbo.MYTABLE;

-- Allow for dynamic server location
DECLARE @my_TSQL NVARCHAR(2048);

SET @my_TSQL =
'INSERT INTO STAGE.dbo.MYTABLE SELECT * FROM OPENROWSET(''SQLNCLI'',' + @my_TSQL + 
';Trusted_Connection=yes;'', ''EXEC usp_My_Stored_Procedure'')';

-- Run the dynamic remote TSQL
exec sp_executesql @my_TSQL;

Dynamic Table from stored procedure

1 Answers1