Cursor question

Last Post 12 Mar 2010 08:10 AM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
PaulMcKibben
New Member
New Member

--
12 Mar 2010 06:08 AM
I have a problem with the following cursor definition that I would appreciate some help with. The first query returns a listing of tables. The second query needs to use the returned table name for its USE statement.


declare tables cursor
for select name
from master.sys.databases
where state = 0

declare @name sysname

open tables
fetch tables into @name
while (@@fetch_status = 0)
begin
-- use [@name]
select @name as [Database], name, algorithm_desc from sys.symmetric_keys
where key_algorithm NOT in ('D3', 'A1', 'A2', 'A3')
order by name, algorithm_desc
-- get next record
fetch tables into @name
end
close tables
deallocate tables

Any help is appreciated.
gunneyk
New Member
New Member

--
12 Mar 2010 06:30 AM
If you want to "USE" that database you will have to wrap it in dynamic sql inside the cursor loop. So the dnyamic sql will need to include the USE and the select.
PaulMcKibben
New Member
New Member

--
12 Mar 2010 06:48 AM
I haven't done a lot with Cursors. What if I would append @name to the first of the table name? I have looked through several books and BOL and don't find a good example.

gunneyk
New Member
New Member

--
12 Mar 2010 08:10 AM
Try this:

declare tables cursor
for select name
from master.sys.databases
where state = 0

declare @name sysname, @SQL NVARCHAR(1000)

open tables
fetch tables into @name
while (@@fetch_status = 0)
begin

SET @SQL = 'USE [' + @name + ']' ;
SET @SQL = 'select ''' + @name + ''' as [Database], name, algorithm_desc from sys.symmetric_keys
where key_algorithm NOT in (''D3'', ''A1'', ''A2'', ''A3'')
order by name, algorithm_desc ;' ;

EXEC(@SQL) ;

-- get next record
fetch tables into @name
end
close tables
deallocate tables
You are not authorized to post a reply.

Acceptable Use Policy