SQL一个存储过程调用另一个存储过程 获取返回值问题.docVIP

2008-12-16 21:41 第一种方法: 使用output参数 USE AdventureWorks;GOIF OBJECT_ID ( Production.usp_GetList, P ) IS NOT NULL ????DROP PROCEDURE Production.usp_GetList;GOCREATE PROCEDURE Production.usp_GetList @product varchar(40) ???? , @maxprice money ???? , @compareprice money OUTPUT???? , @listprice money OUTAS????SELECT p.name AS Product, p.ListPrice AS List Price????FROM Production.Product p????JOIN Production.ProductSubcategory s ??????ON p.ProductSubcategoryID = s.ProductSubcategoryID????WHERE s.name LIKE @product AND p.ListPrice @maxprice;-- Populate the output variable @listprice.SET @listprice = (SELECT MAX(p.ListPrice)????????FROM Production.Product p????????JOIN?? Production.ProductSubcategory s ??????????ON p.ProductSubcategoryID = s.ProductSubcategoryID????????WHERE s.name LIKE @product AND p.ListPrice @maxprice);-- Populate the output variable @compareprice.SET @compareprice = @maxprice;GO 另一个存储过程调用的时候: Create Proc TestasDECLARE @compareprice money, @cost money EXECUTE Production.usp_GetList %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)))+. 第二种方法:创建一个临时表 create proc GetUserNameasbegin????select UserNameendCreate table #tempTable (userName nvarchar(50))insert into #tempTable(userName)exec GetUserNameselect #tempTable--用完之后要把临时表清空drop table #tempTable --需要注意的是，这种方法不能嵌套。例如：??procedure??? a?????begin????????? ...?????????insert??? #table???exec??? b?????end?????????procedure??? b?????begin????????? ...?????????insert??? #table????exec??? c?????????select???*???from??? #table???????end?????????procedure??? c?????begin????????? ...?????????select???*???from??? sometable?????end???--这里ａ调ｂ的结果集，而ｂ中也有这样的应用ｂ调了ｃ的结果集，这是不允许的，--会报“INSERT EXEC 语句不能嵌套”错误。