如何使用存储过程将数据存储在临时表上?
问题描述:
我想根据下面的标量函数编写一个存储过程,以显示每年和每个产品有多少客户?如果一个产品没有客户 那么它应该在客户数量为零的结果中,所以它应该显示所有的产品。然后我想将它插入临时表。这是我迄今为止所尝试的,但对于SP中的一些观点,我不知道我必须做些什么来获得我的结果。我想这死循环:( 任何想法?如何使用存储过程将数据存储在临时表上?
中标量:
CREATE FUNCTION NumOfCustomers
(
@year INT,
@productId INT,
@typeOfCustomer NVARCHAR
)
RETURNS TABLE AS
RETURN
(
SELECT COUNT(DISTINCT Sales.Customer.CustomerID) AS number
FROM Sales.SalesOrderDetail SOD
INNER JOIN Sales.SalesOrderHeader SOH ON SOD.SalesOrderID = SOH.SalesOrderID
INNER JOIN Sales.Customer ON Customer.CustomerID = SOH.CustomerID
WHERE
SOD.ProductID = @productID
AND YEAR(SOH.OrderDate) = @year
AND Sales.Customer.CustomerType = @typeOfCustomer
)
GO
存储过程:
CREATE PROCEDURE MYsp AS
BEGIN
DECLARE @year INT, @count INT
DECLARE yearCursor CURSOR FOR
SELECT DISTINCT YEAR(SOH.OrderDate) FROM Sales.SalesOrderHeader SOH
--ORDER BY YEAR DESC
OPEN yearCursor
FETCH NEXT FROM yearCursor INTO @year
--** Starting the loop in curser and filling the data
SET @count = 1
WHILE(@@FETCH_STATUS = 0)
BEGIN
DECLARE @prodID INT
DECLARE @NumOfCustomers INT
DECLARE prodCursor CURSOR FOR
SELECT P.ProductID FROM Production.Product P
OPEN prodCursor
FETCH NEXT FROM prodCursor INTO @prodID
SET @count = 1
WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT @NumOfCustomers = NUM.number FROM NumOfCustomers(@year, @prodID, 's') NUM
INSERT #temp (Year,ProductID,NumOfCustomers) VALUES (@year,@prodID,@NumOfCustomers)
SET @count = @count+1
FETCH NEXT FROM prodCursor INTO @prodID
END
CLOSE prodCursor
DEALLOCATE prodCursor
FETCH NEXT FROM yearCursor INTO @year
END
CLOSE yearCursor
DEALLOCATE yearCursor
END
GO
然后我要执行它,并从数据那个临时表:
EXEC MYsp
SELECT * FROM #temp
答
在程序中使用select语句:
CREATE PROCEDURE MYsp AS
BEGIN
DECLARE @year INT, @count INT
DECLARE yearCursor CURSOR FOR
SELECT DISTINCT YEAR(SOH.OrderDate) FROM Sales.SalesOrderHeader SOH
--ORDER BY YEAR DESC
OPEN yearCursor
FETCH NEXT FROM yearCursor INTO @year
--** Starting the loop in curser and filling the data
SET @count = 1
WHILE(@@FETCH_STATUS = 0)
BEGIN
DECLARE @prodID INT
DECLARE @NumOfCustomers INT
DECLARE prodCursor CURSOR FOR
SELECT P.ProductID FROM Production.Product P
OPEN prodCursor
FETCH NEXT FROM prodCursor INTO @prodID
SET @count = 1
WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT @NumOfCustomers = NUM.number FROM NumOfCustomers(@year, @prodID, 's') NUM
INSERT #temp (Year,ProductID,NumOfCustomers) VALUES (@year,@prodID,@NumOfCustomers)
SET @count = @count+1
FETCH NEXT FROM prodCursor INTO @prodID
END
CLOSE prodCursor
DEALLOCATE prodCursor
FETCH NEXT FROM yearCursor INTO @year
END
CLOSE yearCursor
DEALLOCATE yearCursor
Select *
from #temp
END
GO
现在执行的过程:
EXEC MYsp
阿齐兹它不插入临时表什么...! 错误是: 过程MYsp没有参数并提供参数。 –
这是在我的SQL Server 2012上运行,没有错误。 –