Nested SQL cursor prints variable, but will not update

Question

I'm using nested cursors to update the first column on every table of my Database, to populate data for an online reporting program.

when testing my cursors using a print statement, everything runs fine. When I switch to the update command, I get an error saying variable @table must be defined. Why does the variable only work for a print, and how can I make this work correctly?

declare @table varchar (30)
declare @Column varchar (30)

DECLARE [TABLE] CURSOR FOR SELECT TABLE_NAME FROM 
postfixing.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' order 
by table_name
OPEN [TABLE]
FETCH FROM [TABLE] INTO @TABLE
WHILE @@FETCH_STATUS=0
    BEGIN
    DECLARE [COLUMN] CURSOR FOR SELECT Top 1 Column_name FROM 
postfixing.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@table order by 
column_name
    OPEN [COLUMN]
    FETCH FROM [COLUMN] INTO @COLUMN
    WHILE @@FETCH_STATUS=0
        BEGIN
        PRINT @TABLE+'   '+@COLUMN
        --UPDATE @TABLE SET @COLUMN=@COLUMN
        FETCH FROM [COLUMN] INTO @COLUMN
        END
    DEALLOCATE [COLUMN]
    FETCH FROM [TABLE] INTO @TABLE
END
DEALLOCATE [TABLE]

Yeah. You can't use variables like that. You need to use dynamic SQL to replace identifiers in queries. — Gordon Linoff
– Gordon Linoff, Commented Jul 22, 2017 at 13:46
I apologize for any ignorance, but I've set my update statement as a variable using (I think) dynamic SQL. the issue I am now having is that it skips the first table, and now tries to update the second table with the columns for the first. It looks like the outer cursor will loop through the first table, it just never steps in to update the column — ThexTallxDude
– ThexTallxDude, Commented Jul 22, 2017 at 16:27
Another case of optimistic programming. You want UPDATE @TABLE SET @COLUMN=@COLUMN to do what you want. Sometimes @COLUMN should be a variable and the value should be used or set. Other times the value should should be substituted into a statement, then the statement reparsed (perhaps recursively). So what would SET @COLUMN=@COLUMN mean? SET [MyFirstColumn] = [MyFirstColumn]? SET [MyFirstColumn] = 'MyFirstColumn'? Or simply SET @COLUMN = @COLUMN? sp_executesql? — HABO
– HABO, Commented Jul 22, 2017 at 19:11

JMabee · Accepted Answer · 2017-07-23 11:27:12Z

1

Well since you are only getting the first row you can eliminate the nested cursor and it should do what you want:

declare @table varchar (30)
declare @Column varchar (30)

DECLARE [TABLE] CURSOR FOR SELECT TABLE_NAME FROM 
postfixing.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' order 
by table_name
OPEN [TABLE]
FETCH FROM [TABLE] INTO @TABLE
WHILE @@FETCH_STATUS=0
    BEGIN

    SET @COLUMN = (SELECT Top 1 Column_name FROM 
postfixing.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@table order by 
column_name)

    PRINT @TABLE+'   '+@COLUMN
    --UPDATE @TABLE SET @COLUMN=@COLUMN

FETCH FROM [TABLE] INTO @TABLE
END
CLOSE [TABLE]
DEALLOCATE [TABLE]

answered Jul 23, 2017 at 11:27

JMabee

2,3102 gold badges11 silver badges15 bronze badges

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

1 Comment

ThexTallxDude Over a year ago

A combination of this, and dynamic SQL to set the table name in the update statement resolved my issues. Thank you JM_ and Gordon Linoff for helping me with this. I still have a few questions on why it was having these issues, but I can probably figure that out from here.

Collectives™ on Stack Overflow

Nested SQL cursor prints variable, but will not update

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related