CODEWITHSUNDEEP
sql-serverpartitioning
James Watt
James Watt

Reputation: 225

Programmatically name FILEGROUP in SQL server?

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

Upvotes: 1

Views: 1674

Answers (2)

AakashM
AakashM

Reputation: 63338

Use dynamic SQL:

DECLARE @FileGroupName sysname
SET @FileGroupName = 'Foo'

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

Think about SQL injection, though.

Upvotes: 0

Andrew
Andrew

Reputation: 27294

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

Upvotes: 2

Related Questions