Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Store result of stored procedure into a xml/nvarchar(max) variable


Store result of stored procedure into a xml/nvarchar(max) variable

Author
Message
tome-473314
tome-473314
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 109
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
ColdCoffee
ColdCoffee
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2317 Visits: 5545
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!
Andrew in WV
Andrew in WV
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 1374
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
jeffem
jeffem
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 346
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.)
Phil Factor
Phil Factor
Right there with Babe
Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)Right there with Babe (750 reputation)

Group: General Forum Members
Points: 750 Visits: 2947
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
CKX
CKX
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 970
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.
ashok84.kr
ashok84.kr
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 677
;
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1120 Visits: 2015
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.

----------------------------------------------------
How to post forum questions to get the best help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search