How to use a variable to specify filegroup in SQL Server

Posted by gt on Stack Overflow See other posts from Stack Overflow or by gt
Published on 2010-06-11T10:40:07Z Indexed on 2010/06/11 11:42 UTC
Read the original article Hit count: 275

Filed under:
|
|
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server