In SQL Server, can you create a default constraint for a column using the result or return value from a Stored procedure, and if so how?

So I am thinking of something along the lines of this, but with valid SQL.

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_MyColumn]
DEFAULT (EXEC [dbo].[GetNextNumber]) FOR [MyColumn] GO

My reason for this is I have a table which has the sole responsibility of providing the next number to use, and a table which the default value must use this next number every time a new record is entered.

  • 2
    Why not use an IDENTITY or a SEQUENCE? Feb 9, 2017 at 10:57
  • I have not used a SEQUENCE before so will investigate that.I don't think use an identity as the column may have to be null, but if it is not then it must have the next number in the sequence.
    – Dib
    Feb 9, 2017 at 11:06
  • I think the SEQUENCE will work fro my, and the implementation looks fairly simple based upon [msdn.microsoft.com/en-us/library/ff878091.aspx] and [stackoverflow.com/a/14413420/254215].
    – Dib
    Feb 9, 2017 at 11:21

1 Answer 1


You can't use a SP in a default, but you can use a function. But presumably your SP code has to increment the value in the NextNumber table, so that won't work for you.

Your best bet for doing this within SQL is probably to have an INSERT trigger on MyTable which calls the SP and sets MyColumn.

However you'll have to carefully consider the behaviour of your SP code when multiple concurrent inserts are involved.
For example, you'll probably need to select the current value from the NextNumber table using an UPDLOCK hint to prevent another user reading and using the same value. (Note that the UPDLOCK hint will only prevent reads in this way if it's within a transaction [which triggers run within in by default], and if other selects on the NextNumber table use UPDLOCK as well.)

  • Yes i tried to update table from function but hit the no side effects rule. Will probably use the SEQUENCE mentioned in the comment by @ypercubeᵀᴹ
    – Dib
    Feb 9, 2017 at 11:25
  • Ah, I assumed the built-in methods weren't an option.
    – T.H.
    Feb 9, 2017 at 11:39
  • I wasn't aware of the SEQUENCE object existed until mentioned in that comment.
    – Dib
    Feb 9, 2017 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.