September 7, 2009 at 7:34 am
claudiosil100 (9/7/2009)
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!
Now I understand your requirement! 😉
Have a look to BOL for "@@NESTLEVEL" it returns the depth of procedure call stack. If you call "SELECT @@NESTLEVEL" outside of any procedure it returns "0". If you select "@@NESTLEVEL" within a procedure it returns "1". If you select "@@NESTLEVEL" within a procedure called from another one it returns "2" and so on...
In your case this should help:
IF @@NESTLEVEL >= 2
SELECT field1, field2, field3
FROM table1
ELSE
SELECT field1, field2, field3
FROM table1
FOR XML RAW
Another option would be just to supply a third parameter "@outputAsXML BIT".
Greets
Flo
September 7, 2009 at 7:35 am
Florian Reischl (9/7/2009)
Hi Cláudioclaudiosil100 (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
I already tried that but I receive the following error:
Msg 6819, Level 16, State 5, Procedure spiappGetTrv, Line 22
The FOR XML clause is not allowed in a INSERT statement.
I'm doing something wrong? :rolleyes:
September 7, 2009 at 7:42 am
Florian Reischl (9/7/2009)
claudiosil100 (9/7/2009)
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!
Now I understand your requirement! 😉
Have a look to BOL for "@@NESTLEVEL" it returns the depth of procedure call stack. If you call "SELECT @@NESTLEVEL" outside of any procedure it returns "0". If you select "@@NESTLEVEL" within a procedure it returns "1". If you select "@@NESTLEVEL" within a procedure called from another one it returns "2" and so on...
In your case this should help:
IF @@NESTLEVEL >= 2
SELECT field1, field2, field3
FROM table1
ELSE
SELECT field1, field2, field3
FROM table1
FOR XML RAW
Another option would be just to supply a third parameter "@outputAsXML BIT".
Greets
Flo
The function @@NESTLEVEL seems OK, like the thrid parameter (the solution I was using)
Flo my English is so bad you could not see the problem? :unsure:
September 7, 2009 at 7:44 am
claudiosil100 (9/7/2009)
Msg 6819, Level 16, State 5, Procedure spiappGetTrv, Line 22The FOR XML clause is not allowed in a INSERT statement.
Oups... didn't ever try this with FOR XML and didn't know that this doesn't work. So you should use the @@NESTLEVEL or (better solution in my opinion) an additional parameter which specifies if XML should be returned.
September 7, 2009 at 7:47 am
claudiosil100 (9/7/2009)
Flo my English is so bad you could not see the problem? :unsure:
Your English, my English, our English - who cares :hehe:
Finally we know what we are speaking about and that's the only important thing. 😉
September 7, 2009 at 7:52 am
Florian Reischl (9/7/2009)
claudiosil100 (9/7/2009)
Msg 6819, Level 16, State 5, Procedure spiappGetTrv, Line 22The FOR XML clause is not allowed in a INSERT statement.
Oups... didn't ever try this with FOR XML and didn't know that this doesn't work. So you should use the @@NESTLEVEL or (better solution in my opinion) an additional parameter which specifies if XML should be returned.
Why you say the 3rd parameter it's the best solution? 🙂
About our English..I care because I'm not English but knowing a little helps a lot! 😛
September 7, 2009 at 8:08 am
claudiosil100 (9/7/2009)
Why you say the 3rd parameter it's the best solution? 🙂
A third parameter gives you the possibility to use both ways from everywhere. If you call your SP1 (the internal procedure) from more than one location you are able to choose your output for each caller.
About our English..I care because I'm not English but knowing a little helps a lot! 😛
Okay. So, probably it was my miss ;-). I'm in a hurry the whole day and jumping from meeting to meeting. Maybe I didn't read to careless.
September 7, 2009 at 8:22 am
Florian Reischl (9/7/2009)
claudiosil100 (9/7/2009)
Why you say the 3rd parameter it's the best solution? 🙂A third parameter gives you the possibility to use both ways from everywhere. If you call your SP1 (the internal procedure) from more than one location you are able to choose your output for each caller.
Ok, make sense! 🙂
Florian Reischl (9/7/2009)
About our English..I care because I'm not English but knowing a little helps a lot! 😛
Okay. So, probably it was my miss ;-). I'm in a hurry the whole day and jumping from meeting to meeting. Maybe I didn't read to careless.
I understand. No problem!
Thanks once again.
September 8, 2009 at 12:50 am
Hello, got solution?
"Don't limit your challenges, challenge your limits"
September 8, 2009 at 2:26 am
kruti (9/8/2009)
Hello, got solution?
Yes, the solution was create an 3rd parameter on the 1st SP which says if return an XML or an Table..after this I do an
INSERT INTO #tmp
EXEC SP1
And I work with this table and no result was sent to the user at 1st SP call. 😉
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply