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

Question

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.

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.)