Bind a column default value to a function in SQL 2005

Question

I have a column containing items that can be sorted by the user:

DOC_ID  DOC_Order DOC_Name
   1       1        aaa
   2       3        bbb
   3       2        ccc

I'm trying to figure out a way to properly initialize DOC_Order when the entry is created. A good value would either be the corresponding DO-CID (since it is autoassigned), or MAX(DOC-ORDER) + 1

After a bit of googling I saw it was possible to assign a scalar function's return to the default column.

CREATE FUNCTION [dbo].[NEWDOC_Order] 
(
)
RETURNS int
AS
BEGIN

RETURN (SELECT MAX(DOC_ORDER) + 1 FROM DOC_Documents)

END

But each of my tries using MS SQL Management studio ended in a "Error validating the default for column 'DOC_Order'" message.

Any idea of what the exact SQL syntax to assign a function to DEFAULT is?

Sami

Answer 1

The syntax to add a default like that would be

alter table DOC_Order 
add constraint 
df_DOC_Order 
default([dbo].[NEWDOC_Order]())
for DOC_Order

Also, you might want to alter your function to handle when DOC_Order is null

Create FUNCTION [dbo].[NEWDOC_Order] 
(
)
RETURNS int
AS
BEGIN

RETURN (SELECT ISNULL(MAX(DOC_ORDER),0) + 1 FROM DOC_Documents)

END

Fiesole

Answer 2

IF someone wants to do it using the interface, typing

[dbo].[NEWDOC_Order]()

does the trick. You apparently need all brackets or it will reject your input.

Sami

Answer 3

Here's screen shots to do it through SQL Server Management Studio GUI:

1. Right click on table and select Design

2. Select DOC_Order column (or other column needing default) in the table's design view to see properties

3. Update Default Value or Binding with function name with brackets like so:

Note: as Luk stated, all brackets are needed including the schema (dbo in this case).

Achaean