question

I've got table which contains User, Category, MonthFrom and MonthTo columns. I've written a UDF which looks like this: USE [pbSol_GL] GO /****** Object: UserDefinedFunction [dbo].[ValidatezIncentivesDates] Script Date: 05/11/2017 07:33:58 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[ValidatezIncentivesDates] ( @Users nvarchar(100), @Category nvarchar(100), @MonthFrom datetime, @MonthTo datetime ) returns bit as begin declare @Valid bit = 1; if exists( select Users,Category,MonthFrom,MonthTo from dbo.z_Incentives z where -- z.ID = @ID ltrim(rtrim(z.Users)) = ltrim(rtrim(@Users)) and ltrim(rtrim(z.Category)) = ltrim(rtrim(@Category)) and @MonthFrom <= z.MonthTo and z.MonthFrom <= @MonthTo) set @Valid = 0; return @Valid; end The check constraint I create on the SQL table looks like this: alter table z_Incentives with nocheck add constraint CK_PromotionCampaign_ValidateDates check (dbo.ValidatezIncentivesDates(Users,Category, MonthFrom, MonthTo) = 1); For some reason, the check constraint does not work as it should. I've tried casting and different tweaks on the UDF, but no luck thus far. The alter table looks as follows: USE [pbSol_GL] GO /****** Object: Table [dbo].[z_Incentives] Script Date: 06/11/2017 02:14:07 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[z_Incentives]( [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [Users] [char](10) NOT NULL, [Category] [varchar](100) NULL, [MonthFrom] [datetime] NULL, [MonthTo] [datetime] NULL, [TargetMonth] [int] NULL, [IncentivePercentage] [decimal](4, 2) NULL, CONSTRAINT [PK_z_Incentives] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[z_Incentives] WITH NOCHECK ADD CONSTRAINT [CK_PromotionCampaign_ValidateDates] CHECK (([dbo].[ValidatezIncentivesDates]([Users],[Category],[MonthFrom],[MonthTo])=(1))) GO ALTER TABLE [dbo].[z_Incentives] CHECK CONSTRAINT [CK_PromotionCampaign_ValidateDates] GO Any help will be appreciated.