Donate. I desperately need donations to survive due to my health

Get paid by answering surveys Click here

Click here to donate

Remote/Work from Home jobs

Stored procedures Execute Multiple time

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