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 FOR VALUES (1,2,3,4,5)
The problem is:The column(ID in IDMASTER table) on which I want to create partition is of smallint datatype.
Declare @IDS NVARCHAR(100)
SELECT @IDS = coalesce(@IDS + ', ', '') + CAST(a.ID AS VARCHAR) FROM
(SELECT DISTINCT TOP 100 ID from IDMASTER ORDER BY ID ) a
--PRINT @IDS --it prints 0,1,2,3,4 like that
CREATE PARTITION FUNCTION PartFun(smallint)
AS RANGE LEFT FOR VALUES (@IDS)
Getting this error:
"Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type."
Any workaround idea? Also,is it that people mostly use hard coded values for partition range?
Aucun commentaire:
Enregistrer un commentaire