SQL Server - how to use 'ALTER INDEX' with variables as the parameters

Question

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.

Andrew Hare · Accepted Answer · 2009-03-18 00:20:33Z

14

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);

edited Mar 18, 2009 at 0:20

answered Mar 18, 2009 at 0:07

Andrew Hare

353k75 gold badges649 silver badges642 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shearichard Over a year ago

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.

Collectives™ on Stack Overflow

SQL Server - how to use 'ALTER INDEX' with variables as the parameters

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related