Saturday, February 25, 2012

"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_checkMake 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.

No comments:

Post a Comment