Bind a Column Default Value to a Function in SQL 2005

Bind a column default value to a function in SQL 2005

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

Create a type with default value and check constraint in SQL Server 2005

Yes, fine. Mercifully it is still all there is SQL Server 2008. Robyn Page's article on Simple-Talk explains it all

Robyn Page's SQL Server Data Validation Workbench

(I have a feeling I helped her a bit with that one)
Just create a Type, and bind defaults and rules to it.

The deprecation issue is a bit of a shame. All that has happened is that rules, types and defaults have fallen foul of the SQL Standard. The preferred way now is to use constraint to do the same thing, but it is a lot less elegant if you need to do it uniformly in several tables. Also, it is only people like Joe Celko who understand constraints in their entirety. Sure as anything I don't.

Alter default binding on a column

You cannot alter a default constraint - you need to drop the old one:

ALTER TABLE [BI011mylovetoys].[mylovetoys150806].[tblTest] 
DROP CONSTRAINT old_constraint 

and then add your new one:

ALTER TABLE [BI011mylovetoys].[mylovetoys150806].[tblTest] 
ADD CONSTRAINT test_constraint DEFAULT '10' FOR test 

How to set default value for column from another table in SQL Server 2005

Yes.

You can use a scalar UDF in a default constraint.

This will be RBAR ("Row By Agonizing Row") for multi row inserts however and a trigger might well be more efficient.

SQL Server 2005 Create Table with Column Default value range

You need to add CHECK Constraint to the column.

ALTER TABLE tblDepartment
ADD CONSTRAINT chkbuilding CHECK (Building >=1 AND Building <= 10 );

T-SQL - function with default parameters

you have to call it like this

SELECT dbo.CheckIfSFExists(23, default)

From Technet:

When a parameter of the function has a default value, the keyword
DEFAULT must be specified when the function is called in order to
retrieve the default value. This behaviour is different from using
parameters with default values in stored procedures in which omitting
the parameter also implies the default value. An exception to this
behaviour is when invoking a scalar function by using the EXECUTE
statement. When using EXECUTE, the DEFAULT keyword is not required.

Referencing another column in DEFAULT definition in SQL Server 2005

Here you go, I'm demonstrating this with an example schema since you've not provided your real table/column names.

Table:

CREATE TABLE test
  (
     id   INT NOT NULL PRIMARY KEY IDENTITY, --made up key
     col1 INT, --first column to add, wasn't sure if this was nullable or not
     col2 INT, --second column to add, wasn't sure if this was nullable or not
     col3 INT NOT NULL --this is the column to optionally insert into
  ) 

Here is the trigger definition:

CREATE TRIGGER demo
ON test
INSTEAD OF INSERT
AS
    INSERT INTO test (col1,col2,col3)
    SELECT inserted.col1,
           inserted.col2,
           CASE
             WHEN inserted.col3 IS NULL THEN COALESCE(inserted.col1, 0) + COALESCE(inserted.col2, 0)
             ELSE inserted.col3
           END
    FROM   inserted

Basically it replaces any insert statement done on the table with the one in the trigger, so I check using the inserted temporary table to see if the value that is trying to be inserted into our non-nullable optional column, col3, is NULL. If it is, I replace it with the addition of col1 and col2 (I'm coalescing with zero as you didn't mention if the two source columns are nullable or not).

You can then run insert statements which either include it or not, despite the fact col3 is not nullable:

INSERT INTO test(col1,col2)
SELECT 12, 31
GO

INSERT INTO test(col1, col2, col3)
SELECT 1, 2, 89
GO

Results are:

ID  COL1 COL2 COL3
------------------  
1   12   31    43
2   1    2     89

If the trigger wasn't there, you could have got an error trying to run that first insert statement, telling you it couldn't insert NULL into col3.

Notice also that the second insert statement that specifies a value has not been replaced by the addition, as requested.

Here's a working SQL Fiddle.

Cannot assign a default value to a local variable in SQL

Prior to SQL Server 2008, assigning a default value (or initial value) to a local variable is not allowed; otherwise this error message will be encountered.

Solution 1: (Use SET)

DECLARE @thresholdDate DATETIME 
set @thresholdDate = '2014-11-30'

For more details about the error : http://www.sql-server-helper.com/error-messages/msg-139.aspx

Solution 2: (Upgrade)

Another way of avoiding this error, which is a little bit a far-fetched solution, is to upgrade to SQL Server 2008. SQL Server 2008 now allows the assigning of a value to a variable in the DECLARE statement.

---

Related Topics