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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment