TSQL Scope with table variable

Question

I have a list of tables, some of which have column Employee_ID. At least one table is missing at least one Employee_ID. I want to find Employee_ID's missing from other tables.

I run the following, but get a, "Must declare scalar variable," error on @temp_employees. I believe the scope is correct.

How do I use the table variable @temp_employees inside TSQL while maintaining its scope so I use it for the entire loop?

Note: There are many versions of the software and many table structure changes throughout the years. I do not want to write a version specific script for each table structure change as the missing Employee_ID(s) is consistently the source of the error in the software.

/***************************************************************
*  Find missing employee id's regardless of software version   *
***************************************************************/
USE [database name]

DECLARE @i AS INT,
        @iMAX AS INT,
        @SQL AS nvarchar(MAX),
        @table AS nvarchar(MAX)

DECLARE @temp_tables TABLE (
    id int identity(1,1),
    table_name nvarchar(MAX) NULL
)       

DECLARE @temp_employees TABLE (
    id int identity(1,1),
    employee_id int NOT NULL,
    table_name nvarchar(255) NULL
)

INSERT @temp_tables (table_name) SELECT t.name FROM sys.tables t 
    INNER JOIN sys.columns c ON t.object_id = c.object_id WHERE c.name = 'employee_id'
SELECT * FROM @temp_tables

SELECT @i = MIN(id) FROM @temp_tables
SELECT @iMAX = MAX(id) FROM @temp_tables

WHILE (@i < @iMAX)
BEGIN
    SELECT @table = table_name FROM @temp_tables WHERE id = @i

    SELECT @SQL = N'INSERT ' + @temp_employees + ' (employee_id, table_name) SELECT employee_id, ''' + @table + ''' FROM ' + @table + ' WHERE employee_id NOT IN (SELECT employee_id FROM ' + @temp_employees + ')'
    EXEC sp_executeSql @SQL

SET @i = @i + 1
END

SELECT * FROM @temp_employees

score 1 · Answer 1 · answered Feb 13 '13 at 16:58

1

When you use sp_executesql it executes in a different scope then the rest of the script. I have a short blog post dealing with that here.

answered Feb 13 '13 at 16:58

TimothyAWiseman

14,385
12
40
47

score 1 · Answer 2 · answered Feb 13 '13 at 17:40

1

...SELECT @SQL = N'INSERT ' + @temp_employees + '...

You're mixing nvarchar with table variable. That's why you get this error "Must declare scalar variable"

I think that you should create a persistent table

...
DECLARE temp_employees TABLE (
    id int identity(1,1),
    employee_id int NOT NULL,
    table_name nvarchar(255) NULL
)
...
    SELECT @SQL = N'INSERT temp_employees (employee_id, table_name)
...

answered Feb 13 '13 at 17:40

Daniel

1,052
9
11

I'm not in a position to add a temporary table as I will be dealing with the same scope issues for ~100 tables. Is there another workaround? Edited: "IS there" to "Is there" – Phillip Deneka Feb 15 '13 at 18:02
Well that was the explanation to your error. You can use temporary tables instead of table variables. `Declare #temp_employees... SELECT @SQL = N'INSERT #temp_employees...` and you'll be able to access #temp_employees in your code and your dynamic SQL. – Daniel Feb 15 '13 at 19:49

TSQL Scope with table variable

2 Answers2