nested curosr help - 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

General SQL Server Forums

New to SQL Server Programming

nested curosr help

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-04 : 22:43:44

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROC [dbo].[grants] as

declare curse1 cursor for
SELECT name
FROM master.dbo.sysdatabases
WHERE dbid > 4

OPEN CURSE1
declare @tname varchar(100)

fetch next from curse1 into @tname
while @@fetch_status=0
begin
declare curse cursor for
select name
from tname.sysobjects
where type='P' AND category='0'

OPEN CURSE

declare @proc varchar(100)
declare @stmt nvarchar(200)

fetch next from curse into @proc
while @@fetch_status=0
begin
set @stmt='deny execute on '+@proc+' to mina_test'
exec SP_EXECUTESQL @STMT
print @stmt
fetch next from curse into @proc
end

close curse
deallocate curse

fetch next from curse1 into @tname
end

close curse1
deallocate curse1

..............

Im trying to get the value tname from one cursor and use it as part of an sql query in another cursor any advise?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-04 : 23:18:30

Run this

Select 'deny execute on '+name+' to mina_test' from sysobjects
where xtype='p' and category='0'

Copy the result back to QA and run them

Madhivanan

Failing to plan is Planning to fail

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-04 : 23:25:07

yeah i want to automate that so that i can use that script for several instances...

in other words i want to say something like

select *
from @tname..sysobjects <<<< INCLUDE VARIABLE BEFORE SYS OBJECTS
...

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-04 : 23:33:18

in other words can a cursor variable be used in annother cursors select statement but instead of being used in the WHERE section i'd use it in the FROM section

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-05 : 01:35:59

the specific thing you are attempting to do is not possible without some very messy dynamic sql.

Here's a much simpler way, basically what Madhivanan was suggesting, minus the copy/paste:


declare @sql varchar(8000)
select @sql = coalesce(@sql, '') + 'deny execute on ' + quotename(name) + ' to mina_test;
'
from sysobjects where xtype='P' 
--exec(@sql)
print @sql

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-05 : 01:57:16

yeah but that just does it for the 1 database im in not all other databases two...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-05 : 06:07:24

quote:
Originally posted by rnbguy

yeah but that just does it for the 1 database im in not all other databases two...

http://sqlteam.com/forums/topic.asp?TOPIC_ID=75666

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources