How to use a variable to specify filegroup in SQL Server

Question

I want to alter a table to add a constraint during upgrade on a SQL Server database.

This table is normally indexed on a filegroup called 'MY_INDEX' - but may also be on a database without this filegroup. In this case I want the indexing to be done on the 'PRIMARY' filegroup.

I tried the following code to achieve 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

ALTER TABLE [dbo].[mytable]
ADD CONSTRAINT [PK_mytable] PRIMARY KEY
(
    [myGuid] ASC
)
ON @fgName -- fails: 'incorrect syntax'

However, the last line fails as it appears a filegroup cannot be specified by variable.

Is this possible?

Reagan Williams · Accepted Answer · 2010-06-11 23:32:43Z

2

I've 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 that helps....

answered Jun 11, 2010 at 23:32

Reagan Williams

3411 silver badge8 bronze badges

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

1 Comment

g t Over a year ago

Thanks, I've gone with this, though it's more of a workaround than a solution. Wonder why variables aren't allowed there.

marc_s · Accepted Answer · 2010-06-11 11:40:04Z

1

I would believe that if SQL Server returns an incorrect syntax, there's probably no way to do this, unfortunately.

You will need to specify your filegroup names as string literals.

You will 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'

answered Jun 11, 2010 at 11:40

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

1 Comment

g t Over a year ago

Good idea - the two answers are a tradeoff between losing syntax highlighting and having code duplication!

Aaron D · Accepted Answer · 2014-01-11 19:07:21Z

0

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

edited Jan 11, 2014 at 19:07

Aaron D

5,8971 gold badge39 silver badges55 bronze badges

answered Jan 11, 2014 at 18:48

Marcos

1

Collectives™ on Stack Overflow

How to use a variable to specify filegroup in SQL Server

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related