CODEWITHSUNDEEP
sqlsql-servert-sqlparametersoutput
user3658439
user3658439

Reputation: 163

SQL Server: Output parameter to table

I have the next T-SQL code:

DECLARE @A CHAR(1)
DECLARE @B CHAR(10)
DECLARE @C DECIMAL
DECLARE @D CHAR(1)

SET @A = 'S'
SET @B = '2015-03-23'
SET @C = 1
SET @D = 'P'

EXEC('CALL DATA_COLLECTOR(?,?,?,?)',@A, @B, @C, @D) at LINKED_SERVER_10

PRINT @D

@D is an input-ouput variable.

After running the code, the variable @D gets a result set (5 rows, 10 columns).

How can I copy the result set contained in @D to another table in my database?.

Thanks in advance.

Upvotes: 1

Views: 447

Answers (2)

sqluser
sqluser

Reputation: 5672

Your output cannot be CHAR(1)

If you ignore @D and just want to store the result set of your procedure into a table, then you have few choices to do:

  1. Table has been created before

    CREATE TABLE #Table (Field1 INT, Field2 VARCHAR(50));
INSERT INTO #Table
EXEC yourProcedure

To see the result

    SELECT *
    FROM #Table

  1. Create table at runtime using OPENROWSET

    sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE

SELECT * INTO #Table
FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC tempdb.dbo.yourProcedure')

To see the result

    SELECT *
    FROM #Table

Upvotes: 0

Mike
Mike

Reputation: 2559

To create a new table from the variable, use

SELECT * INTO newTable FROM @D

To put the data into an existing table, use

INSERT INTO existingTable SELECT * FROM @D

Upvotes: 1

Related Questions