How to use a variable to specify a filegroup in SQL Server

Question

I want to modify the table to add a constraint during a SQL Server database upgrade.

This table is usually indexed in a filegroup named "MY_INDEX", but can also be in a database without this filegroup. In this case, I want the indexing to be performed in the "PRIMARY" filegroup.

I tried to execute the following code:

DECLARE @fgName AS VARCHAR(10) SET @fgName = CASE WHEN EXISTS(SELECT groupname FROM sysfilegroups WHERE groupname = 'MY_INDEX') THEN QUOTENAME('MY_INDEX') ELSE QUOTENAME('PRIMARY') END ALTER TABLE [dbo].[mytable] ADD CONSTRAINT [PK_mytable] PRIMARY KEY ( [myGuid] ASC ) ON @fgName -- fails: 'incorrect syntax' 

However, the last line fails because it seems that the filegroup cannot be specified by a variable.

Is it possible?

Answer 1

I found that dynamic sql works when passing variables in DDL statements.

Try something like this:

 DECLARE @fgName AS VARCHAR(10) SET @fgName = CASE WHEN EXISTS(SELECT groupname FROM sysfilegroups WHERE groupname = 'MY_INDEX') THEN QUOTENAME('MY_INDEX') ELSE QUOTENAME('PRIMARY') END DECLARE @sql as varchar(1024) SET @sql = 'ALTER TABLE [dbo].[mytable] ADD CONSTRAINT [PK_mytable] PRIMARY KEY ( [myGuid] ASC ) ON ' + @fgName EXEC(@sql) 

I hope this helps ....

Answer 2

I would believe that if SQL Server returns incorrect syntax , there may be no way to do this, unfortunately.

You will need to specify the names of your filegroups as string literals.

You probably just have to rewrite your script to be something like:

 IF EXISTS(SELECT groupname FROM sysfilegroups WHERE groupname = 'MY_INDEX') ALTER TABLE [dbo].[mytable] ADD CONSTRAINT [PK_mytable] PRIMARY KEY([myGuid] ASC) ON 'MY_INDEX' ELSE ALTER TABLE [dbo].[mytable] ADD CONSTRAINT [PK_mytable] PRIMARY KEY([myGuid] ASC) ON 'PRIMARY' 

Answer 3

Replace ELSE QUOTENAME('PRIMARY') with ELSE QUOTENAME('[PRIMARY]')