SQL Server - Execute statements using WHILE Loop Issues

Question

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.

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

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

but my SQL statements look like they should be fine. Maybe I'm not understanding how WHILE works in 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)?

podiluska · Accepted Answer · 2012-10-26 15:30:35Z

3

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

answered Oct 26, 2012 at 15:30

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Encryption Over a year ago

Thanks...curious tho. Further explanation as to why my SET @sqlrun = (SELECT ....) was not acceptable to sql server? Just want to understand so I dont make same mistake again, and follow best practice.

MatBailie Over a year ago

@Encryption - variables in SQL Server are Scalars, they can only contain a single value of a single datatype. The alternative is to use a table variable, but that doesn't get you anywhere in this case. So, you either need to push the three values into three different variables, or concatenate them so they can go into one single variable.

Encryption Over a year ago

So using SELECT @@sql was providing a one string where SET @SQL was not providing a string type?

Farfarak · Accepted Answer · 2012-10-26 15:29:28Z

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

answered Oct 26, 2012 at 15:29

Farfarak

1,5471 gold badge8 silver badges8 bronze badges

Comments

Mike P · Accepted Answer · 2012-10-26 15:35:43Z

0

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

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.

answered Oct 26, 2012 at 15:35

Mike P

75211 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL Server - Execute statements using WHILE Loop Issues

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related