CODEWITHSUNDEEP
t-sqlsql-functioncheck-constraints
Hassan Faghihi
Hassan Faghihi

Reputation: 2031

Check Constraint Which Uses A Custom Function

I want to let user to enter, only one entry of data with specific ReqNo, that it's Type is equal 'SS'. There are two issue:

  1. I Receive following error:

Msg 4145, Level 15, State 1, Line 21 An expression of non-boolean type specified in a context where a condition is expected, near ')'.

  1. This code should be normalized for update too though myself won't do update through my application, but maybe the future DB Admin which operate my application data, do something at some point.

List ENDS ( I add this line so the parser will start parsing the code, which otherwise it wouldn't )

CREATE FUNCTION dbo.ReqNoIsUniquePerReserve
(
    @ReqNo AS BIGINT,
    @Type AS CHAR
)
RETURNS BIT
AS
BEGIN
    IF UPPER(@Type) = 'SS'
    BEGIN
        IF EXISTS (SELECT TOP 1 Id FROM dbo.LiaQueue WHERE @ReqNo = ReqNo)
            RETURN 0; 
        ELSE
            RETURN 1;
    END
    ELSE IF @ReqNo IS NOT NULL
    BEGIN
        IF EXISTS (SELECT TOP 1 ID FROM dbo.LiaQueue WHERE @ReqNo = ReqNo)
            RETURN 1;
        ELSE
            RETURN 0;
    END
    RETURN 1;
END

ALTER TABLE dbo.LiaQueue 
ADD CONSTRAINT CK_LiaQueue_ReqNo_Unique 
CHECK (dbo.[ReqNoIsUniquePerReserve](ReqNo,[Type]))

UPDATE

USE [Liaison]
GO
/****** Object:  UserDefinedFunction [dbo].[ReqNoIsUniquePerReserve]    Script Date: 10/03/2016 12:48:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ReqNoIsUniquePerReserve]
(
    @Id AS BIGINT,
    @Command AS NVARCHAR,
    @ReqNo AS BIGINT
)
RETURNS BIT
AS
BEGIN
    IF UPPER(@Command) = 'SS'
    BEGIN
        IF EXISTS (SELECT TOP 1 Id FROM dbo.LiaQueue WHERE @ReqNo = ReqNo AND Id <> @Id)
            RETURN 0; 
        ELSE
            RETURN 1;
    END
    ELSE IF @ReqNo IS NOT NULL
    BEGIN
        IF EXISTS (SELECT TOP 1 ID FROM dbo.LiaQueue WHERE @ReqNo = ReqNo)
            RETURN 1;
        ELSE
            RETURN 0;
    END
    RETURN 1;
END



ALTER TABLE dbo.LiaQueue 
ADD CONSTRAINT CK_LiaQueue_ReqNo_Unique 
CHECK (dbo.[ReqNoIsUniquePerReserve](Id, Command, ReqNo) = 1)

Upvotes: 0

Views: 1196

Answers (1)

DVT
DVT

Reputation: 3127

For 1)

Have you tried:

ALTER TABLE dbo.LiaQueue 
ADD CONSTRAINT CK_LiaQueue_ReqNo_Unique 
CHECK (dbo.[ReqNoIsUniquePerReserve](ReqNo,[Type]) = 1)

For 2), I'm not so sure what you mean.

Upvotes: 1

Related Questions