T-SQL nested cursor with parameter

Question

I have two cursors, I want to loop through first one, and use its values as parameters for the second cursor and then do some processing. I know how to do this in PL-SQL, but T-SQL puzzles me.

I have gotten so far. For some reason the nested cursor does not print anything.

DECLARE @period DATE
DECLARE @table_type VARCHAR(2)
DECLARE @clmn_clr VARCHAR(100)
DECLARE @clmn_per VARCHAR(100)
DECLARE @period_num INT
DECLARE @period_date DATE
DECLARE @table_type2 VARCHAR(2)

DECLARE c_table CURSOR FOR SELECT   period,
                                    table_type
                            FROM    #period_table
DECLARE c_period CURSOR LOCAL FAST_FORWARD FOR SELECT   clmn_clr,
                                                        clmn_per,
                                                        period_num,
                                                        period_date,
                                                        table_type
                                                 FROM   #column_period
                                                 WHERE  table_type = @table_type

OPEN c_table
    FETCH NEXT FROM c_table
    INTO @period, @table_type
WHILE @@FETCH_STATUS = 0
BEGIN
    print @period
    print @table_type
    print '------------'
    FETCH NEXT FROM c_table
        INTO @period, @table_type

    --Nested cursor
    OPEN c_period
        FETCH NEXT FROM c_period
        INTO @clmn_clr, @clmn_per, @period_num, @period_date, @table_type2

    BEGIN
        print @clmn_clr
        print @clmn_per
        print @period_num
        print @period_date
        print @table_type2
        FETCH NEXT FROM c_period
            INTO @clmn_clr, @clmn_per, @period_num, @period_date, @table_type2
    END
    CLOSE c_period
    DEALLOCATE c_period
END
CLOSE c_table
DEALLOCATE c_table

It does print the first line from cursor c_table but that is all.

Also, any comments for how to do this in T-SQL better are very welcomed.

IVNSTN · Accepted Answer · 2016-07-01 07:23:58Z

1

There is no need in nesting, here is the only cursor that can do everything:

DECLARE c_period CURSOR LOCAL FAST_FORWARD FOR
    SELECT  p.period,
            c.clmn_clr,
            c.clmn_per,
            c.period_num,
            c.period_date,
            c.table_type
    FROM   #period_table p
    INNER JOIN #column_period c
    ON c.table_type = p.table_type
    ORDER BY p.table_type

I never tested it but id does not seem to me that MSSQL allows something like "dynamic parameter binding" for cursor. Since you opened it - it's done. It only can react on data modifications if appripriate options applied.

You may reopen cursor if you still need nesting for any purpose. Fetch @table_type from outer cursor, then DECLARE ... OPEN nested cursor.

Also note that strings to print (if this is your real subject) can be obtained with single select, withot any cursors (search "string aggregation" for mssql).

answered Jul 1, 2016 at 7:23

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HonzaB Over a year ago

Thank you, but I need nesting, this was simplified code. No, printing is just for debugging, but can be useful in the future.

IVNSTN Over a year ago

@HonzaB take a look at third paragraph for nested cursors.

HonzaB · Accepted Answer · 2016-07-01 07:27:38Z

1

Here is working solution I have found - I only moved the nested cursor inside.

DECLARE @period DATE
DECLARE @table_type VARCHAR(2)

DECLARE @clmn_clr VARCHAR(100)
DECLARE @clmn_per VARCHAR(100)
DECLARE @period_num INT
DECLARE @period_date DATE
DECLARE @table_type2 VARCHAR(2)

DECLARE c_table CURSOR FOR SELECT   period,
                                    table_type
                            FROM    #period_table
--Fetch first row
OPEN c_table
    FETCH NEXT FROM c_table
    INTO @period, @table_type
--Loop starts here, until it is empty
WHILE @@FETCH_STATUS = 0
BEGIN
    print @period
    print @table_type
    print '----------'

    --Nested cursor
    DECLARE c_period CURSOR LOCAL FAST_FORWARD FOR SELECT   clmn_clr,
                                                            clmn_per,
                                                            period_num,
                                                            period_date,
                                                            table_type
                                                     FROM   #column_period
                                                     WHERE  table_type = @table_type
    OPEN c_period
        FETCH NEXT FROM c_period
        INTO @clmn_clr, @clmn_per, @period_num, @period_date, @table_type2
    WHILE @@FETCH_STATUS = 0
    BEGIN
        print @clmn_clr
        print @clmn_per
        print @period_num
        print @period_date
        print @table_type2
        FETCH NEXT FROM c_period
            INTO @clmn_clr, @clmn_per, @period_num, @period_date, @table_type2
    END
    CLOSE c_period
    DEALLOCATE c_period

    FETCH NEXT FROM c_table
        INTO @period, @table_type
END
CLOSE c_table
DEALLOCATE c_table

answered Jul 1, 2016 at 7:27

HonzaB

7,4056 gold badges34 silver badges46 bronze badges

3 Comments

Gottfried Lesigang Over a year ago

Maybe I do not get this correctly, but why do you need a CURSOR at all? Cursors are send from the dark side of the universe to pull poor db people away from the light of set based approaches... What are you doing in this cursor? At the moment it is a procedural (sigh) equivalent of an INNER JOIN, isn't it?

HonzaB Over a year ago

Yes, it seems like case for a simple INNER JOIN, but it is not.I am well aware of disadvantages. But those are only small auxiliary tables (<50 rows), which will execute a lot of queries, which are similar, but need different parameters. Which are stored in those tables. So the heavy lifting will be done by simple SQL.

Gottfried Lesigang Over a year ago

Ah I see... Happy Coding!

Collectives™ on Stack Overflow

T-SQL nested cursor with parameter

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related