How can I Use a User Defined function as a default for a column value?

Question

Hi, I have an int column for which I want to set to a certain code value, e.g.:  In my table "Biometric" I have a column called "BiometricStatusCode" which is a foreign key to a Table/Column  "REF_PERSON_BiometricStatusCodes:BiometricStatusID"

In that REF_PERSON_BiometricStatusCodes table, I have a row of data that returns a value of "All Others", the PK id for this row happens to be 3. 

I can enter 3 as my default for the column in my original "Biometric" table...that will work fine and return "All Others" when joined...as long as the PK for "All Others" is 3.  However, I cannot guarantee that in customer installations, hence I want to use a function that returns the Int/PK code for the row of data that contains "All Others" in my  REF_PERSON_BiometricStatusCodes table. 

So..........I created this function:

 

Create FUNCTION [dbo].[ufn_SelectDefaultBiometricStatusCode]()

RETURNS int

AS

BEGIN

DECLARE @ret int;

SELECT @ret = [BiometricStatusID]

FROM [AFR].[dbo].[REF_PERSON_BiometricStatusCodes] where

Upper([BiometricStatusDescr]) = 'ALL OTHERS';

IF (@ret IS NULL)

SET @ret = 0

RETURN @ret

END;

The function works fine, it's not full proof...but is ok.

 

When I enter the function name, no quotes in the "Default Value or Binding" property in the table designer in sql 2005, it returns an error:  "error validating the default for column".

 

The column is an int, the function returns an int...    I can't figure it out..

 

Anyone have any ideas?

 

thanks.

Answer 1

You can use a computed column.

If you use SQL Server Management Studio to modify the table - use the column properties tab - Computed Column Specification - Formula.