question

Marius_van_Dyk avatar image
Marius_van_Dyk asked

UDF function and check constraint for date range per User and Category not working

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.
sqlsql 2012udf
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.