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

There are alternatives to creating filegroups on the fly when partitioning, you can re-use existing ones if organised correctly. — Andrew
– Andrew, Commented Feb 2, 2010 at 10:23

Andrew · Accepted Answer · 2010-02-02 10:18:40Z

2

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

answered Feb 2, 2010 at 10:18

Andrew

27.4k5 gold badges67 silver badges90 bronze badges

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

2 Comments

James Watt Over a year ago

We've taken your advice and settled upon an alternative to creating them on the fly but thanks for this answer too.

Andrew Over a year ago

Have a read of sqlfascination.com/2009/10/15/… for some guidance / ideas.

AakashM · Accepted Answer · 2010-02-02 10:20:52Z

0

Use dynamic SQL:

DECLARE @FileGroupName sysname
SET @FileGroupName = 'Foo'

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

Think about SQL injection, though.

answered Feb 2, 2010 at 10:20

AakashM

63.5k17 gold badges154 silver badges190 bronze badges

1 Comment

Andrew Over a year ago

The parameter is coming in declared a datetime, so injection is not going to occur.

Collectives™ on Stack Overflow

Programmatically name FILEGROUP in SQL server?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related