Unfortunately you cannot make cursors dynamic enough to ingest, a statement run by executing a command string. For example let's say you want to populate a cursor based on a statement that takes an input database name such as below.
Declare @DBName varchar(100)
Declare @Stmt varchar(500)
Set @DBName =
'master'
Set @Stmt =
'select name from '
+ @DBName +
'.sys.database_principals'
Declare test_cursor cursor
for
Exec (@Stmt)
This will fail. The way to get around this is too populate a variable or temp table then select from that.
Declare @DBName varchar(100)
Declare @Stmt varchar(500)
Declare @HoldTable table(UserName varchar(100))
Set @DBName =
'master'
Set @Stmt =
'select name from '
+ @DBName +
'.sys.database_principals'
Insert
into @HoldTable
Exec (@Stmt)
Declare test_cursor cursor
for
Select Username from @holdTable
1 comment:
Hi. What about making the entire DECLARE statement part of the dynamic string? This is an example of what I do (it does work) :)
DECLARE @Cmd nvarchar(2000);
SET @Cmd = N'DECLARE dbname_cur CURSOR FAST_FORWARD FOR SELECT RTRIM([name]) FROM [master].[dbo].[sysdatabases] WITH (NOLOCK) WHERE [dbid] > 4 AND [name] = ''' + @DBName + '''; '
EXEC master.dbo.sp_executesql @Cmd;
OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @DBName
...etc...
Post a Comment