CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7532

Declared variable isn't seen as declared

I have a variable that I am populating from a select statement(@ENTY_TABLE_NAME):

DECLARE @ENTY_TABLE_NAME varchar(500) 
DECLARE @ENTY_ID INT;
DECLARE @FIELD_ID INT;
DECLARE @VALUE VARCHAR(50);

SET @ENTY_ID = 1
SET @FIELD_ID = 90
SET @VALUE = '0'

SET @ENTY_TABLE_NAME = 
(SELECT TOP 1 ENTY_TABLE_NAME
FROM ENTY
WHERE ENTY.ENTY_ID=@ENTY_ID);

SELECT DISTINCT ATTR_VAL 
FROM @ENTY_TABLE_NAME _ATTR
WHERE FIELD_ID= @FIELD_ID AND ATTR_VAL LIKE'%' + @VALUE + '%'
ORDER BY ATTR_VAL

After I am trying to use another select statement but it is returning an error:

Msg 1087, Level 16, State 1, Line 26
Must declare the table variable "@ENTY_TABLE_NAME".

Why is it saying I haven't declared a variable that I have an fact declared? How do I fix this?

Upvotes: 0

Views: 183

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

The problem is not that the variable is not declared. The problem is that you cannot use a variable as a table name in a regular SQL statement (unless the variable is a table variable).

If the table name needs to be a variable, then use dynamic SQL:

declare @sql nvarchar(max);

set @sql = '
SELECT DISTINCT ATTR_VAL 
FROM @ENTY_TABLE_NAME _ATTR
WHERE FIELD_ID = @FIELD_ID AND ATTR_VAL LIKE ''%'' + @VALUE + ''%'''
ORDER BY ATTR_VAL';

set @sql = replace(@sql, '@ENTY_TABLE_NAME', ENTY_TABLE_NAME);

exec sp_executesql @sql, N'@FIELD_ID int, @VALUE varchar(50)', @FIELD_ID = @FIELD_ID, @VALUE = @VALUE;

Upvotes: 6

Related Questions