calling stored procedure in a cursor

Question

calling stored procedure in a cursor

anjaliv

Say Hey Kid

Points: 689
More actions
July 13, 2011 at 10:19 am

#253587

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!!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

well it depends on your proc whether will work correctly or not, but you can call the proc via a 3 part naming convention, and explicitly naming the database and schema:

SET @sql = 'USE '+@dbName +'; EXEC master.dbo.proc_depend ' + @tableName

here i assumed the master database, but you can change that if it's not right.

does the proc assume compatibility views like sysobjects and systables, or is it using the newer sys.tables and sys.objects(if true, you'd need to make it a system proc.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bagofbirds-767347 Ten Centuries Points: 1110 More actions · Answer 2

first thing i would try is a 'GO' statement after your 'use' statement.

correct sytax would be

use [dbname]

GO

exec [procname]

but i also think lowell's syntax works.

let use know when and how you succeed?

anjaliv Say Hey Kid Points: 689 More actions · Answer 3

Lowell's solution worked. Thank you!

I am creating a table in a stored procedure I have written. But when I call it from the above query, it returns an error saying that table already exists. When we run a stored procedure, does the table gets created? I am confused.

bagofbirds-767347 Ten Centuries Points: 1110 More actions · Answer 4

for help with the table thing, you'll need to include the code that creates the table. you say it is created in a proc. when is the proc run? in which db? is it a temp table?

bopeavy SSCertifiable Points: 5869 More actions · Answer 5

If your going to use this accross the board. Why don't you do away with the cursor and use sp_MSForEachDB.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

anjaliv Say Hey Kid Points: 689 More actions · Answer 6

@bagofbirds-767347: Procedure is run only in the above script. and It is in my current T1DB. It does not matter whether its a temp table or not..it gives the same error even for non temp table.

@bopeavy: am not sure how sp_MSForEachDB will help in my case. But trying on it.

anjaliv Say Hey Kid Points: 689 More actions · Answer 7

@bagofbirds-767347: Procedure is run only in the above script. and It is in my current T1DB. It does not matter whether its a temp table or not..it gives the same error even for non temp table.

@bopeavy: am not sure how sp_MSForEachDB will help in my case. But trying on it.

bopeavy SSCertifiable Points: 5869 More actions · Answer 8

Even if your doing this only with in a database you could use sp_msforeachtable to execute across all tables. Only recommended this because you were looking for dependents which soulds as if it may be related to doing maintenance.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Lowell SSC Guru Points: 323504 More actions · Answer 9

anjaliv (7/13/2011)
Lowell's solution worked. Thank you!
I am creating a table in a stored procedure I have written. But when I call it from the above query, it returns an error saying that table already exists. When we run a stored procedure, does the table gets created? I am confused.

we'd have to see the procedure, i think...if it is calling itself recursively, then any temp table created at a higher level exists and is available for procs that are called inside it...that might be the issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

anjaliv Say Hey Kid Points: 689 More actions · Answer 10

Procedure that is called inside the cursor is as following:

CREATE PROCEDURE [dbo].[sp_dependency3]

-- Add the parameters for the stored procedure here

@tableName nvarchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

CREATE TABLE [dbo].[Table_Dependency1](

[db_name] [nvarchar](50),

[table_name] [nvarchar](50),

[obj_name] [nvarchar](50),

[obj_type] [varchar](50),

)

insert into [dbo].[Table_Dependency1] (obj_name, obj_type)

SELECT DISTINCT o.name, o.xtype

FROM syscomments c

INNER JOIN sysobjects o ON c.id=o.id

WHERE c.TEXT LIKE CHAR(39)+'%'+@tableName+'%'+CHAR(39)

UPDATE [dbo].[Table_Dependency3] SET db_name = DB_NAME ,table_name = @tableName WHERE db_name IS NULL

END

Lowell SSC Guru Points: 323504 More actions · Answer 11

lots of stuff doesn't look right here.

if you EVER call that proc more than once, it will fail because it creates a permenant table, but never drops it.

depending on how the proc gets called, you don't know which database the table would get created in.

you need to fix that as well.

the calling user might not have create table permissions, so it could fail due to that as well.

CREATE PROCEDURE [dbo].[sp_dependency3]

-- Add the parameters for the stored procedure here

@tableName NVARCHAR(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

IF NOT EXISTS(SELECT name FROM T1DB.dbo.sysobjects WHERE name = 'Table_Dependency1')

BEGIN

DECLARE @sql VARCHAR(MAX)

SET @sql = 'CREATE TABLE T1DB.[dbo].[Table_Dependency1] (

[db_name] nvarchar(100) NULL,

[table_name] nvarchar(100) NULL,

[obj_name] nvarchar(100) NULL,

[obj_type] varchar(50) NULL)'

PRINT @sql

EXEC(@sql)

END --if exists

--what if there was previous data? do you want to delete it?

INSERT INTO [T1DB].[dbo].[Table_Dependency1] (obj_name, obj_type)

SELECT DISTINCT o.name, o.xtype

FROM syscomments c

INNER JOIN sysobjects o ON c.id=o.id

WHERE c.TEXT LIKE CHAR(39)+'%'+@tableName+'%'+CHAR(39)

UPDATE [T1DB].[dbo].[Table_Dependency1] SET DB_NAME = DB_NAME() ,table_name = @tableName WHERE DB_NAME IS NULL

END --PROC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

anjaliv Say Hey Kid Points: 689 More actions · Answer 12

Thank you so much for the help Lowell 🙂

I am using that stored procedure in a cursor and the code is as following:

It gives an error saying " Could not find stored procedure"

DECLARE name_cur CURSOR FOR SELECT db_name, obj_name from table1

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

print @dbName

SET @sql = 'USE '+@dbName +' EXEC sp_dependency3 ' + @tableName

EXEC (@sql)

FETCH name_cur INTO @dbName, @tableName

END

CLOSE name_cur

DEALLOCATE name_cur

GO

I am asking too much...But i really need this to work.

Thank you again for the help!!

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 13

Try three-part naming to reference your stored proc instead of USE. You may also have had an issue because you were not surrounding @tableName with single quotes in your proc call.

DECLARE name_cur CURSOR

FOR

SELECT db_name,

obj_name

FROM table1

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

PRINT @dbName

SET @sql = 'EXEC ' + QUOTENAME(@dbName) + '.dbo.sp_dependency3 N''' + @tableName + ''''

EXEC (@sql)

FETCH name_cur INTO @dbName, @tableName

END

CLOSE name_cur

DEALLOCATE name_cur

GO

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 14

bagofbirds-767347 (7/13/2011)
first thing i would try is a 'GO' statement after your 'use' statement.
correct sytax would be
use [dbname]
GO
exec [procname]
but i also think lowell's syntax works.
let use know when and how you succeed?

As an aside "GO" is a client-specific batch separator. It is not actually a T-SQL command. You can change it to suit in SSMS if you like.

To demonstrate, this will not work:

EXEC ('

USE master ;

GO

SELECT *

FROM sys.tables ;

GO

');

Where this will:

EXEC ('

USE master ;

SELECT *

FROM sys.tables ;

');

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato