[ACCEPTED]-Bind a column default value to a function in SQL 2005-default-value

Accepted answer

Score: 25

The syntax to add a default like that would 2 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 1 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

Score: 7

IF someone wants to do it using the interface, typing 2

[dbo].[NEWDOC_Order]()

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

Score: 4

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

Right click on table and select Design

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

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

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

Source: stackoverflow.com

More Related questions

IF/ELSE Stored Procedure

Conditional Operator in SQL Where Clause

SQL Server 2005 Get First and Last date for any Month in any Year

How do I properly SELECT INTO @TempTable Multiple times to return a single resultset?

How to create sequential number column index on table with data

Aggregate function checking 'if contains'

TSQL - ISNULL over multiple columns

retrieve the most recent record for each customer

Why can't I perform an aggregate function on an expression containing an aggregate but I can do so by creating a new select statement around it?

TSQL Error: A RETURN statement with a return value cannot be used in this context

Cookie Warning

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.