Hello,
I am using the below procedure to Insert data in a table if record not exists else update, but the Insert is working fine and the update over right the records, I don't know where I am doing wrong.
What I have tried:
USE [AnsiSchoolDB2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[GenFeePayment]
@cid int,
@pid int
As
BEGIN
IF NOT EXISTS (SELECT * FROM tblFeePayment WHERE tblFeePayment.ClassID =@cid AND tblFeePayment.FeePartID =@pid)
BEGIN
INSERT INTO [dbo].[tblFeePayment]
([enrollID] ,[AdmissionNo] ,[classfee] ,ClassID ,[catdiscount],[studiscount] ,FeePartID ,[feeparticular]
)
SELECT t.EnrollID,t.AdmissionNo, t.FeeAmount,t.ClassID,t.CatDiscountAmount,t.StuDiscountAmount,t.FeePartID,t.particular
FROM
(SELECT DiSTINCT tblEnrollment.EnrollID,tblStudent.AdmissionNo, tblStuCategory.CategoryID,tblStuCategory.Category,tblClass.ClassID,tblClass.ClassName,tblFeeParts.FeePartID,tblFeeParts.particular, tblClassFee.FeeAmount,
tblCatDiscount.Discount AS CatDiscount, tblCatDiscount.is_Percent AS CatPercent,
ISNULL((CASE WHEN tblCatDiscount.is_Percent = 1 THEN (tblClassFee.FeeAmount * tblCatDiscount.Discount) / 100
ELSE tblCatDiscount.Discount END), 0)
AS CatDiscountAmount,
tblStuDiscount.Discount AS StuDiscount,
tblStuDiscount.is_percent AS StuPercent,
ISNULL((CASE WHEN tblStuDiscount.is_percent = 1 THEN (tblClassFee.FeeAmount * tblStuDiscount.Discount)
/ 100 ELSE tblStuDiscount.Discount END), 0) AS StuDiscountAmount
FROM [tblEnrollment]
INNER JOIN tblClassFee ON tblClassFee.ClassID = tblEnrollment.ClassID
INNER JOIN tblStudent ON tblStudent.StudentID = tblEnrollment.StudentID
INNER JOIN tblStuCategory ON tblStuCategory.CategoryID = tblEnrollment.CategoryID
INNER JOIN tblClass ON tblClass.ClassID = tblClassFee.ClassID
INNER JOIN tblFeeParts ON tblFeeParts.FeePartID = tblClassFee.FeePartID
INNER JOIN tblCatDiscount ON tblEnrollment.CategoryID = tblCatDiscount.CategoryID
LEFT JOIN tblStuDiscount ON tblStuDiscount.EnrollID = tblEnrollment.EnrollID AND tblStuDiscount.FeeTypeID = tblFeeParts.FeeTypeID ) t
WHERE ClassID =@cid AND FeePartID =@pid
END
ELSE
BEGIN
UPDATE [tblFeePayment]
SET [EnrollID]= p.EnrollID ,[AdmissionNo] =p.AdmissionNo,[classfee]=p.FeeAmount
,ClassID=p.ClassID ,[catdiscount]=p.CatDiscountAmount,[studiscount]=p.StuDiscountAmount ,FeePartID=p.FeePartID ,
[feeparticular]=p.particular
FROM
(SELECT DiSTINCT tblEnrollment.EnrollID,tblStudent.AdmissionNo, tblStuCategory.CategoryID,tblStuCategory.Category,tblClass.ClassID,tblClass.ClassName,tblFeeParts.FeePartID,tblFeeParts.particular, tblClassFee.FeeAmount,
tblCatDiscount.Discount AS CatDiscount, tblCatDiscount.is_Percent AS CatPercent,ISNULL((CASE WHEN tblCatDiscount.is_Percent = 1 THEN (tblClassFee.FeeAmount * tblCatDiscount.Discount) / 100 ELSE tblCatDiscount.Discount END), 0)
AS CatDiscountAmount,
tblStuDiscount.Discount AS StuDiscount, tblStuDiscount.is_percent AS StuPercent, ISNULL((CASE WHEN tblStuDiscount.is_percent = 1 THEN (tblClassFee.FeeAmount * tblStuDiscount.Discount)
/ 100 ELSE tblStuDiscount.Discount END), 0) AS StuDiscountAmount
FROM [tblEnrollment]
INNER JOIN tblClassFee ON tblClassFee.ClassID = tblEnrollment.ClassID
INNER JOIN tblStudent ON tblStudent.StudentID = tblEnrollment.StudentID
INNER JOIN tblStuCategory ON tblStuCategory.CategoryID = tblEnrollment.CategoryID
INNER JOIN tblClass ON tblClass.ClassID = tblClassFee.ClassID
INNER JOIN tblFeeParts ON tblFeeParts.FeePartID = tblClassFee.FeePartID
INNER JOIN tblCatDiscount ON tblEnrollment.CategoryID = tblCatDiscount.CategoryID
LEFT JOIN tblStuDiscount ON tblStuDiscount.EnrollID = tblEnrollment.EnrollID AND tblStuDiscount.FeeTypeID = tblFeeParts.FeeTypeID ) p
WHERE p.ClassID =@cid AND p.FeePartID =@pid
END
END