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

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.

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.