title: SQL Server - Execute statements using WHILE Loop Issues
body: <p>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.</p>
  
  <p>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 </p>
  
  <blockquote>
    <p><em>Only one expression can be specified in the select list when the subquery is not introduced with EXISTS</em> </p>
  </blockquote>
  
  <p>but my SQL statements look like they should be fine. Maybe I'm not understanding how WHILE works in SQL Server.</p>
  
  <p>Here is my code: </p>
  
  <pre><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 &lt; (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
  </code></pre>
  
  <p>Two questions: </p>
  
  <ol>
  <li><p>How can I accomplish executing the above script for each item in my temporary table?</p></li>
  <li><p>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)?</p></li>
  </ol>