CREATE FUNCTION dbo.GetPaymentStatus
(
@PaymentMethodID int,
@ChequeNo varchar(20)
)
RETURNS bit
AS
BEGIN 
DECLARE @Ret bit

SELECT @Ret=CASE WHEN Description ='cheque' AND @ChequeNo IS NULL THEN 0
ELSE 1 END
FROM PaymentMethods
WHERE PaymentMethodID = @PaymentMethodID 

RETURN(@Ret)
END


then create check constraint based on it
ALTER TABLE Payments ADD CONSTRAINT Chk_PaymentMethod CHECK (dbo.GetPaymentStatus(PaymentMenthodID,ChequeNo)=1)

quote:

---

Originally posted by aidmondo

When I execute the function, i get the error message 'cannot insert null into the column ChequeNo and if i allow ChequeNo accepts null values, the ChequeNo column accept all values even if description in not Cheque.


aidmondo

---

-- Create Tables

CREATE TABLE PaymentMethod
(
	PaymentMethodID INT IDENTITY(1, 1) PRIMARY KEY,
	Description VARCHAR(20) NOT NULL
)


CREATE TABLE Payment
(
	PaymentID INT IDENTITY(1, 1) PRIMARY KEY,
	PaymentMenthodID INT NOT NULL CONSTRAINT cfkPay FOREIGN KEY (PaymentMenthodID) REFERENCES PaymentMethod (PaymentMethodID),
	ChequeNo varchar(20) not null 
)

ALTER TABLE Payment
ADD CONSTRAINT CK_Payment_ChequeNo CHECK ((CASE WHEN PaymentMenthodID = 1 AND LEN(ChequeNo) > 0 THEN 1 WHEN PaymentMenthodID <> 1 AND LEN(ChequeNo) = 0 THEN 1 ELSE 0 END) = 1)

-- Popualte Domain Data

SET IDENTITY_INSERT PaymentMethod ON
INSERT PaymentMethod (PaymentMethodID, Description)
VALUES
(1, 'Cheque'),
(2, 'Cash'),
(3, 'Credit Card')
SET IDENTITY_INSERT PaymentMethod ON


-- Try Inserting

INSERT Payment (PaymentMenthodID, ChequeNo) VALUES (1, NULL) -- Fail
INSERT Payment (PaymentMenthodID, ChequeNo) VALUES (1, '') -- Fail
INSERT Payment (PaymentMenthodID, ChequeNo) VALUES (1, '12345') -- Success
INSERT Payment (PaymentMenthodID, ChequeNo) VALUES (2, '12345') -- Fail
INSERT Payment (PaymentMenthodID, ChequeNo) VALUES (2, '') -- Success

-- Drop tables
DROP TABLE Payment
DROP TABLE PaymentMethod