Using the MS-SQL Server Stored Procedure sp_bindefault to Bind a User-Created Default to a Table Column

As mentioned in Tip 49, "Using the Transact-SQL CREATE DEFAULT Statement to Set a Column Default," you must bind defaults to table columns so the DBMS knows which columns it is supposed to set to which default values. The syntax to use when executing the stored procedure sp_bindefault to bind a default value to a table column is

 EXEC sp_bindefault
 @DEFNAME=,
 @OBJNAME=.
where  is the name you gave the default in  the CREATE DEFAULT statement, and . is the  name column in the table for which you want the DBMS to supply the default  value.
 
For example, if you executed the Transact-SQL CREATE DEFAULT  statements
 
 CREATE DEFAULT ud_value_unknown AS "Unknown"
CREATE DEFAULT ud_applied_for AS "Applied for"
 
the DBMS would store the default values UD_VALUE_UNKNOWN and  UD_APPLIED_FOR in the database system tables. Once it's created, you can use the  stored procedure sp_bindefault to bind the defaults to columns in tables (such  as the EMPLOYEE table defined by example in Tip 49).
 
To bind the default ud_value_unknown ("Unknown") to the  SOCIAL_SECURITY_NUMBER column in the EMPLOYEE table, execute the Transact-SQL  statement:
 
 EXEC sp_bindefault
 @defname=ud_value_unknown,
 @objname='employee.[social_security_number]'
 
To bind the default ud_applied_for ("Applied For") to the  SHERIFF_CARD_NUMBER column in the EMPLOYEE table, execute the Transact-SQL  statement:
 
 EXEC sp_bindefault
 @defname=ud_applied_for,
 @objname='employee.[sheriff_card_number]'
 
To bind the default ud_applied_for ("Applied For") to the  HEALTH_CARD_NUMBER column in the EMPLOYEE table, execute the Transact-SQL  statement:
 
 EXEC sp_bindefault
 @defname=ud_applied_for,
 @objname='employee.[health_card_number]'
 
After you bind defaults to the EMPLOYEE table columns, the DBMS  will supply the default value for the default-bound columns when you execute an  INSERT statement on the EMPLOYEE table, such as:
 
 INSERT INTO employee (employee_ID, first_name, last_name)
 VALUES (1, 'Konrad', 'King')
 
In the current example, the DBMS will supply "Unknown" for  SOCIAL_SECURITY_NUMBER, "Applied For" for SHERIFF_CARD_NUMBER and  HEALTH_CARD_NUMBER, and NULL for STREET_ADDRESS.