1

I created a UDF that I am using to generate a default value for a column. It works great, but I want to pass another field as a parameter into the function. Is this possible?

For example, one of the fields is a DealerID field, and I want to pass in the value of the DealerID field into my UDF because I will use it to calculate the new value. Any help would be appreciated!

Mike Cole
  • 14,474
  • 28
  • 114
  • 194

3 Answers3

3

I had a similar issue where I wanted to automatically assign a URL slug for new records inserted to the table. The approach I took was to set the field's default value to 'NOTSET' (just a text placeholder value) then used an insert trigger to update the field ON INSERT to the value of my UDF (where the field value is NOTSET) as follows:

CREATE TRIGGER [dbo].[TR_MyTable_MyTriggerName] 
   ON  [dbo].[tblMyTable]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Create the geography field from the lat and lon coordinates
    UPDATE tblMyTable
    SET fldURLSlug = dbo.UDF_MyFunction(INS.[fldRecordTitle])
    FROM tblMyTable MT INNER JOIN inserted INS ON MT.fldRecordId = INS.fldRecordId
    WHERE INS.fldURLSlug = 'NOTSET'

END

GO
Starjumper Tech SL
  • 417
  • 5
  • 10
3

No, because the default value will be needed before DealerID is known (eg on INSERT)

Edit:

This means that SQL Server does not the value in the table at the time of insert, only after. Therefore, it can not a UDF for the default.

For example, what about a multiple row insert, or where you have NEWID() default?

Now, using logic basic on DealerID: if it's GUID, why? It's an internal, non-user readable value.

If you really need this, you'll have to use a computed column for the "base" value and another column for the "actual" value with ISNULL.

gbn
  • 422,506
  • 82
  • 585
  • 676
0

Please correct me if you have a specific reason why you need to use a UDF, but why not just define the default value for the column in your table DDL, which will then be overwritten if you supply a specific value in your UPDATE, INSERT etc.? Using a UDF in a SELECT will cause the function to be executed every row, an overhead you will save if it is taken care of at the table definition level.

davek
  • 22,499
  • 9
  • 75
  • 95
  • I am trying to do a formula to calculate the default value, which is why I am trying to use a UDF. – Mike Cole Nov 16 '09 at 22:51
  • 1
    A good reason might be in multi-tenant databases where the default you want to use is specific to the Business/Customer that owns the record being inserted.e.g.: When inserting a new Document record you might want to assign the "Unassigned" Document Group ID to it. With the current constraints the middle tier has to lookup the "Unassigned" Document Group ID itself and include it in the INSERT statement's values. – AlwaysLearning Jan 14 '15 at 01:25