16 декабря 2005 г. 20:16
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]()
BEGINDECLARE @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
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?