Problem with a cursor and table variable...

Question

Problem with a cursor and table variable...

Brandon Hunt

Old Hand

Points: 303
More actions
March 11, 2004 at 9:13 am

#107232

What I'm trying to do: I need to dump a list of all FormulaNumbers in the database (there are about 25 table with a FormulaNumber field) and the associated table for that formula number. I only need one entry in my result set for each distinct number in a particular table
Here's the SQL I'm trying to run:
SET NOCOUNT ON
DECLARE tableCurs CURSOR FAST_FORWARD FOR
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'FormulaNumber'
DECLARE @resultTable TABLE (TableName varchar(50) NULL, FormulaNumber int NULL)
DECLARE @tableName varchar(30)
DECLARE @formNum int
OPEN tableCurs
FETCH NEXT FROM tableCurs INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE formCurs CURSOR FAST_FORWARD FOR
        SELECT DISTINCT FormulaNumber FROM @tableName
    OPEN formCurs
    FETCH NEXT FROM formCurs INTO @formNum
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @resultTable (FormulaNumber) VALUES (@formNum)
        FETCH NEXT FROM formCurs INTO @formNum
    END
    UPDATE @resultTable SET TableName = @tableName WHERE TableName IS NULL
    CLOSE formCurs
    DEALLOCATE formCurs
    FETCH NEXT FROM tableCurs INTO @tableName
END
CLOSE tableCurs
DEALLOCATE tableCurs
SELECT * FROM @resultTable
SET NOCOUNT OFF
The error message I'm getting is:
Server: Msg 137, Level 15, State 2, Line 21
Must declare the variable '@tableName'.
Line 21 is OPEN formCurs.

Thanks for any help,

-Brandon

Viewing 4 posts - 1 through 3 (of 3 total)

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

Brandon Hunt Old Hand Points: 303 More actions · Answer 1

I think the problem is that SQL things @tableName is a Table variable and isn't converting it out to an actual table name in this statement.

DECLARE formCurs CURSOR FAST_FORWARD FOR

SELECT DISTINCT FormulaNumber FROM @tableName

I tried using Dynamic SQL in the FOR clause of the cursor, but it didn't like that either.

-Brandon

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

You can't just substitute a variable for an object identifier in a statement unless you use dynamic SQL. Dynamic SQL can be used to declare cursors but gets sticky because of scope issues. As this is obviously an ad hoc utility, I would just cheat with something like this (ignore the error messages):

CREATE TABLE #t(Tablename varchar(261), FormNum int)

INSERT #t

EXEC sp_msforeachtable "SELECT DISTINCT '?', FormulaNumber FROM ?"

SELECT *

FROM #t

--Jonathan

Brandon Hunt Old Hand Points: 303 More actions · Answer 3

Thanks, Jonathan. That worked like a charm.

-Brandon