title: how to use a parameterized function for the Default Binding of a Sql Server column
body: <p>I have a table that catalogs selected files from multiple sources. I want to record whether a file is a duplicate of a previously cataloged file at the time the new file is cataloged.  I have a column in my table (“primary_duplicate”) to record each entry as ‘P’ (primary) or ‘D’ (duplicate).  I would like to provide a Default Binding for this column that would check for other occurrences of this file (i.e. name, length, timestamp) <strong>at the time the new file is being recorded.</strong> </p>
  
  <p>I have created a function that performs this check (see “GetPrimaryDuplicate” below). But I don’t know how to bind this function which requires three parameters to the table’s “primary_duplicate” column as its Default Binding.</p>
  
  <p>I would like to avoid using a trigger.  I currently have a stored procedure used to insert new records that performs this check. But I would like to ensure that the flag is set correctly if an insert is performed outside of this stored procedure.</p>
  
  <p>How can I call this function with values from the row that is being inserted?</p>
  
  <pre><code>USE [MyDatabase]
  GO
  
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[FileCatalog](
      [id] [uniqueidentifier] NOT NULL,
      [catalog_timestamp] [datetime] NOT NULL,
      [primary_duplicate] [nchar](1) NOT NULL,
      [name] [nvarchar](255) NULL,
      [length] [bigint] NULL,
      [timestamp] [datetime] NULL
  ) ON [PRIMARY]
  
  GO
  
  ALTER TABLE [dbo].[FileCatalog] ADD  CONSTRAINT [DF_FileCatalog_id]  DEFAULT (newid()) FOR [id]
  GO
  
  ALTER TABLE [dbo].[FileCatalog] ADD  CONSTRAINT [DF_FileCatalog_catalog_timestamp]  DEFAULT (getdate()) FOR [catalog_timestamp]
  GO
  
  ALTER TABLE [dbo].[FileCatalog] ADD  CONSTRAINT [DF_FileCatalog_primary_duplicate]  DEFAULT (N'GetPrimaryDuplicate(name, length, timestamp)') FOR [primary_duplicate]
  GO
  
  
  USE [MyDatabase]
  GO
  
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE FUNCTION [dbo].[GetPrimaryDuplicate] 
  (
      @name nvarchar(255),
      @length bigint,
      @timestamp datetime
  )
  RETURNS nchar(1)
  AS 
  BEGIN
  
      DECLARE @c int
  
      SELECT @c = COUNT(*)
      FROM FileCatalog
      WHERE name=@name and length=@length and timestamp=@timestamp and primary_duplicate = 'P'
  
      IF @c &gt; 0
          RETURN 'D' -- Duplicate
  
      RETURN 'P' -- Primary
  
  END
  
  GO
  </code></pre>