title: Generate an serial number for primary key using sql e.g 16-001 and auto increment on every new entry 16-002 and so on
body: <p>I created a function but it throws an conversion exception.</p>
  
  <blockquote>
    <p>Msg 245, Level 16, State 1, Line 10
    Conversion failed when converting the varchar value '16-001' to data type int.</p>
  </blockquote>
  
  <p>This statement causes the error. +'-'+  RIGHT(@pId,3)+1</p>
  
  <pre><code>Create Function NextProjectNumber() 
  Returns char(6) 
  As 
  Begin 
  
      Declare @pId varchar(50) , @pYear int, @serial varchar(3), @currYear int
  
      SELECT 
          @pId = ProjectId 
      from 
          [JobPortalDb].[dbo].[Projects] 
      Where 
          ProjectId= (Select max(ProjectId) from [JobPortalDb].[dbo].[Projects])
  
      Set @currYear = (SELECT RIGHT(CONVERT(VARCHAR(8), year(GETDATE()), 1),2)) --SELECT @pId,@year
  
      If @pId IS NULL 
          Set @pId = (SELECT RIGHT(CONVERT(VARCHAR(8), year(GETDATE()), 1),2))+'-001' 
      Else 
          Set @pYear = SUBSTRING(@pId, 1, 2)
  
      If @pYear = @currYear 
          set @pId =(SELECT RIGHT(CONVERT(VARCHAR(8), year(GETDATE()), 1),2)) +'-'+ RIGHT(@pId,3)+1
  
      If @currYear &gt; @pYear 
          set @pId = (SELECT RIGHT(CONVERT(VARCHAR(8), year(GETDATE()), 1),2)) +'-001'
  
      Return @pId 
  End
  </code></pre>