calling stored procedure in a cursor

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

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

  • 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?

  • 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.

  • 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?

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

  • @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.

  • @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.

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

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

  • 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

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

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

  • 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

  • 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

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

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