不使用游标的每行 SQL 调用存储过程

如何使用游标为表中的每一行调用存储过程，其中行的列是 sp 没有的输入参数？

259455 次浏览
小开

如果您不使用游标，我认为您必须在外部执行(获取表，然后运行每个语句，每次调用 sp) 它与使用游标相同，但仅限于 SQL 之外。 你为什么不用光标？

小开

对于 SQLServer2005以后的版本，您可以使用 交叉适用和表值函数来完成此操作。

为了清楚起见，我指的是存储过程可以转换为表值函数的情况。

小开

您可以这样做: 按照 CustomerID (使用 AdventureWorks Sales.Customer示例表)对表进行排序，然后使用 WHILE 循环对这些客户进行迭代:

-- 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，这应该可以用于任何表。

小开

如果可以将存储过程转换为返回表的函数，则可以使用交叉应用。

例如，假设您有一个客户表，并且您想要计算他们的订单总额，您将创建一个接受 CustomerID 并返回总额的函数。

你可以这样做:

SELECT CustomerID, CustomerSum.Total


FROM Customers
CROSS APPLY ufn_ComputeCustomerTotal(Customers.CustomerID) AS CustomerSum

函数应该是这样的:

CREATE FUNCTION ComputeCustomerTotal
(
@CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
SELECT SUM(CustomerOrder.Amount) AS Total FROM CustomerOrder WHERE CustomerID = @CustomerID
)

显然，上面的示例可以在单个查询中不使用用户定义的函数来完成。

缺点是函数非常有限——存储过程的许多特性在用户定义的函数中不可用，并且将存储过程转换为函数并不总是有效。

小开

马克的回答很好(如果我知道怎么做的话，我会加以评论的!)
我只是想指出，更改循环可能更好，这样 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


END
小开

我通常这样做，当它是一个相当多的行:

  1. 使用 SQLManagementStudio 选择数据集中的所有 spproc 参数
  2. 右击-> 复制
  3. 粘贴到出类拔萃
  4. 在一个新的 excel 列中创建单行 sql 语句，其公式为“ = “ EXEC schema.mysproc@param =”& A2”。(其中 A2是包含参数的 excel 列)
  5. 将 excel 语句列表复制到 SQLManagementStudio 中的新查询中并执行。
  6. 成交。

(对于较大的数据集，我会使用上面提到的解决方案之一)。

小开
最佳答案

一般来说，我总是寻找一种基于集合的方法(有时会以改变模式为代价)。

然而，这个片段确实有它的位置. 。

-- 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
小开

DELIMITER//

CREATE PROCEDURE setFakeUsers (OUT output VARCHAR(100))
BEGIN


-- define the last customer ID handled
DECLARE LastGameID INT;
DECLARE CurrentGameID INT;
DECLARE userID INT;


SET @LastGameID = 0;


-- define the customer ID to be handled now


SET @userID = 0;


-- select the next game to handle
SELECT @CurrentGameID = id
FROM online_games
WHERE id > LastGameID
ORDER BY id LIMIT 0,1;


-- as long as we have customers......
WHILE (@CurrentGameID IS NOT NULL)
DO
-- call your sproc


-- set the last customer handled to the one we just handled
SET @LastGameID = @CurrentGameID;
SET @CurrentGameID = NULL;


-- select the random bot
SELECT @userID = userID
FROM users
WHERE FIND_IN_SET('bot',baseInfo)
ORDER BY RAND() LIMIT 0,1;


-- update the game
UPDATE online_games SET userID = @userID WHERE id = @CurrentGameID;


-- select the next game to handle
SELECT @CurrentGameID = id
FROM online_games
WHERE id > LastGameID
ORDER BY id LIMIT 0,1;
END WHILE;
SET output = "done";
END;//


CALL setFakeUsers(@status);
SELECT @status;
小开

这是上面 n3rds 解决方案的一个变体。不需要使用 ORDERBY 进行排序，因为使用的是 MIN ()。

请记住 CustomerID (或用于进度的任何其他数字列)必须具有唯一约束。此外，为了使它尽可能快，CustomerID 必须建立索引。

