I want to use a new SEQUENCE each year (See here: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year )

However you can not use NEXT VALUE FOR inside a CASE statement. Which normally makes sense. However I am hoping that I have a legit edge case.

Is there any way around this (which wouldn't need elevated permissions in prod)?

Perhaps with some indirection, wrapping each NEXT VALUE FOR in some UDF and then call them in a CASE statement?

Or is that just crazy talk?

  • 1
    Why not use an identity column and create a job to reseed each year? Apr 19 '16 at 20:50
  • @Kin Wont that need elevated permissions to successfully run? Apr 19 '16 at 20:56
  • In the answer itself @gbn has mentioned - its not possible. You should have a sql agent job that resets the sequence every year.
    – Kin Shah
    Apr 19 '16 at 20:56
  • 1
    The table owner can do it. As can db_owner. Either way that's fine. If you are setting up a SQL Agent job you easily run the job under an id with the required permissions. No user will need elevated permissions. Apr 19 '16 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.