calling a stored procedure from within a stored proc

Hi Peeps

I have a SP that returns xml

I have writen another stored proc in which I want to do something like this:

Select FieldOne, FieldTwo, (execsp_that_returns_xml ( @.a, @.b) ), FieldThreefrom TableName

But it seems that I cant call the proc from within a select.

I have also tried

declare @.vxml

set @.v =execsp_that_returns_xml ( @.a, @.b)

But this again doesn't work

I have tried changing the statements syntax i.e. brackets and no brackets etc...,

The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @.v as a select from the temp table -

Which to be frank is god awful way to do it.

Any and all help appreciated.

Kal

Create a function instead of stored procedure. Because you can access function with select statements.

you will find more information about how to create function herehttp://msdn2.microsoft.com/en-us/library/ms186755.aspx

hope it helps

|||

Hi

Thanks for the reply - I had thought of using a function - But as I understand it functions are bad Karma where performance is concerned

for example:http://www.sql-server-performance.com/tips/user_defined_functions_p1.aspx

Thanks for the suggestion

Kal

|||

You can return your XML in Output parameter from your stored procedure see exampl from SQL Help below

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/afe3d86d-c9ab-44e4-b74d-4e3dbd9cc58c.htm:

GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @.Product varchar(40)
, @.MaxPrice money
, @.ComparePrice money OUTPUT
, @.ListPrice money OUT
AS
SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice;
-- Populate the output variable @.ListPprice.
SET @.ListPrice = (SELECT MAX(p.ListPrice)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.[Name] LIKE @.Product AND p.ListPrice < @.MaxPrice);
-- Populate the output variable @.compareprice.
SET @.ComparePrice = @.MaxPrice;
GO

DECLARE @.ComparePrice money, @.Cost money EXECUTE Production.uspGetList '%Bikes%', 700, @.ComparePrice OUT, @.Cost OUTPUTIF @.Cost <= @.ComparePrice BEGIN PRINT 'These products can be purchased for less than $'+RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'ENDELSE PRINT 'The prices for all products in this category exceed $'+ RTRIM(CAST(@.ComparePrice AS varchar(20)))+'.'