Use return value from SQL function as default value on multiple columns?

Question

I have a table where I'd like to set two of its columns' default values to the same value. I'm using a SQL function which generates a unique value. I can set a column default for one of the columns easily enough using the function:

[PreCode] NVARCHAR(20) NOT NULL DEFAULT([dbo].GetPreCode()),

But how would I use the same value in my other column? I'd assume that if I used the same syntax as above for the other column that the function would generate another unique value instead of using the value that was generated for the first column. How can I get both columns to have the same default value from a single call to the SQL function?

If the purpose of that function is to generate a unique value every time it's called, how do you expect to return the same value for multiple calls? — Rick S
– Rick S, Commented Mar 7, 2017 at 16:36
You can't. There's no way you can pass any context to the function about what row you're generating data for, and this is a hard requirement if you want the function to return identical values in one single row. If you know for sure only one row is ever inserted, you can do shady stuff like key off sys.dm_tran_current_transaction in the function, but it's probably far more sane to do this in the code that performs the actual inserts, wrap it in a stored procedure, or use an AFTER INSERT trigger. — Jeroen Mostert
– Jeroen Mostert, Commented Mar 7, 2017 at 16:37
You would need to call the function exactly once, and store that value in a variable, and apply it to both columns. You can do this with a trigger (I prefer INSTEAD OF but you can also do it with an AFTER). If you have full control over the DML (e.g. all writes go through a stored procedure or data access layer), you could change that to generate the value, but you'd still need a trigger if you want to be sure that the values get set correctly when people go around your DAL. Yes, a trigger can detect whether a value has been supplied. Just take care up front that it handles mutli-row inserts. — Aaron Bertrand
– Aaron Bertrand, Commented Mar 7, 2017 at 16:47

Andrew O'Brien · Accepted Answer · 2017-03-07 17:29:16Z

1

You can use an INSTEAD OF INSERT trigger to set it, or you can have the second column be a computed column and set the value to that of the first.

You can try setting the second columns default value to the value of the first, but I'm not sure if it would wait for the first to be set before setting that of the second or if it would set it to NULL before applying a value to the first.

answered Mar 7, 2017 at 17:29

Andrew O'Brien

1,8432 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use return value from SQL function as default value on multiple columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related