How to do an Alter in SQL Cursor

Question

I have 100's of databases for which I need to do an Alter to a procedure, they all have the same procedure. How can I add a cursor that will allow me to do this alter?.

DECLARE @databasename varchar(100)
DECLARE @Command nvarchar(200)

DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @databasename

WHILE @@FETCH_STATUS = 0
BEGIN
     SELECT @Command = 'ALTER PROCEDURE ''' + @databasename + '''.[dbo].[DeleteAccountUpgrade]
               @Id INT
            AS
                DELETE FROM AccountUpgrades WHERE Id = @Id'
     EXEC sp_executesql @Command

     FETCH NEXT FROM database_cursor INTO @databasename
END

CLOSE database_cursor
DEALLOCATE database_cursor

It says that i need to declare @Id but i dont understand why, I just want to run that alter on each database, i am not sending any data to execute the procedure, i just want to modify the existing procedure on all databases.

This is really a deployment issue; I wouldn't try to do this from pure TSQL. Have you considered using [SQLCMD variables](http://msdn.microsoft.com/en-us/library/ms188714.aspx), or just writing a small deployment script that can execute your new DDL script against any server/database that you need? It's very difficult to manage source code if you deploy it in the way that you're trying to do it. — Pondlife, May 06 '13 at 15:45
CREATE/ALTER PROCEDURE does not allow specifying the database name as a prefix to the object name. — Aleksandr Fedorenko, May 06 '13 at 16:56

score 1 · Answer 1 · edited May 23 '17 at 10:25

1

You need to use EXEC by itself, and not with sp_executesql.

EXEC sp_executesql @Command

should be changed to:

EXEC(@Command)

The sp_executesql procedure does parameterization with variables. This is where the error is coming from. See also https://stackoverflow.com/a/16308447/1822514

edited May 23 '17 at 10:25

Community

1
1

answered May 06 '13 at 15:58

chue x

18,573
7
56
70

Aleksandr Fedorenko · Accepted Answer · 2013-05-06T21:01:30.970

Use nest sp_executesql calls. This allow you to execute DDL against other databases.

DECLARE @databasename varchar(100)
DECLARE @Command nvarchar(200)

DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @databasename

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @Command = N'USE ' + QUOTENAME(@databasename) +
                     ' EXEC sp_executesql N''ALTER PROCEDURE [dbo].[DeleteAccountUpgrade] ' +
                     '@Id INT ' +
                     'AS ' +
                     'DELETE FROM AccountUpgrades WHERE Id = @Id'''
  --PRINT @Command
  EXEC sp_executesql @Command

  FETCH NEXT FROM database_cursor INTO @databasename
END

CLOSE database_cursor
DEALLOCATE database_cursor

How to do an Alter in SQL Cursor

2 Answers2