How to use a variable to specify filegroup in SQL Server
- by gt
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?
