1) StoreProc_ 1
DECLARE @IsOpenPoint BIT= 0; -- if 1 Execute SP
DECLARE @IsSuccess INT= 0;
IF EXISTS(SELECT Column1 FROM [dbo].[Table1] WHERE [dbo].[Table1].[Column1] = @Id)
BEGIN
IF EXISTS(SELECT Column1 FROM [dbo].[Table2] WHERE [dbo].[Table1].[Column1] = @Id And [dbo].[Table1].[Column1] = 0)
BEGIN
-- update record
SET @IsOpenPoint = 1;-- if 1 Execute SP
END
ELSE
BEGIN
IF NOT EXISTS(SELECT Column1 FROM [dbo].[Table2] WHERE [dbo].[Table1].[Column1] = @Id And [dbo].[Table1].[Column1] = 1)
BEGIN
-- insert record
SET @IsOpenPoint = 1;-- if 1 Execute SP
END
ELSE
BEGIN
SET @IsSuccess = 3;--user not register
END;
END;
END
ELSE
BEGIN
SET @IsSuccess = 2;--user not register
END;
IF(@IsOpenPoint = 1)
BEGIN
DECLARE @Coins INT= 1000;
DECLARE @ActionType INT= 1;
EXEC [dbo].[StoreProc_2] @Value1, @Value2, @Value3;
SET @IsSuccess = 1; --success
END;
2) StoreProc_2
DECLARE @IsInsert BIT = 0;
IF(@ActionType = 1)
BEGIN
DECLARE @Level1Coins INT = 180;--90;
DECLARE @Level2Coins INT = 96;--48;
DECLARE @Level_1_ID BIGINT;
DECLARE @Level_2_ID BIGINT;
SELECT
@Level_1_ID = Level1,
@Level_2_ID = Level2
FROM
Table1
WHERE
Id = @Id
IF(@Level_1_ID > 0 AND @Level_1_ID IS NOT NULL) -- This is for First Level
BEGIN -- Update Level_1
Update Table2 SET col1 = col1 + ISNULL(@Level1Coins,0) WHERE Id = @Level_1_ID
END
IF(@Level_2_ID > 0 AND @Level_2_ID IS NOT NULL) -- This is for Second Level
BEGIN -- Update Level_2
Update Table2 SET col1 = col1 + ISNULL(@Level2Coins,0) WHERE Id = @Level_2_ID
END
SET @IsInsert = 1;
END
IF (@ActionType = 2)
BEGIN
SET @IsInsert = 1;
END
IF(@IsInsert = 1)
BEGIN
UPDATE Table2 SET col1 = col1 + ISNULL(@Value1,0) WHERE Id = @Id;
INSERT INTO InsertTables
(Value1,Value2,Value3)
VALUES
(@Value1,ISNULL(@Value2,0),@Value3)
END
i have two Stored procedures.
StoreProc_1 check data exists or not. if exists i set IsOpenPoint value to 1 for execute StoreProc_2
i don't add any PKS in InsertTable for StoreProc_2
StoreProc_2 update record basis on ids ids is PK For table when record updated. if IsInsert value is 1. so insert record in InsertTable as record
problem is some time first Stored execute second Stored procedures multiple time.
i am verifying InsertTable every same record insert in minimum 10 sec diff and also id's value was updates.
it's PK Issue or someone calling SP again?
if someone calling SP how to find them?
Comments
Post a Comment