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