title: How to dynamically declare PARTITION RANGE IN Partition function in sql server
body: <p>I want to dynamically declare the range of my partition function.
  I don't want to hard-code the range value,like below:</p>
  
  <blockquote>
    <p>CREATE PARTITION FUNCTION PartFun(smallint)AS RANGE LEFT FOR VALUES (1,2,3,4,5) </p>
  </blockquote>
  
  <p>The problem is:The column(ID in IDMASTER table) on which I want to create partition is of smallint datatype.</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Getting this error:</p>
  
  <blockquote>
    <p>"Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type."</p>
  </blockquote>
  
  <p>Any workaround idea?
  Also,is it that people mostly use hard coded values for partition range?</p>