Declaring a cursor with table name as parameter

  • Hi SQL Gurus

    When you execute the following code it works perfectly:

    DECLARE @CursorVar CURSOR

    SET @CursorVar = CURSOR SCROLL DYNAMIC

    FOR

    SELECT *

    FROM Northwind.dbo.Employees

    --WHERE LastName like 'B%'

    OPEN @CursorVar

    FETCH NEXT FROM @CursorVar

    WHILE @@FETCH_STATUS = 0

    BEGIN

        FETCH NEXT FROM @CursorVar

    END

    CLOSE @CursorVar

    DEALLOCATE @CursorVar

    BUT when I try to open a cursor by supplying table name as a parameter, which I need to do, it fails:

    declare @sql varchar(200)

    declare @tablename varchar(200)

    set @tablename = 'sysobjects'

    SET @sql = 'declare @CursorVar cursor ' --OR declare @CursorVar cursor  --without using SET @sql

    SET @sql = @sql + 'SET @CursorVar = CURSOR FOR SELECT * from ' + @tablename 

    PRINT @sql

    exec (@SQL)

    OPEN @CursorVar

    FETCH NEXT FROM @CursorVar

    WHILE @@FETCH_STATUS = 0

    BEGIN

        FETCH NEXT FROM @CursorVar

    END

    CLOSE @CursorVar

    DEALLOCATE @CursorVar

    Is there a way to achieve that - open a cursor with table name as a parameter?

    Thanks

    Gary

     

     

  • Can you post what are you trying to achieve?

    What is the purpose of this ?

     


    * Noel

  • (1) Do not use a Cursor Variable

    (2) Declare the cursor Global

    This allows what you need to do. So, this code should do it:

    declare @sql varchar(200)

    declare @tablename varchar(200)

    set @tablename = 'sysobjects'

    SET @sql = 'Declare c_CursorVar CURSOR GLOBAL FOR SELECT * from ' + @tablename

    PRINT @sql

    exec (@SQL)

    OPEN c_CursorVar

    FETCH NEXT FROM c_CursorVar

    WHILE @@FETCH_STATUS = 0

    BEGIN

    FETCH NEXT FROM c_CursorVar

    END

    CLOSE c_CursorVar

    DEALLOCATE c_CursorVar

  • 3) Listen to Noeld and try to find a set based approach for this.

    What is the goal of that stored proc?

  • Time for me to agree with ALL your points

     


    * Noel

  • Agreed... but that could cause an infinite loop so I might retract that statement .

  • Fantastic - thanks a lot Brendthess. I really appreciate it

  • Looks like my font was wrong :

    3) Listen to Noeld and try to find a set based approach for this.

    What is the goal of that stored proc?

  • Hi Remi

    I am writing a stored procedure that opens a cursor with all the records (select * from @table) of the tablename supplied as parameter by the user. It formates each row according to the need and populates a temporary table for further business logic implementation and processing.

    Is the global cursor approach not recommended?

    Thanks

    Gary

  • I've been working for 18 months with sql server. I never found a situation where a cursor is the only solution and where a set based approach doesn't exists. I've seen a single situation where the cursor was faster than the set-based approach (out of 5k-8K queries). I've also seen a report with an approach like this go down from 24 hours to 1 minute. Now I don't know how complexe the problem is but I suggest you at least consider a set based approach, maybe even DTS if you have a lot of steps needed to do this report.

    Can you explain in more details what you need to do. Ideally with some sample data, table definition and the expected results?

  • OK

    I am actually using the identity col approach at the moment. But there will be situations when I cannot use Identity col all the time. I have different tables that get popluated based on criteria. And then INSERT stmts are generated calling a different stored procedure.

    HOW it works:

    Table A has some rows that I want.

    Exec Stored Proc 1 and create Table B from Table A  with required rows and introduce an Identity column in Table B.

    Exec Stored Proc 2 @tablename = Table B

    I scroll via a while loop through the records in Table B based on Identity Col criteria (where Ident_col = current counter value) - I want to eliminate this dependency of identity column.

    Then I open another While loop for each column in the row. Check if the column is Primary Key, get the value into a #tempTable, if yes:

    PRINT 'IF NOT EXISTS(SELECT * FROM @TABLENAME WHERE PK = value)'

    I put the value into the temporary table becuase I was getting same type of problems becuase of the dynamic SQL (@tablename), I could not put the value in a variable. Want to reolve this too.

    Then add the column to the columnlist and value to the valuelist.

    Once the inner while loop finishes:

    PRINT 'INSERT INTO @TABLE (COLLIST) VALUES (VALUELIST)

    In this process values for Table_id column(Identity Col) are also generated, so I have to check if the column is Table_Id, do not add to columnlist or valuelist) - a drawback of the current situation.

    Here is the code:

    ALTER  PROCEDURE up_generate_inserts

    (@tablename varchar(255),

    @target varchar(255))

    AS

    BEGIN

    set nocount on

    declare @cnum int

    declare @rnum int

    declare @rcount int

    declare @ccount int

    declare @onedone int

    --declare @tablename varchar(255)

    --declare @target varchar(255)

    declare @columnname varchar(255)

    declare @data_type varchar(255)

    declare @sql nvarchar(4000)

    declare @if_stmt varchar(4000)

    declare @columnvalue varchar(8000)

    declare @INS_Stmt varchar(8000)

    declare @columnlist varchar(8000)

    declare @valuelist varchar(8000)

    --set @tablename = 'CNH_Format_Cat2'

    --set @target = 'Format'

    If exists(select [Name] from tempdb.dbo.sysobjects where [name] = '##TEMPCOUNT')

     DROP TABLE ##TEMPCOUNT 

    set @sql = 'Select COUNT(*) as COUNT into ##TEMPCOUNT from ' + @tablename

    set @columnlist = ''

    set @valuelist = ''

    EXECUTE sp_executesql @sql

    SELECT @rcount = [COUNT] FROM ##TEMPCOUNT

     

    set @rnum = 1

    --ROW LOOP

    while (@rnum < =  @rcount)

    begin

     SELECT @ccount = max(ordinal_position) from information_schema.columns where table_name = @tablename

     set @cnum = 1

     set @onedone = 0

     SET @IF_stmt = 'IF NOT EXISTS ( SELECT * FROM ' + @target + ' WHERE '

     SET @INS_stmt = 'INSERT INTO ' + @target

     set @columnlist = ''

     set @valuelist = ''

     --COLUMN LOOP

     While (@cnum < = @ccount)

     Begin

      select @columnname = column_name, @data_type = data_type from information_schema.columns where table_name = @tablename and ordinal_position = @cnum    

      

      

      IF @Columnname in (select Column_Name from information_schema.Key_Column_Usage kcu

       inner join information_schema.Table_Constraints tc on kcu.constraint_name = tc.constraint_name

       where tc.Constraint_Type = 'PRIMARY KEY' AND KCU.TABLE_NAME = @tablename)

       Begin

       IF @onedone = 0

        Begin

        set @if_stmt = @if_stmt + @columnname + ' = '

        set @sql = 'SELECT ' + CAST (@columnname as Varchar(1024)) + ' as PKCOL INTO ##TMPCOL from ' + @tablename + ' where tabID = ' + CAST(@rnum as Varchar(5))

        

        If exists(select [Name] from tempdb.dbo.sysobjects where [name] = '##TMPCOL')

         DROP TABLE ##TMPCOL

        EXECUTE sp_executesql @sql

        select @columnvalue = ISNULL(PKCOL, 'NOTHING') FROM ##TMPCOL

        set @if_stmt = @if_stmt + @columnvalue

        set @onedone = 1

        end

       else

        Begin

        set @if_stmt = @if_stmt + ' AND ' + @columnname + ' = '

        If exists(select [Name] from tempdb.dbo.sysobjects where [name] = '##TMPCOL')

         DROP TABLE ##TMPCOL

        set @sql = 'SELECT ' + CAST (@columnname as Varchar(1024)) + ' as PKCOL INTO ##TMPCOL from ' + @tablename + ' where tabID = ' + CAST(@rnum as Varchar(5))

        set @onedone = 1   

        EXECUTE sp_executesql @sql

        select @columnvalue = ISNULL(PKCOL, 'NULL') FROM ##TMPCOL

        

        set @if_stmt = @if_stmt + @columnvalue

        end

       end

      

      If exists(select [Name] from tempdb.dbo.sysobjects where [name] = '##TEMPCOL')

       DROP TABLE ##TEMPCOL

      

      set @sql = 'SELECT ' + CAST (@columnname as Varchar(1024)) + ' as CCOL INTO ##TEMPCOL from ' + @tablename + ' where tabID = ' + CAST(@rnum as Varchar(5))

      EXECUTE sp_executesql @sql

      

      if (SELECT CCOL FROM ##TEMPCOL) IS NOT NULL

       select @columnvalue = CCOL FROM ##TEMPCOL

      ELSE  

       SET @COLUMNVALUE = 'NULL'

      

      IF @Data_Type IN ('char','varchar','nchar','nvarchar') AND @columnvalue <> 'NULL'

       Set @columnvalue = '''' + cast(@ColumnValue as varchar(4000)) + ''''

      IF @Data_Type IN ('datetime','smalldatetime') AND @columnvalue <> 'NULL'

       Set @columnvalue = convert(varchar(100),convert (datetime, @columnvalue), 101)

    --   Set @columnvalue = convert (varchar(100), @columnvalue, 101) --Does not work

    --  DROP TABLE ##TEMPCOL

      

      

      If UPPER(@columnname) = 'TABID'

       set @columnvalue = ''

      

      

      If UPPER(@columnname) = 'TABID'

       set @columnname = ''

      

      SET @columnlist = @columnlist + ' ' + @columnname + ','

      set @valuelist = @valuelist + ' ' +  @columnvalue + ','

      set @cnum = @cnum + 1

     end

    SET @IF_STMT = @IF_STMT + ' )'

    PRINT @IF_STMT

    SET @columnlist = LEFT(@columnlist, LEN(@columnlist)-1)

    SET @columnlist = RIGHT(@columnlist, LEN(@columnlist)-2)

    SET @valuelist = LEFT(@valuelist, LEN(@valuelist)-1)

    SET @valuelist = RIGHT (LTRIM(@valuelist), LEN(@valuelist)-2)

    Set @INS_Stmt = @Ins_Stmt + '(' + @columnlist + ' ) Values (' + @valuelist + ' )'

    print @INS_Stmt

    print ''

    set @rnum = @rnum + 1

    end

    End

    -----------------------

    I know people have written generate insert stored procs, already, but they don't solve my purpose, as I need the IF NOT EXISTS stmt as well and avoid primary key errors - thats how our business works. We generate stuff over and over again and need to first check if the primary key will be violated.

    Therefore, the goals are:

    1. Eliminate Identity Col dependency

    2. Eliminate the need to check whether column is TabID (extra Identity col added to @tablename)

     

     

     

  • Sorry to hear that. Looks like you're stuck with this situation. Good luck with that.

  • I'd replace the section of code:

     If exists(select [Name] from tempdb.dbo.sysobjects where [name] = '##TEMPCOUNT')

     DROP TABLE ##TEMPCOUNT

     set @sql = 'Select COUNT(*) as COUNT into ##TEMPCOUNT from ' + @tablename

     set @columnlist = ''

     set @valuelist = ''

     EXECUTE sp_executesql @sql

     SELECT @rcount = [COUNT] FROM ##TEMPCOUNT

    to:

     SET @columnlist = ''

     SET @valuelist = ''

     CREATE TABLE #TempCount ([COUNT] INT)

     SET @sql = 'INSERT INTO #TempCount SELECT COUNT(*) FROM ' + @tablename

     EXECUTE sp_executesql @sql

     SELECT @rcount = [COUNT] FROM #TempCount

     DROP TABLE #TempCount

    1. A single # makes the table local to this thread, there's no chance this proc could be run on separate threads and one killing the table while the other is trying to read it.

    2. I reordered the steps because there are basically two processes going on.  Initializing variables and getting a count variable.  The "SET @sql" is not initializing data, it is setting it for a specific purpose.  I put it after "CREATE TABLE" simply because it reads better for a human.  I included the "DROP TABLE" for the same reason.

    3. It's good practice to make SQL key words stand out in your code.  The best way to do that is to capitalize the key words.  SQL is not case sensitive, humans are somewhat case sensitive.

    Later on you have "set @sql = 'SELECT ' + CAST (@columnname as Varchar(1024)) + ..."  This is rather silly.  @columnname is defined as VARCHAR(255), it isn't going to suddenly increase in size just because you cast it bigger.  (OK, you can increase the size, but you would have to cast to CHAR(1024) and then you are just taking up unneeded space.) The value of this variable comes from a "sysname" type of variable, which is equivalent to NVARCHAR(128), (256 bytes - 128 characters.) that when cast as VARCHAR will never be longer than 128 characters.  (Now maximum of 128 bytes. It might lose UNICODE characters, but since you probably aren't shipping this code to Europe/Asia, you are probably safe doing this cast.)  Same complaint about the ## table here.

    You've repeated "If UPPER(@columnname) = 'TABID'" in the last part of your proc.  You might consider setting that up as a BEGIN/END segment.

    First thing I did was copy your proc into a Microsoft Development Environment TextFile, so I could easily indent the code.  After that I could easily see the scope of BEGIN/END blocks.  This makes it more readable.  (If you removed them before posting, sorry about this comment.)  In the same vein, if a command spans lines, indenting the following line(s) makes it easier to see the lines are related.

    Suggest you rewrite:

     If UPPER(@columnname) = 'TABID'

       set @columnvalue = ''

     

     

      If UPPER(@columnname) = 'TABID'

       set @columnname = ''

     

      SET @columnlist = @columnlist + ' ' + @columnname + ','

      set @valuelist = @valuelist + ' ' +  @columnvalue + ','

    as:

     If UPPER(@columnname) <> 'TABID'

     BEGIN

      SET @columnlist = @columnlist + ' ' + @columnname + ','

      set @valuelist = @valuelist + ' ' +  @columnvalue + ','

     END

    Of course this whole logic emulates a cursor.  You'd be better off to re-write it so that the command reads:

    INSERT INTO TargetTable (ColumnList)

     SELECT ColumnList FROM SourceTable s

     LEFT JOIN TargetTable t ON s.PrimaryKey=t.PrimaryKey

     WHERE t.PrimaryKey IS NULL

    This proc would be a lot faster to execute (It doesn't read SourceTable data, just the schema.), produce a lot less output, and executing the output would be faster.  The variables in the output would be TargetTable, ColumnList, SourceTable, s.PrimaryKey=t.PrimaryKey, and t.PrimaryKey. (t.PrimaryKey would only be the first field, s.PrimaryKey=t.PrimaryKey would be the combination of all fields that make up the primary key.)

    What's going on:  The LEFT JOIN combines the two tables, retaining all the SourceTable rows and only the matching TargetTable rows.  Since you don't want to add rows that already exist in the target, the WHERE clause removes them.  (You could replace the WHERE with AND, but I think this is more readable.)  Since all fields in a primary key must not be null, it doesn't matter if the first field in the target is nullable because null values in the target would never match.  The only way the first field would have a value in the target is if all primary fields matched.

    Oops, one more variable. Since both tables will have the same ColumnList, all fields in the SELECT ColumnList should be prefixed with "s." and you should not have the prefix in the (ColumnList) fields.

  • Thanks Kenneth - it was informative. I will make the corrections.

    I could not understand this part:

    INSERT INTO TargetTable (ColumnList)

     SELECT ColumnList FROM SourceTable s

     LEFT JOIN TargetTable t ON s.PrimaryKey=t.PrimaryKey

     WHERE t.PrimaryKey IS NULL

    Does the above produce same output? The goal is to first run the generated script in Beta, QA, Staging for testing and then to Production environments.

    Do u have the re-written code that you can send me?

    Also is it possible to eliminate this:

      set @sql = 'SELECT ' + CAST (@columnname as Varchar(1024)) + ' as CCOL INTO ##TEMPCOL from ' + @tablename + ' where tabID = ' + CAST(@rnum as Varchar(5))

      EXECUTE sp_executesql @sql

      

      if (SELECT CCOL FROM ##TEMPCOL) IS NOT NULL

       select @columnvalue = CCOL FROM ##TEMPCOL

    WITH:

    set @sql = 'SELECT @columnvalue =' + CAST (@columnname as Varchar(1024)) + ' from ' + @tablename + ' where tabID = ' + CAST(@rnum as Varchar(5))

    EXECUTE sp_executesql @sql

    - The above fails.

    MOST important: is how to write a cursor and eliminate dependency on TABID - which I am still unable to do even after declaring it as Global.

    Thanks

    Gary

      

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

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