how to use a parameterized function for the Default Binding of a Sql Server column
- by Walt Gaber
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) at the time the new file is being recorded.
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.
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.
How can I call this function with values from the row that is being inserted?
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 NOT NULL,
[name] nvarchar 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 > 0
RETURN 'D' -- Duplicate
RETURN 'P' -- Primary
END
GO
