Programmatically name FILEGROUP in SQL server?

Question

I'm trying to write a stored procedure that will create a new FILEGROUP based upon a given date parameter. What I want to see is a FILEGROUP called something like '2010_02_01'. What I get is a FILEGROUP called '@PartitionName'.

ALTER PROCEDURE [dbo].[SP_CREATE_DATE_FILEGROUP] @PartitionDate DATETIME
AS
DECLARE
    @PartitionName VARCHAR(10);
BEGIN
    SET @PartitionName = REPLACE(LEFT(CONVERT(VARCHAR, @PartitionDate, 120), 10), '-', '_');
    ALTER DATABASE MSPLocation ADD FILEGROUP [@PartitionName];
END

Answer 1

You are going to end up having to using sp_executesql to execute it, something like

declare @sql nvarchar(4000)
setl @sql = 'ALTER DATABASE MSPLocation ADD FILEGROUP[' + @PartitionName + ']'
exec sp_executesql @sql

Answer 2

Use dynamic SQL:

DECLARE @FileGroupName sysname
SET @FileGroupName = 'Foo'

EXEC ('ALTER DATABASE MyDatabase ADD FILEGROUP [' + @FileGroupName + ']')

Think about SQL injection, though.