February 16, 2012 at 4:39 pm
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
February 16, 2012 at 4:59 pm
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!
June 21, 2012 at 7:24 am
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
June 21, 2012 at 9:09 am
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.)
May 4, 2014 at 10:33 am
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.
[font="Courier New"]
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[/font]
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
May 6, 2014 at 9:11 am
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.
May 7, 2014 at 5:04 am
;
June 26, 2014 at 5:56 pm
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.
----------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply