存储过程间表值传递

最新推荐文章于 2021-11-11 09:13:51 发布

原创最新推荐文章于 2021-11-11 09:13:51 发布 · 204 阅读

0 ·

CC 4.0 BY-SA版权

SQL Server 专栏收录该内容

13 篇文章

订阅专栏

本文详细介绍了一组SQL存储过程的创建与使用，包括产品信息的获取、处理及多表数据整合。通过具体实例，展示了如何利用存储过程进行高效的数据管理和操作。

--测试数据准备
Create table Product (ProductID varchar(18),cName varchar(50))
insert into Product Select '80100212800','A'
insert into Product Select '80100250006','B'
insert into Product Select '80101038000','C'

Create type dbo.OrderDetailsTemp as Table
(
OrderID CHAR(12) , --订单号
ProductID CHAR(11) --产品编号
)

--测试
   Exec ProductGet

Create PROCEDURE ProductGet
AS
Begin
Declare @sql as nvarchar(max)
CREATE TABLE #OrderDetailsTemp
(
OrderID CHAR(12) ,       --订单号
ProductID CHAR(11)        --产品编号
)
   INSERT INTO #OrderDetailsTemp Exec ProductOne '80100212800'


   INSERT INTO #OrderDetailsTemp Exec ProductOne '80100250006'

INSERT INTO #OrderDetailsTemp Exec ProductOne '80101038000'

Select * From #OrderDetailsTemp
End

Create PROCEDURE ProductOne
@cProductID as varchar(18)
AS
Begin
Declare @sql as nvarchar(max)

DECLARE @tmpRows as OrderDetailsTemp

Insert into @tmpRows(OrderID,ProductID)
   Select ToP 1 'T1',ProductID From Product Where ProductID = @cProductID

   Exec ProductTwO @cProductID,@tmpRows
   -- EXEC sp_executesql Exec ProductTwO @cProductID,N'@tmpRows as OrderDetailsTemp Out',@tmpRows

--SET @sql='INSERT INTO #OrderDetailsTemp Exec ProductTwO '''+@cProductID+ ''',@tmpRows'
--EXEC (@sql) --插入数据到赠品表临时表
/*

SET @sumqty = 0
SET @sql = ' SELECT @sumqty=ISNULL(SUM(RemainQTY),0) FROM #OrderDetailsTemp WHERE '
+ @ProdExp

EXEC sp_executesql @sql, N'@sumqty DECIMAL(15,2) out',@sumqty OUT
*/
   Select * from @tmpRows
   -- Select * From #OrderDetailsTempX
End

Create PROCEDURE ProductTwO
   @cProductID as varchar(18)
   ,@ManyRows as OrderDetailsTemp READONLY
AS
Begin
CREATE TABLE #OrderDetailsTempY
(
OrderID CHAR(12) ,       --订单号
ProductID CHAR(11)        --产品编号
)
Insert into #OrderDetailsTempY(OrderID,ProductID)
   Select ToP 1 'T2',ProductID From Product Where ProductID = @cProductID

   Insert into #OrderDetailsTempY(OrderID,ProductID) Select OrderID,ProductID From @ManyRows

   Select * From #OrderDetailsTempY
End