Sql procedure not working properly

Question

1.00/5 (1 vote)

See more:

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:

SQL

USE [AnsiSchoolDB2]
GO
/****** Object:  StoredProcedure [dbo].[GenFeePayment]    Script Date: 3/9/2019 11:22:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
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

Posted 2-Sep-19 22:43pm

nyt72

Updated 10-Sep-19 22:43pm

Add a Solution

Comments

OriginalGriff 3-Sep-19 4:47am

"it did not work" is one of the error reports we get quite often, and it's always completely useless - it tells us nothing about your problem other than "I have a problem" and we knew that because you are asking a question!

What did it do that you didn't expect, or not do that you did?
When did it do it?
Are there any error messages?
What did you do to make it do that?
What have you tried to do to find out why?
What were the results?
What help do you need?

These are all questions we need an answer to - or we can't help you at all!

phil.o 3-Sep-19 4:49am

What is the problem? Of course an update will overwrite.
What result are you expecting instead?

[no name] 3-Sep-19 4:56am

The Problem is in the Update part, it over write all records instead of update only which matched with ClassID and FeePartID.

Means Over write those too which are not matching.

Santosh kumar Pithani 3-Sep-19 5:46am

Hello nyt72, your simply updating all records without maintaining proper relation on derived and updated table, try to use merge for these type of task.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Answer 1 · 2019-09-10T22:44:00

How your query is incorrect?
you had written where clause but it is on temp table only (that is inside sub query) here, you should write where clause on table tblFeePayment as well.

So try this

SQL

;WITH temp AS
(
	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 
	WHERE 
		tblClass.ClassID =@cid AND tblFeeParts.FeePartID =@pid -----HERE IT IS KEY FILTER
)
UPDATE 
	FP
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
	[tblFeePayment] FP 
	INNER JOIN temp AS P -----HERE IT IS JOIN TO UPDATE PARTICULAR ROW IN tblFeePayment
	ON FP.ClassID = P.ClassID AND FP.FeePartID = P.FeePartID

Happy Coding!
:)

Santosh kumar Pithani · Answer 2 · 2019-09-02T23:56:00

SQL

;WITH CTE
AS (
	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
	WHERE   tblClass.ClassID = @cid
		AND tblFeeParts.FeePartID = @pid
	)

UPDATE [tblFeePayment]
SET [EnrollID] = CTE.EnrollID
	,[AdmissionNo] = CTE.AdmissionNo
	,[classfee] = CTE.FeeAmount
	,ClassID = CTE.ClassID
	,[catdiscount] = CTE.CatDiscountAmount
	,[studiscount] = CTE.StuDiscountAmount
	,FeePartID = CTE.FeePartID
	,[feeparticular] = CTE.particular
FROM [tblFeePayment]
INNER JOIN CTE ON (
		    [tblFeePayment].ClassID = CTE.ClassID
		AND [tblFeePayment].FeePartID = CTE.FeePartID
		)

Sql procedure not working properly

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0