-- Declare & init
DECLARE @CustomerID INT = (SELECT MIN(CustomerID) FROM Sales.Customer); -- First ID
DECLARE @Data1 VARCHAR(200);
DECLARE @Data2 VARCHAR(200);


-- Iterate over all customers
WHILE @CustomerID IS NOT NULL
BEGIN


-- Get data based on ID
SELECT @Data1 = Data1, @Data2 = Data2
FROM Sales.Customer
WHERE [ID] = @CustomerID ;


-- call your sproc
EXEC dbo.YOURSPROC @Data1, @Data2


-- Get next customerId
SELECT @CustomerID = MIN(CustomerID)
FROM Sales.Customer
WHERE CustomerID > @CustomerId


END

我对需要查看的一些 varchars 使用这种方法，首先将它们放在一个临时表中，以便为它们提供一个 ID。

小开 
DECLARE @SQL varchar(max)=''


-- MyTable has fields fld1 & fld2


Select @SQL = @SQL + 'exec myproc ' + convert(varchar(10),fld1) + ','
+ convert(varchar(10),fld2) + ';'
From MyTable


EXEC (@SQL)

好吧，所以我永远不会把这样的代码投入生产，但它确实满足您的要求。

小开

我喜欢做一些类似的事情(尽管它仍然非常类似于使用光标)

[代码]

-- Table variable to hold list of things that need looping
DECLARE @holdStuff TABLE (
id INT IDENTITY(1,1) ,
isIterated BIT DEFAULT 0 ,
someInt INT ,
someBool BIT ,
otherStuff VARCHAR(200)
)


-- Populate your @holdStuff with... stuff
INSERT INTO @holdStuff (
someInt ,
someBool ,
otherStuff
)
SELECT
1 , -- someInt - int
1 , -- someBool - bit
'I like turtles'  -- otherStuff - varchar(200)
UNION ALL
SELECT
42 , -- someInt - int
0 , -- someBool - bit
'something profound'  -- otherStuff - varchar(200)


-- Loop tracking variables
DECLARE @tableCount INT
SET     @tableCount = (SELECT COUNT(1) FROM [@holdStuff])


DECLARE @loopCount INT
SET     @loopCount = 1


-- While loop variables
DECLARE @id INT
DECLARE @someInt INT
DECLARE @someBool BIT
DECLARE @otherStuff VARCHAR(200)


-- Loop through item in @holdStuff
WHILE (@loopCount <= @tableCount)
BEGIN


-- Increment the loopCount variable
SET @loopCount = @loopCount + 1


-- Grab the top unprocessed record
SELECT  TOP 1
@id = id ,
@someInt = someInt ,
@someBool = someBool ,
@otherStuff = otherStuff
FROM    @holdStuff
WHERE   isIterated = 0


-- Update the grabbed record to be iterated
UPDATE  @holdAccounts
SET     isIterated = 1
WHERE   id = @id


-- Execute your stored procedure
EXEC someRandomSp @someInt, @someBool, @otherStuff


END

[/代码]

请注意，您不需要 需要标识或临时/变量表上的 isIterated 列，我只是更喜欢这样做，这样我就不必在迭代循环时从集合中删除 top 记录。

小开

我会使用接受的答案，但是另一种可能性是使用一个表变量来保存一组编号的值(在这种情况下只是表的 ID 字段) ，并通过行号对这些值进行循环，并使用一个 JOIN 到达表，以检索循环中操作所需的任何内容。

DECLARE @RowCnt int; SET @RowCnt = 0 -- Loop Counter


-- Use a table variable to hold numbered rows containg MyTable's ID values
DECLARE @tblLoop TABLE (RowNum int IDENTITY (1, 1) Primary key NOT NULL,
ID INT )
INSERT INTO @tblLoop (ID)  SELECT ID FROM MyTable


-- Vars to use within the loop
DECLARE @Code NVarChar(10); DECLARE @Name NVarChar(100);


WHILE @RowCnt < (SELECT COUNT(RowNum) FROM @tblLoop)
BEGIN
SET @RowCnt = @RowCnt + 1
-- Do what you want here with the data stored in tblLoop for the given RowNum
SELECT @Code=Code, @Name=LongName
FROM MyTable INNER JOIN @tblLoop tL on MyTable.ID=tL.ID
WHERE tl.RowNum=@RowCnt
PRINT Convert(NVarChar(10),@RowCnt) +' '+ @Code +' '+ @Name
END
小开

