using Set in a cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using Set in a cursor

Author

Topic

Norwich
Posting Yak Master

158 Posts

Posted - 2006-03-17 : 07:15:15

I need to find a way to use Dynamic SQL inside a cursor.

I get an error near set for the following code:




Declare c Cursor for

Select Name
from sysdatabases

Open c

Fetch Next from c into
	@DBName

While @@Fetch_Status = 0
	Begin
		Declare cx Cursor for 

		Set @SQL = 
		'Select top 3
		sl.name, 
		Roles = 
			Case 
				When a.Name is null then ''Public''
				Else a.Name
			End
		from ' + @DBName + '..syslogins sl
		Left Outer Join ' + @DBName + '..sysusers su
			On sl.sid = su.sid
		Left OUter Join ' + @DBName + '..sysmembers sm
			On su.uid = sm.memberuid
		Left Outer Join 
			(
			Select Uid, Name
			from ' + @DBName + '..sysusers
			Where altuid = 1
			Or uid < 3
			) a
		On sm.groupuid = a.Uid
		Where su.uid = ''18'''

		Exec (@SQL)

This is just a snippet of the code.

Any ideas?

The revolution won't be televised!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-17 : 07:27:21

What is the cx cursor for? You've got no result set being referenced by CX cursor...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-17 : 07:34:22

btw - I've reviewed the code.

1 - fix the code - removed the 1st @DBName - syslogins is in master, not in each user database
2 - added [] around the @Dbname references - if you have database (as I do) that start with numerics, you need that.

3 - 2nd option that removes the seemingly unnecessary (?) @SQL usage


declare @Dbname sysname
declare @SQl	varchar(1000)

Declare c Cursor for

Select Name
from sysdatabases

Open c

Fetch Next from c into
	@DBName

While @@Fetch_Status = 0
	Begin
	Set @SQL = 
		'Select top 3
		sl.name, 
		Roles = 
			Case 
				When a.Name is null then ''Public''
				Else a.Name
			End
		from [master]..syslogins sl
		Left Outer Join [' + @DBName + ']..sysusers su
			On sl.sid = su.sid
		Left OUter Join [' + @DBName + ']..sysmembers sm
			On su.uid = sm.memberuid
		Left Outer Join 
			(
			Select Uid, Name
			from [' + @DBName + ']..sysusers
			Where altuid = 1
			Or uid < 3
			) a
		On sm.groupuid = a.Uid
		Where su.uid = ''18'''

		Exec (@SQL)

FETCH NEXT FROM c INTO @DBName

END 
close c
DEALLOCATE c

--btw, what about

Declare c Cursor for

Select Name
from sysdatabases

Open c

Fetch Next from c into
	@DBName

While @@Fetch_Status = 0
	Begin
	exec ('Select top 3
		sl.name, 
		Roles = 
			Case 
				When a.Name is null then ''Public''
				Else a.Name
			End
		from [master]..syslogins sl
		Left Outer Join [' + @DBName + ']..sysusers su
			On sl.sid = su.sid
		Left OUter Join [' + @DBName + ']..sysmembers sm
			On su.uid = sm.memberuid
		Left Outer Join 
			(
			Select Uid, Name
			from [' + @DBName + ']..sysusers
			Where altuid = 1
			Or uid < 3
			) a
		On sm.groupuid = a.Uid
		Where su.uid = ''18''')


FETCH NEXT FROM c INTO @DBName

END 
close c
DEALLOCATE c

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Norwich
Posting Yak Master

158 Posts

Posted - 2006-03-18 : 05:01:10

Hi Wanderer

Well spotted, the syslogins is under the master db.
The query I posted was just a snippet of the code that's why you might not understand why included somethings and it seems I don't use the variables.

The code works fine but it seems I can't Set the @SQL variable inside the cursor and that's what I was querying?

The results of the 'cx' cursor will be used later on in the code.

I get an error when I set the @SQL variable

The revolution won't be televised!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources