SQL Server - 使用WHILE循环问题执行语句

I'm trying to write a script that can be used to restore permissions to stored procedures after our merge replication snapshot agent completes applying snapshot to subscribers. This SQL needs to be somewhat dynamic.

我正在尝试编写一个脚本，在我们的合并复制快照代理完成将快照应用于订阅者之后，该脚本可用于恢复存储过程的权限。这个SQL需要有些动态。

Currently, I'm selecting a list of all our stored procedures and inserting them into a temporary table along with string statements for "Granting" permissions. I'm attempting to loop through all the rows on that table, executing the statements one by one using EXEC() command. I keep getting the error

目前，我正在选择所有存储过程的列表，并将它们与“授予”权限的字符串语句一起插入临时表中。我试图遍历该表上的所有行，使用EXEC（）命令逐个执行语句。我一直在收到错误

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

当未使用EXISTS引入子查询时，只能在选择列表中指定一个表达式

but my SQL statements look like they should be fine. Maybe I'm not understanding how WHILE works in SQL Server.

但我的SQL语句看起来应该没问题。也许我不明白WHILE在SQL Server中是如何工作的。

Here is my code:

这是我的代码：

BEGIN
CREATE TABLE sqltemp (id int IDENTITY(1, 1) , Stmt1 varchar(max), Stmt2 varchar(max),  Stmt3 varchar(max))

INSERT INTO sqltemp 
   SELECT 
      'GRANT EXECUTE ON OBJECT::' as Stmt1, name as Stmt2, 'TO edoc_only_execute' as Stmt3
   FROM sys.sysobjects
   WHERE 
      type = 'P' AND name NOT LIKE 'MSMerge%'

DECLARE @counter int = 1
WHILE (@counter < (SELECT COUNT(*) FROM sqltemp))
BEGIN
    DECLARE @sqlrun varchar(max)
    SET @sqlrun = (SELECT Stmt1, Stmt2, Stmt3 FROM sqltemp WHERE id = @counter)
    EXEC(@sqlrun)
    SET @counter = @counter + 1
END
END
GO
DROP TABLE sqltemp

Two questions:

两个问题：

How can I accomplish executing the above script for each item in my temporary table?

如何为临时表中的每个项目完成上述脚本的执行？
Is there a better way of writing a script to restore permissions for each stored procedure in my database after the snapshot is applied (Note: I must be able use SQL system tables to pull stored procedure names)?

在应用快照后，是否有更好的方法来编写脚本来恢复数据库中每个存储过程的权限（注意：我必须能够使用SQL系统表来提取存储过程名称）？

3 个解决方案

#1

You can't say

你不能说

SET @sqlrun = (SELECT Stmt1, Stmt2, Stmt3 FROM sqltemp WHERE id = @counter)

You will have to concatenate them

你必须连接它们

SELECT @sqlrun = Stmt1 +' '+ Stmt2 +' '+ Stmt3 FROM sqltemp WHERE id = @counter

A better solution might be?

更好的解决方案可能是？

GRANT EXEC TO edoc_only_execute

#2

Corrected query for you first question

更正了第一个问题的查询

    BEGIN
    CREATE TABLE sqltemp (id int IDENTITY(1, 1) , Stmt1 varchar(max), Stmt2 varchar(max),  Stmt3 varchar(max))

    INSERT INTO sqltemp SELECT 'GRANT EXECUTE ON OBJECT::' as Stmt1, name as Stmt2, 'TO edoc_only_execute' as Stmt3
                        FROM sys.sysobjects
                        WHERE type = 'P' AND name NOT LIKE 'MSMerge%'

    DECLARE @counter int = 1
    WHILE (@counter < (SELECT COUNT(*) FROM sqltemp))
    BEGIN
        DECLARE @sqlrun varchar(max)
        SELECT @sqlrun = Stmt1 + Stmt2 +' '+ Stmt3 FROM sqltemp WHERE id = @counter
        PRINT @sqlrun
        EXEC(@sqlrun)
        SET @counter = @counter + 1
    END
    END

#3

@010001100110000101110010011010 and @podiluska beat me to it, but...

@ 010001100110000101110010011010和@podiluska打败了我，但......

SELECT COUNT(*) FROM sqltemp

outside of the while:

在外面：

SET @end = SELECT COUNT(*) FROM sqltemp
WHILE (@counter < @end)
    ...

No need to re-calculate the end condition for each loop iteration.

无需为每个循环迭代重新计算结束条件。

#1