这是已经提供的答案的一个变体，但是应该表现得更好，因为它不需要 ORDERBY、 COUNT 或 MIN/MAX。这种方法的唯一缺点是必须创建一个临时表来保存所有 ID (假设 CustomerID 列表中存在空白)。

尽管如此，我同意@Mark Powell 的观点，但是一般来说，基于集合的方法应该更好。

DECLARE @tmp table (Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, CustomerID INT NOT NULL)
DECLARE @CustomerId INT
DECLARE @Id INT = 0


INSERT INTO @tmp SELECT CustomerId FROM Sales.Customer


WHILE (1=1)
BEGIN
SELECT @CustomerId = CustomerId, @Id = Id
FROM @tmp
WHERE Id = @Id + 1


IF @@rowcount = 0 BREAK;


-- call your sproc
EXEC dbo.YOURSPROC @CustomerId;
END
小开

如果顺序很重要的话

--declare counter
DECLARE     @CurrentRowNum BIGINT = 0;
--Iterate over all rows in [DataTable]
WHILE (1 = 1)
BEGIN
--Get next row by number of row
SELECT TOP 1 @CurrentRowNum = extendedData.RowNum
--here also you can store another values
--for following usage
--@MyVariable = extendedData.Value
FROM    (
SELECT
data.*
,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNum
FROM [DataTable] data
) extendedData
WHERE extendedData.RowNum > @CurrentRowNum
ORDER BY extendedData.RowNum


--Exit loop if no more rows
IF @@ROWCOUNT = 0 BREAK;


--call your sproc
--EXEC dbo.YOURSPROC @MyVariable
END
小开

解决这个问题的更好办法是

  1. 存储过程的复制/过去代码
  2. 将该代码与要为其再次运行该代码的表(对于每一行)联接起来

这是一个干净的表格格式的输出。如果对每一行运行 SP，则每次迭代都会得到一个单独的查询结果，这很难看。

小开

我有一些一次只能处理20名员工的生产代码，下面是代码的框架。我只是复制了生产代码，删除了下面的内容。

ALTER procedure GetEmployees
@ClientId varchar(50)
as
begin
declare @EEList table (employeeId varchar(50));
declare @EE20 table (employeeId varchar(50));


insert into @EEList select employeeId from Employee where (ClientId = @ClientId);


-- Do 20 at a time
while (select count(*) from @EEList) > 0
BEGIN
insert into @EE20 select top 20 employeeId from @EEList;


-- Call sp here


delete @EEList where employeeId in (select employeeId from @EE20)
delete @EE20;
END;


RETURN
end
小开

我遇到过这样一种情况: 我需要对一个结果集(表)执行一系列操作。所有的操作都是设置好的，所以这不是问题，但是..。 我需要在很多地方做这件事。因此，将相关的部分放在一个表类型中，然后填充一个表变量 w/each result set，这样我就可以调用 sp 并在每次需要时重复操作。

虽然这并没有解决他提出的确切问题，但它确实解决了如何在不使用游标的情况下对表的所有行执行操作。

@ Johannes 没有透露他的动机，所以这可能帮不了他。

我的研究引导我找到了这篇写得很好的文章，作为我的解决方案的基础 Https://codingsight.com/passing-data-table-as-parameter-to-stored-procedures/

这是设置

    drop type if exists cpRootMapType
go


create type cpRootMapType as Table(
RootId1 int
, RootId2 int
)


go
drop procedure if exists spMapRoot2toRoot1
go
create procedure spMapRoot2toRoot1
(
@map cpRootMapType Readonly
)
as


update linkTable set root = root1
from linktable  lt
join @map m on lt.root = root2


update comments set root = root1
from comments c
join @map m on c.root = root2


--  ever growing list of places this map would need to be applied....
--  now consolidated into one place

这是实现

... populate #matches


declare @map cpRootMapType
insert @map select rootid1, rootid2 from #matches
exec spMapRoot2toRoot1 @map

提交答案