Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    11

    Unanswered: "must declare variable" when variable has been declared!

    Any idea why the following message is returned?

    Server: Msg 137, Level 15, State 2, Line 5
    Must declare the variable '@table'.

    The script is created to run through each column in the database and check it against cross referenced data in other table. It had to be built because of the database we own has not a single referntial constraint in it (long story but it's from the the age old "our code works better than a well designed database" school of thought).

    excuse the poorly laid out SQL - it's hard to copy and paste into this thing....

    Thanks
    Yal

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

    SET NOCOUNT ON

    DECLARE @table VARCHAR(32)
    ,@column VARCHAR(32)
    ,@x_table VARCHAR(32)
    ,@x_column VARCHAR(32)
    ,@x_type VARCHAR(32)
    ,@problem VARCHAR(32)
    ,@count VARCHAR(32)

    DECLARE xref_check CURSOR FOR
    SELECT table_name, column_name, xref_table, xref_column, xref_type
    FROM xref_check

    OPEN xref_check
    FETCH NEXT FROM xref_check INTO @table, @column, @x_table, @x_column, @x_type

    WHILE @@FETCH_STATUS = 0
    BEGIN

    IF @x_table = 'PS_XREF'
    BEGIN
    -- SELECT @table, @column, @x_table, @x_column, @x_type
    EXEC ('IF (SELECT COUNT('+ @column +')
    FROM '+ @table +'
    WHERE '+ @column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @x_type +''')) > 0
    BEGIN
    SELECT @table, @column, @x_table, @x_column, @x_type, x.'+ @column +'
    FROM '+ @table +' x
    WHERE '+ @column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @x_type +''')
    END'
    )

    END

    ELSE
    BEGIN
    -- SELECT @table, @column, @x_table, @x_column, @x_type
    EXEC ('IF (SELECT COUNT('+ @column +')
    FROM '+ @table +'
    WHERE '+ @column + ' NOT IN (SELECT '+ @x_column +' FROM '+ @x_table +')) > 0
    BEGIN
    SELECT @table, @column, @x_table, @x_column, @x_type, x.'+ @column +'
    FROM '+ @table +' x
    WHERE '+ @column + ' NOT IN (SELECT '+ @x_column +' FROM '+ @x_table +')
    END')
    END

    FETCH NEXT FROM xref_check INTO @table, @column, @x_table, @x_column, @x_type

    END

    CLOSE xref_check
    DEALLOCATE xref_check

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Make sure your references to these variables exist outside the string. For example:

    @x_type +''')) > 0
    BEGIN
    SELECT @table, @column, @x_table, @x_column, @x_type, x.'

    should have @table, @column ... outside the string like you did before this statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •