Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Store result of stored procedure into a xml/nvarchar(max) variable Expand / Collapse
Author
Message
Posted Thursday, February 16, 2012 4:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 12, 2013 2:33 PM
Points: 9, Visits: 108
I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.

Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1

I declared a nvarchar(max) variable and trying to saved the result of StoredProc1

Declare @xml nvarchar(max)

EXEC @xml = StoredProc1 @Id

This doesn't work as expected as @xml doesn't get any value assigned or rather I would say

EXEC @xml = StoredProc1 @Id

outputs the entire xml whereas it should just save the xml in a variable.

Any suggestions as to what is going wrong
Post #1253537
Posted Thursday, February 16, 2012 4:59 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
THere is something called as Output Parameters while defining a Stored Procedures. Thats what you are looking for .

Here is the link : CREATE PROCEDURE

To be precise, Example C in Passing Parameters is what you are after!
Post #1253546
Posted Thursday, June 21, 2012 7:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:47 PM
Points: 133, Visits: 1,270
ColdCoffee (2/16/2012)
THere is something called as Output Parameters while defining a Stored Procedures. Thats what you are looking for .

Here is the link : CREATE PROCEDURE

To be precise, Example C in Passing Parameters is what you are after!


OK, I'm going to interject here. I realize the OP has probably moved on given that this exchange was a few months ago, but I am looking for the same thing. While they MAY have been a suitable option for the OP, they are NOT suitable for what I need to do and therefore it is quite possible that they are not what the OP was looking for either.

I have a similar situation where I need to compare the results of two stored procedures that return results using FOR XML EXPLICIT. While there are other ways to approach the problem in my case (I'm doing acceptance testing to verify the correctness of a new, more efficient and performant version of an existing procedure since the original is horribly inefficient) the ability to assign the results of each procedure to a variable for comparison would make things much simpler since in my case I am doing this using SQL 2K and the EXCEPT/INTERSECT statements are not available.

What the OP asked for may not be possible, but it is not really correct to just assume that he/she is looking for output parameters when those might not be suitable for his/her case at all.


--Andrew
Post #1319273
Posted Thursday, June 21, 2012 9:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:41 AM
Points: 403, Visits: 300
Andrew in WV (6/21/2012)
ColdCoffee (2/16/2012)
THere is something called as Output Parameters while defining a Stored Procedures. Thats what you are looking for .

Here is the link : CREATE PROCEDURE

To be precise, Example C in Passing Parameters is what you are after!


OK, I'm going to interject here. I realize the OP has probably moved on given that this exchange was a few months ago, but I am looking for the same thing. While they MAY have been a suitable option for the OP, they are NOT suitable for what I need to do and therefore it is quite possible that they are not what the OP was looking for either.

I have a similar situation where I need to compare the results of two stored procedures that return results using FOR XML EXPLICIT. While there are other ways to approach the problem in my case (I'm doing acceptance testing to verify the correctness of a new, more efficient and performant version of an existing procedure since the original is horribly inefficient) the ability to assign the results of each procedure to a variable for comparison would make things much simpler since in my case I am doing this using SQL 2K and the EXCEPT/INTERSECT statements are not available.

What the OP asked for may not be possible, but it is not really correct to just assume that he/she is looking for output parameters when those might not be suitable for his/her case at all.


You may get a better/faster answer posting an original thread in a forum not specific to 2008? (Sorry that I'm not providing an actual answer for you.)
Post #1319384
Posted Sunday, May 4, 2014 10:33 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 5:03 AM
Points: 579, Visits: 2,520
Just posting an answer to this forum just in case anyone else hits the problem.
The return value from a stored procedure is always an integer and it was never intended to pass back programmatic values. They are for status and are used for communicating things like warnings and errors within the data or code. By convention, a 0 (zero) means everything is OK.
You can pass back integer values in the 'return' statement by passing an optional integer parameter to RETURN.


CREATE PROCEDURE Tryout2
AS
RETURN
(4)
GO
--The variable you assign this to  must be declared in the batch, stored procedure, or function before it is used in an EXECUTE statement.
DECLARE @Return INT
EXECUTE
@Return =Tryout2
SELECT @Return


When you google for '@return_status' you'll see that it is an optional integer variable that stores the return status of a module.

In order to pass XML, or any other type of data, from a stored procedure you must use Outout Variables as has already been said. They are extraordinarily useful.





Best wishes,

Phil Factor
Simple Talk
Post #1567341
Posted Tuesday, May 6, 2014 9:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 3:22 AM
Points: 69, Visits: 495
Andrew in WV (6/21/2012)
ColdCoffee (2/16/2012)
THere is something called as Output Parameters while defining a Stored Procedures. Thats what you are looking for .

Here is the link : CREATE PROCEDURE

To be precise, Example C in Passing Parameters is what you are after!


OK, I'm going to interject here. I realize the OP has probably moved on given that this exchange was a few months ago, but I am looking for the same thing. While they MAY have been a suitable option for the OP, they are NOT suitable for what I need to do and therefore it is quite possible that they are not what the OP was looking for either.

I have a similar situation where I need to compare the results of two stored procedures that return results using FOR XML EXPLICIT. While there are other ways to approach the problem in my case (I'm doing acceptance testing to verify the correctness of a new, more efficient and performant version of an existing procedure since the original is horribly inefficient) the ability to assign the results of each procedure to a variable for comparison would make things much simpler since in my case I am doing this using SQL 2K and the EXCEPT/INTERSECT statements are not available.

What the OP asked for may not be possible, but it is not really correct to just assume that he/she is looking for output parameters when those might not be suitable for his/her case at all.


Actually, I think in this case, the OP does need OUTPUT Parameters to solve his problem as has been suggested.
Post #1568024
Posted Wednesday, May 7, 2014 5:04 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:10 AM
Points: 417, Visits: 574
;
Post #1568380
Posted Thursday, June 26, 2014 5:56 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:05 PM
Points: 355, Visits: 870
SSIS has an XML source and XML task. THe XML task can validate your data, merge it with another xml set and even do comparisons between the sets (You would set the operation type to "Diff" in this last case). Worth looking into if you have SSIS ( as I know this is a t-sql forum) and you dont have to to worry about lack if INTERSECT in sql server 2000.
Post #1586692
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse