Exec SP and ignore the output (occasionally)

Question

Post reply

Exec SP and ignore the output (occasionally)

Cláudio Silva

SSC Eights!

Points: 986
More actions
September 7, 2009 at 2:57 am

#133361

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

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

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

savee Say Hey Kid Points: 711 More actions · Answer 1

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

sudhanva Ten Centuries Points: 1343 More actions · Answer 2

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

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 3

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

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 4

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

sudhanva Ten Centuries Points: 1343 More actions · Answer 5

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

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 6

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.

sudhanva Ten Centuries Points: 1343 More actions · Answer 7

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

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 8

Hi Cláudio,

Kindly provide some sample of your both SPs.

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

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 9

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

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 10

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 11

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

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 12

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

Cláudio Silva SSC Eights! Points: 986 More actions · Answer 13

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!

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 14

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