calling stored procedure in a cursor

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 2005 Forums

Transact-SQL (2005)

calling stored procedure in a cursor

Author

Topic

anjaliv
Starting Member

10 Posts

Posted - 2011-07-13 : 12:17:30

Hi All,

I have created a stored procedure which finds dependent objects on a particular table.
I want to run this stored procedure for different database and for different tables.
I have created cursor for this.
But when I write USE @dbname, it tries to find the Stored Procedure in a @dbname and not the current database.
Can anybody please help me with how do I write this command in a cursor?

DECLARE name_cur CURSOR FOR SELECT db_name, obj_name from Stats_Usage WHERE last_user_update > '2011-06-01' ORDER BY db_name
DECLARE @tableName NVARCHAR (800)
DECLARE @dbName NVARCHAR(800)
DECLARE @sql NVARCHAR(900)
OPEN name_cur
FETCH name_cur INTO @dbName, @tableName
WHILE @@Fetch_Status = 0
BEGIN

SET @sql = 'USE '+@dbName +' EXEC proc_depend ' + @tableName
EXEC (@sql)
FETCH name_cur INTO @dbName, @tableName
END
CLOSE name_cur
DEALLOCATE name_cur
GO

Thank you!!

aureolin
Starting Member

3 Posts

Posted - 2011-07-16 : 22:34:20

You need to fully qualify the proc name with the dbname and schema where it can be found.

________________________________________
"It's more complicated than that"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources