Hi there,

System Info: MS - Sql server 2000 SP3a

I've one stored procedure which returns result of a query in XML format using FOR XML AUTO clause, say it SP1
Now I've requirement that I need to call this SP from another NEW SP (say it as SP2) and use the result produced by SP1.

One way is to make an output parameter in SP1 and resolve this issue but the problem is that change will leads to big impact on existing system.

Can anyone please give some other options? Please find below sample code for "NORTHWIND" database.

==========SP 1 - START===============
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP1]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[SP1] AS

RETURN SELECT CategoryID, CategoryName FROM Categories FOR XML AUTO


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
====================SP1 - END=================

====================SP2 - START=================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP2]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [DBO].[SP2] AS

DECLARE @ABC VARCHAR(1000)

EXEC @ABC = SP1

SELECT @ABC

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
====================SP2 - END=================