September 7, 2009 at 2:57 am
Hi,
I have an SP that return an SELECT FOR XML RAW. What I want is to re-use this SP calling inside other SP but when I do this the result of 1st SP appears on RESULT of my 2nd SP.
I need the result of 1st SP to work into 2nd. So I use an OUTPUT parameter to return the result into XML variable. However the 2nd SP still return the 1st output (what is normal).
What I want to know is there is any way of executing a SP but indicating that I don't wanna receive the output!?
Best regards :-),
Cláudio
September 7, 2009 at 5:08 am
Have u used a XML variable in your 2ndSP to receive the output?
September 7, 2009 at 5:11 am
claudiosil100 (9/7/2009)
I need the result of 1st SP to work into 2nd.
In the 1st SP. Make the return type as varchar or something..
refer below example
--creating some temp table for reference
create table #temp ( testName varchar(50))
insert into #temp values ( 'abc'); insert into #temp values ( 'abc1');
insert into #temp values ( 'abc2'); insert into #temp values( 'abc3');
insert into #temp values ( 'abc4'); insert into #temp values ( 'abc5');
insert into #temp values ( 'abc6'); insert into #temp values ( 'abc7');
Declare @XML XML
SET @XML = (SELECT * from #temp FOR XML RAW)
select cast(@xml as varchar(max))
September 7, 2009 at 5:31 am
savee (9/7/2009)
Have u used a XML variable in your 2ndSP to receive the output?
Like
DECLARE @x XML
SET @x = SP @param1, @param2
??
Don't work the return only accept an INTEGER as return value
September 7, 2009 at 5:33 am
savee (9/7/2009)
Have u used a XML variable in your 2ndSP to receive the output?
I can't because the output of SP1 it's an XML
Msg 257, Level 16, State 3, Procedure spiappGetTrv, Line 339
Implicit conversion from data type xml to varchar(max) is not allowed. Use the CONVERT function to run this query. :S
September 7, 2009 at 5:43 am
claudiosil100 (9/7/2009)
I can't because the output of SP1 it's an XML
In the 1st SP. Make the return type as varchar or something..
refer below example
--creating some temp table for reference
create table #temp ( testName varchar(50))
insert into #temp values ( 'abc'); insert into #temp values ( 'abc1');
insert into #temp values ( 'abc2'); insert into #temp values( 'abc3');
insert into #temp values ( 'abc4'); insert into #temp values ( 'abc5');
insert into #temp values ( 'abc6'); insert into #temp values ( 'abc7');
Declare @XML XML
SET @XML = (SELECT * from #temp FOR XML RAW)
select cast(@xml as varchar(max))
September 7, 2009 at 5:48 am
sudhanva (9/7/2009)
claudiosil100 (9/7/2009)
I can't because the output of SP1 it's an XML
In the 1st SP. Make the return type as varchar or something..
refer below example
--creating some temp table for reference
create table #temp ( testName varchar(50))
insert into #temp values ( 'abc'); insert into #temp values ( 'abc1');
insert into #temp values ( 'abc2'); insert into #temp values( 'abc3');
insert into #temp values ( 'abc4'); insert into #temp values ( 'abc5');
insert into #temp values ( 'abc6'); insert into #temp values ( 'abc7');
Declare @XML XML
SET @XML = (SELECT * from #temp FOR XML RAW)
select cast(@xml as varchar(max))
I understand your solution and I thank but there is still a problem..I can't change the first SP output format..it's in use for other applications.
September 7, 2009 at 6:03 am
Have a look at this url
check the code below:
----------------------------------------
create proc sp_test123
as
begin
SET NOCOUNT ON;
create table #temp ( testName varchar(50))
insert into #temp values ( 'abc'); insert into #temp values ( 'abc1');
insert into #temp values ( 'abc2'); insert into #temp values( 'abc3');
insert into #temp values ( 'abc4'); insert into #temp values ( 'abc5');
insert into #temp values ( 'abc6'); insert into #temp values ( 'abc7');
Declare @XML XML
SET @XML = (SELECT * from #temp FOR XML RAW)
select @xml
end
go
------------------------------
create table #temp1 ( data xml)
insert into #temp1
exec sp_executesql sp_test123
select * from #temp1
drop table #temp1
September 7, 2009 at 6:18 am
Hi Cláudio,
Kindly provide some sample of your both SPs.
"Don't limit your challenges, challenge your limits"
September 7, 2009 at 6:35 am
sudhanva (9/7/2009)
Have a look at this urlcheck the code below:
----------------------------------------
create proc sp_test123
as
begin
SET NOCOUNT ON;
create table #temp ( testName varchar(50))
insert into #temp values ( 'abc'); insert into #temp values ( 'abc1');
insert into #temp values ( 'abc2'); insert into #temp values( 'abc3');
insert into #temp values ( 'abc4'); insert into #temp values ( 'abc5');
insert into #temp values ( 'abc6'); insert into #temp values ( 'abc7');
Declare @XML XML
SET @XML = (SELECT * from #temp FOR XML RAW)
select @xml
end
go
------------------------------
create table #temp1 ( data xml)
insert into #temp1
exec sp_executesql sp_test123
select * from #temp1
drop table #temp1
The example of the site don't help me.
I can't do SET @x = exec sp1 @param1, @param2.
The SP1 return an XML. And a return value.
When I execute the SP1 exec sp1 @param1, @param2 I don't want to see the XML. That's why I put an output parameter.
But this don't prevent the xml be show.
Thanks for your effort
September 7, 2009 at 6:52 am
kruti (9/7/2009)
Hi Cláudio,Kindly provide some sample of your both SPs.
Hi Kruti
something like this
CREATE PROC SP1
@param1 DATETIME,
@param2 INT
AS
SELECT field1,
field2,
field3,
field4,
field5
FROM table1
WHERE field1 = @param2
FOR XML RAW
GO
CREATE PROC SP2
AS
DECLARE @x XML
-- (get here the SP1 XML)
I tried this:
1 - SET @x = exec SP1 @param1, @param2
2 - INSERT INTO #tmpTable
3 - exec SP1 @param1, @param2, @param3 OUTPUT
-- After this I wanna do some updates using the @x (xml)
GO
1 - (this doesn't work)
2 - (doesn't work too! Because FOR XML can't be used on INSERT INTO)
3 - (I modified the SP1 to include and OPTIONAL parameter which is output type but the SELECT I do inside SP1 is always returned too)
Thanks
September 7, 2009 at 6:53 am
Hi claudiosil100
Try this:
---==============================================
-- we test on tempdb
USE tempdb;
GO
---==============================================
-- a procedure which returns XML
IF (OBJECT_ID('usp_TestXML') IS NULL)
EXECUTE ('CREATE PROCEDURE usp_TestXML AS SELECT 1');
GO
ALTER PROCEDURE usp_TestXML
AS
SELECT CONVERT(XML, 'value');
GO
---==============================================
-- usual call
EXECUTE usp_TestXML;
---==============================================
-- INSERT INTO ... EXECUTE ... call
DECLARE @ret TABLE (Scrap XML);
INSERT INTO @ret
EXECUTE usp_TestXML;
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
September 7, 2009 at 7:15 am
Florian Reischl (9/7/2009)
Hi claudiosil100Try this:
---==============================================-- we test on tempdb
USE tempdb;
GO
---==============================================
-- a procedure which returns XML
IF (OBJECT_ID('usp_TestXML') IS NULL)
EXECUTE ('CREATE PROCEDURE usp_TestXML AS SELECT 1');
GO
ALTER PROCEDURE usp_TestXML
AS
SELECT CONVERT(XML, 'value');
GO
---==============================================
-- usual call
EXECUTE usp_TestXML;
---==============================================
-- INSERT INTO ... EXECUTE ... call
DECLARE @ret TABLE (Scrap XML);
INSERT INTO @ret
EXECUTE usp_TestXML;
Greets
Flo
Hi Flo,
How can I apply this example to my problem? :S
My usp_TestXML return an XML, using FOR XML RAW that it's used by another users/applications I can't modify to something like that.
Thanks,
Cláudio
September 7, 2009 at 7:26 am
would be the perfect way without any 3rd parameter to say if it is normal or is being called by another SP
IF @SP2
SELECT field1, field2, field3
FROM table1
ELSE
SELECT field1, field2, field3
FROM table1
FOR XML RAW
and this could already be put back on a temp table and then select the FOR XML RAW.
If I can't find other solution I will do like this. 🙂
Thanks to all who helped!
September 7, 2009 at 7:29 am
Hi Cláudio
claudiosil100 (9/7/2009)
How can I apply this example to my problem? :SMy usp_TestXML return an XML, using FOR XML RAW that it's used by another users/applications I can't modify to something like that.
My "SELECT CONVERT(XML, '...')" was an example, which returns XML like "XML RAW". You can use a temp table and "INSERT INTO @myTemp EXECUTE myProc" to redirect the client output to the temp table. The structure of the temp table has to match the structure of the data returned by the called procedure, in your case one column with data type XML.
Try this:
-- Temp table to get the output of SP1
DECLARE @t TABLE (Scrap XML);
-- Execute SP1 and redirect its output to the temp table
INSERT INTO @t
EXECUTE SP1 @param1, @param2;
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply