0

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
Will Marcouiller
  • 23,773
  • 22
  • 96
  • 162
  • 1
    You can have both, but the output parameter(s) may be populated "late", e.g. after closing the `DataReader` in a .NET application. – HABO Feb 08 '19 at 19:24
  • 1
    As @HABO says, you can have both, but the `out` variables are only available in general after the last result set has been processed. – Lucero Feb 08 '19 at 19:41
  • 1
    Possible duplicate of [Possible to return an out parameter with a DataReader](https://stackoverflow.com/questions/32052621/possible-to-return-an-out-parameter-with-a-datareader) – Lucero Feb 08 '19 at 19:50
  • Actually, there ain't any DataReader! I don't know where you guys saw a C# tag or the like, but there ain't any! It's a SQL Server only project, as this database's purpose is to serve as a data exchange. So, it goes and read data from side to side and pushes the information. So, no C# code around! So now, I obtain the output param value, but never do I obtain the result set data. And I need both. Anyone can help? – Will Marcouiller Feb 10 '19 at 10:31
  • @HABO please see above comment. No DataReader, no C# code. I don't know where you guys picked it up. So, I added a usage example which clearly shows that it's used within another procedure call. – Will Marcouiller Feb 10 '19 at 10:39
  • 1
    @WillMarcouiller The problem you described typically happens when calling the server remotely and it is not a problem of the `DataReader` but rather due to the nature of the TDS protocol used to communicate with the SQL Server. I am unable to reproduce your problem when I run the query on the server, [see screenshot](https://i.ibb.co/ncJ9fQz/10-02-2019-14-27-05.png). – Lucero Feb 10 '19 at 13:31
  • @Lucero Then it must be something with my XML, then. Maybe I'm confusing something as I have much manipulation to do with it, like read its values into a table variable, feed the missing information, and then convert it back to an XML and pass it to another procedure for further processing. This is to maximize code reuse throught a workflow. Thanks for this screenshot of yours. I helped. – Will Marcouiller Feb 10 '19 at 14:12

1 Answers1

1

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, ... .

HABO
  • 15,314
  • 5
  • 39
  • 57
  • Making a test with "random" results like you did does not seem very sensible, as 25% of the time this will display the "failing" test case of the original issue. – Lucero Feb 10 '19 at 23:23
  • @Lucero The intent was to provide an example that terminates and that has varying behavior, including sometimes returning zero rows, while always returning an output parameter. It addresses the question in the title of the OP's post: "Can a SP return both an out param and a result set". It does not resolve the OP's issue where the "failing" case is that only one or the other output appears to be possible, but never both. – HABO Feb 11 '19 at 00:41
  • Fair enough, I did not downvote or anything but merely pointed out that the 0-row-result was not a good example here. Something like 1 or 2 rows would have been better if you want to have "randomness". :) – Lucero Feb 11 '19 at 00:46
  • Actually my problem was that I had this procedure, returning a result set and an output parameter, on the third hierarchy level which MSSQL doesn't like much. Instead, I had to use a table-valued function and a separate scalar-valued function. And because I didn't like this fashion, I refactored my code differently and yes, now my `uspReadXmlDataInformation` procedure returns the wanted information flawlessly. So, morale of the story is not to line-up over two nested result set SP, otherwise weird behaviour occurs. – Will Marcouiller Feb 28 '19 at 00:20