一個人如何在不使用游標的情況下為表中的每一行調用存儲過程，其中行的列是sp的輸入參數？
不使用游標的每行的SQL調用存儲過程
子衿沉夜
2019-11-25 14:43:31
3 回答
素胚勾勒不出你
TA貢獻1827條經驗 獲得超9個贊
一般來說，我總是在尋找一種基于集合的方法（有時以更改架構為代價）。
但是，此片段確實有它的位置。
-- Declare & init (2008 syntax)
DECLARE @CustomerID INT = 0
-- Iterate over all customers
WHILE (1 = 1)
BEGIN
-- Get next customerId
SELECT TOP 1 @CustomerID = CustomerID
FROM Sales.Customer
WHERE CustomerID > @CustomerId
ORDER BY CustomerID
-- Exit loop if no more customers
IF @@ROWCOUNT = 0 BREAK;
-- call your sproc
EXEC dbo.YOURSPROC @CustomerId
END
慕娘9325324
TA貢獻1783條經驗 獲得超4個贊
您可以執(zhí)行以下操作：通過例如CustomerID（使用AdventureWorks Sales.Customer示例表）訂購表，并使用WHILE循環(huán)遍歷那些客戶：
-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0
-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT
-- select the next customer to handle
SELECT TOP 1 @CustomerIDToHandle = CustomerID
FROM Sales.Customer
WHERE CustomerID > @LastCustomerID
ORDER BY CustomerID
-- as long as we have customers......
WHILE @CustomerIDToHandle IS NOT NULL
BEGIN
-- call your sproc
-- set the last customer handled to the one we just handled
SET @LastCustomerID = @CustomerIDToHandle
SET @CustomerIDToHandle = NULL
-- select the next customer to handle
SELECT TOP 1 @CustomerIDToHandle = CustomerID
FROM Sales.Customer
WHERE CustomerID > @LastCustomerID
ORDER BY CustomerID
END
只要您可以ORDER BY在某些列上定義某種形式的表，那么該表就適用于任何表。
繁花不似錦
TA貢獻1851條經驗 獲得超4個贊
Marc的回答很好（如果可以解決的話，我會對此發(fā)表評論！）
只是以為我指出，更改循環(huán)可能會更好，因此SELECT僅存在一次（在實際情況下，我需要這樣做SELECT非常復雜，將其寫入兩次是有風險的維護問題）。
-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0
-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT
SET @CustomerIDToHandle = 1
-- as long as we have customers......
WHILE @LastCustomerID <> @CustomerIDToHandle
BEGIN
SET @LastCustomerId = @CustomerIDToHandle
-- select the next customer to handle
SELECT TOP 1 @CustomerIDToHandle = CustomerID
FROM Sales.Customer
WHERE CustomerID > @LastCustomerId
ORDER BY CustomerID
IF @CustomerIDToHandle <> @LastCustomerID
BEGIN
-- call your sproc
END
- 3 回答
- 0 關注
- 791 瀏覽
添加回答
舉報
0/150
提交
取消