CODEWITHSUNDEEP
t-sqlsql-server-2008stored-proceduresresultsetoutput-parameter
Will Marcouiller
Will Marcouiller

Reputation: 24132

MSTSQL: Can a SP return both an out param and a result set

I wish to know whether it's feasible to have a TSQL stored procedure return both a result set and the output parameter like so.

create procedure uspReadMyXmlInformation(@myXmlDoc xml, @myProductNum varchar(18) output) as
    begin
        set nocount on;

        declare @myXmlContent table(MyOrderId varchar(12) not null
                                   ,CreatedAt datetime not null)

        insert into @myXmlContent
            select x.c.value('MyOrderID[1]', 'varchar(12)')
                    x.c.value('CreatedAt[1]', 'datetime')
                from @myXmlDoc.nodes('MyRootNodeName/MyChildNodeName') x(c)

        set @myProductNum='MyProductNum'

        select *
            from @myXmlContent

        return;
    end

So, what happens here is that I can either obtain the result set, when I remove the output parameter, or I obtain the output parameter and the result set is always empty (0=count(*)).

Is there anyway I can obtain both with the same stored procedure or I'd better split them?

I think it's doable from this post in Oracle. I'd like to achieve the same in SQL Server, although constrained to the 2008 version.

Oracle stored procedure: return both result set and out parameters

What I like from doing it using the same SP is that both the result set and the output parameter represent information I read from the XML document. So, the name of the SP says it all, somehow.

EDIT

As some think it might be a duplicate of:

Possible to return an out parameter with a DataReader

I don't think it is as answers there are related as to how the DataReader behaves more than how it could be achieved with TSQL.

The fact is that I get the the value from the output parameter, but I don't get it from the result set at all, it's always returning null.

So, I'm on a SQL Server only project and I'd need that. Otherwise, I'll split it in two, if I can't achieve it in a timely fashion.

Here's how it's used:

declare @xmlInformationData table(MyOrderId varchar(12) not null
                                  ,CreatedAt datetime not null)
insert into @xmlInformationData
    execute uspReadMyXmlInformation @myXmlDoc, @myProductNum output

while 0<(select count(*) from @xmlInformationData)
    begin
        -- This will never be executed because I have no rows in @xmlInformationData
        -- And yet, before the loop, I have my out param value!
    end

Upvotes: 0

Views: 228

Answers (1)

HABO
HABO

Reputation: 15816

The following is a trivial demonstration of using both an output parameter and result set. Try running it a few times and the results should vary.

create procedure Arthur( @TheAnswer as Int Output ) as
  begin

  -- Set the value of the output parameter.
  set @TheAnswer = 42;

  -- Generate a single row most of the time.
  select GetDate() as NextVogonPoetryReading
    where DatePart( millisecond, GetDate() ) < 750;

  end;
go 1

-- Declare the variables for the test.
declare @HHGTTU as Table ( HHGTTUId Int Identity, NextVogonPoetryReading DateTime );
declare @SixTimesNine as Int;

-- Execute the SP once so that the   while   loop might.
insert into @HHGTTU ( NextVogonPoetryReading )
  execute Arthur @TheAnswer = @SixTimesNine Output;

-- See what happens.
while exists ( select Pi() from @HHGTTU )
  begin
  -- See where we are at.
  select @SixTimesNine as SixTimesNine, Max( HHGTTUId ) as MaxHHGTTUId, Max( NextVogonPoetryReading ) as MaxNextVogonPoetryReading
    from @HHGTTU;
  -- Reset.
  delete from @HHGTTU;
  set @SixTimesNine = 54;
  select @SixTimesNine as SixTimesNineAfterReset;
  waitfor delay '00:00:00.100';
  -- Execute the SP again.
  insert into @HHGTTU ( NextVogonPoetryReading )
    execute Arthur @TheAnswer = @SixTimesNine Output;
  end;

Aside: My apologies for the trauma introduced into your life by my mention of a DataReader. I was merely attempting to pass on my experience in a C# application without getting into the weeds of exactly what sort of connection to the database you are using, which driver(s) might be involved, ... .

Upvotes: 1

Related Questions