Click here to Skip to main content
15,890,282 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables data as Leave and LeaveTrans

Leave Table Data
ID Name
1 CL

LeaveTrans Table Data

LID ID Category MaxDays BalanceDays
1 1 1 10 15
2 1 2 11 11
3 1 3 2 4

I am trying to update the LeaveTrans Table on two conditions

1)trying to update the category,if category is already existed under ID,I am not going to update the value.Showing Errorpop message.It is working fine with this below stored procedure.

SQL
ALTER proc [dbo].[spCheckDuplicateRecordForUpdate]
(@ID int,@Category int,@Value int Out)
AS
BEGIN 
IF EXISTS(Select TOP 1 ID from LeaveTrans where ID=@ID AND Category=@Category)
set @Value = 2;
Else
SET @Value=0; 
END


2)Already existed LeaveTrans Data,want to update only MaxDays,BalanceDays.
which Could not get any idea to do these two in one stored procedure.

I want to do the two conditions,How can i achieve this any idea?
Posted
Updated 25-Oct-13 2:59am
v5
Comments
Prasad Khandekar 25-Oct-13 10:08am    
Hello,

How about altering your code as shown below.

ALTER proc [dbo].[spCheckDuplicateRecordForUpdate] (@ID int, @Category int, @maxDaya int, @balDays int, @Value int Out)
AS
BEGIN
IF EXISTS (SELECT TOP 1 ID FROM LeaveTrans WHERE ID = @ID AND Category = @Category)
BEGIN
SET @Value = 2;
UPDATE LeaveTrans SET MaxDays = @maxDays, BalanceDays = @balDays
WHERE ID = @ID AND Category = @Category;
END
ELSE
SET @Value = 0;
END


Regards,

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

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900