How To Dynamically Declare Partition Range In Partition Function In Sql Server

I want to dynamically declare the range of my partition function. I don't want to hard-code the range value,like below: CREATE PARTITION FUNCTION PartFun(smallint)AS RANGE LEFT FO

Solution 1:

May not be an exact solution to what you are looking for. Here is the scenario I am faced with:

We have a DB that has multiple tables partitioned on a column named 'PriceListDate', but strangely, the data type is Varchar(8). We are in the middle of redesigning the application and the DB, so decided to change the data type to 'Date'. Here is how we are doing this dynamically:

IF NOTEXISTS (SELECTNULLFROM sys.partition_functions WHERE name = N'PriceListDateFunction')
BEGIN;
    DECLARE@CreatePartitionFunctionScript NVARCHAR(MAX);

    SET@CreatePartitionFunctionScript='CREATE PARTITION FUNCTION [PriceListDateFunction] (Date) AS RANGE LEFT FOR VALUES ('+
                                            STUFF((SELECT','+'N'+''''+CAST(prv.value asvarchar(8))+''''FROM sys.partition_range_values prv 
                                                        INNERJOIN sys.partition_functions pf 
                                                            ON pf.function_id = prv.function_id 
                                                        WHERE pf.name ='PriceListFunction'FOR XML PATH(''), TYPE 
                                                    ).value('.', 'NVARCHAR(MAX)'),1,1,'') --Get list of existing partitons from existing partition function+')';  
    --  Create Partition FunctionEXECUTE sp_executesql @CreatePartitionFunctionScript;
END;

Hope this gives you some ideas.

Raj

Solution 2:

Thanks, got idea. This is how I have solved it:

DECLARE @sqlcmd nvarchar(400),@ids nvarchar(100);
SET @sqlcmd = N'CREATE PARTITION FUNCTION PartFun(smallint) AS RANGE LEFT FOR VALUES (' + @ids + N')' ;
--PRINT@sqlcmd
EXEC SP_EXECUTESQL @sqlcmd

So the solution is:Dynamic SQL and making everything as NVARCHAR!

Solution 3:

Well what you can do is this:

Use QuoteName(exp,'''')

While creating your string of values (in this case ids) and the rest remains the same. Then execute it normally and it works great.