Exec SP and ignore the output (occasionally)

  • 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

  • Have u used a XML variable in your 2ndSP to receive the output?

  • 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))

  • 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

  • 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

  • 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))

  • 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.

  • 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

  • Hi Cláudio,

    Kindly provide some sample of your both SPs.

    "Don't limit your challenges, challenge your limits"

  • sudhanva (9/7/2009)


    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

    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

  • 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

  • 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

  • Florian Reischl (9/7/2009)


    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

    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

  • 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!

  • Hi Cláudio

    claudiosil100 (9/7/2009)


    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.

    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

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply