CODEWITHSUNDEEP
sqlsql-serverstored-procedures
blarson
blarson

Reputation: 365

OpenRowSet Stored Procedure Returned Data Capture

Situation: SQL Server 2008 query calling a stored procedure on Informix IDS 11.7

I need to capture all returned data from N'SELECT * in a variable using dynamic SQL (do not want in a temp table).

What works: data is returned fine from Informix, but I need it in a column name for a form.

What I've tried:

  1. N'SELECT @Credit = * does not work as there is no column name from the Informix SP to refer to as in N'SELECT @Credit = cx.SP_Credit

  2. Tried a linked server using OpenQuery but actually have more trouble with that -- can't get data from Informix returned.

Generic code:

DECLARE @parameter NVARCHAR(32)
DECLARE @Credit    NVARCHAR (32)

SET @parameter = N'@Credit NVARCHAR (32)  OUTPUT'

DECLARE @SQL_TEXT   NVARCHAR(1000)

SET @SQL_TEXT = N'Select * from OPENROWSET ( ''MSDASQL'' , ''DSN=Informix_DB'' , ''execute procedure an_informix_stored_procedure ("parameter1", "parameter2")'') as cx'

EXECUTE sp_executesql
    @SQL_TEXT,
    @parameter,
    @Credit OUTPUT;

select @Credit as credit;

Results so far (I want the "1-3" stored in the Credit column that is currently NULL):

enter image description here

Ideas? Thanks.

Upvotes: 0

Views: 534

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

DECLARE @parameter NVARCHAR(32)
DECLARE @Credit    NVARCHAR (32)

SET @parameter = N'@CreditOut NVARCHAR (32)  OUTPUT'

DECLARE @SQL_TEXT   NVARCHAR(1000)

SET @SQL_TEXT = N'SET @CreditOut = (Select * from OPENROWSET ( ''MSDASQL'' , ''DSN=Informix_DB'' , ''execute procedure an_informix_stored_procedure ("parameter1", "parameter2")'') as cx)'

EXECUTE sp_executesql
    @SQL_TEXT,
    @parameter,
    @CreditOut = @Credit OUTPUT;

select @Credit as credit;

Upvotes: 1

Related Questions