views:

1309

answers:

2

Using T-SQL I've found that I can't use 'ALTER INDEX' with the table/index values in variables without getting a syntax error. Is there some way this could be done ? I'm on SQL Server 2005.

My code looks like this :

DECLARE @TABLENAME VARCHAR(256)
DECLARE @IDXNAME VARCHAR(256)
DECLARE @SCHEMAID INT
SET @TABLENAME = 'T1'
SET @IDXNAME = 'T1_IDX0'
-- The next line is OK as it hardcodes the variable names
ALTER INDEX T1_IDX0 ON T1 SET (ALLOW_PAGE_LOCKS = ON)
-- The next line generates a syntax error
ALTER INDEX @IDXNAME ON @TABLENAME SET (ALLOW_PAGE_LOCKS = ON)

The syntax error looks like this :

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@IDXNAME'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SET'.

The real code I'm working on is more complex than the above and being able to use variables would be useful. I guess one way around it would be use dynamic SQL but I'd really rather not if I could avoid it.

+2  A: 

Unfortunately what you are trying to do is not possible. The best workaround I can suggest is to build the alter statement as a string, concatenate the variables you have in the statement, and then exec() it.

Try something like this:

declare @alter varchar(200);
set @alter = 'ALTER INDEX ' + @IDXNAME + ' ON ' + @TABLENAME + ' SET (ALLOW_PAGE_LOCKS = ON)';

exec(@alter);
Andrew Hare
Thanks I'd forgotten you could exec single lines of SQL (thought you had to build an SP or something) so doing what you suggest is fine. Thanks again for your help.
southof40
My pleasure, glad I could help :)
Andrew Hare
A: 

Thanks! This was the one I needed...!