how to update table with multiple if..else statetemt

Question

i have stored procedure that InsertNewFlag this stored procedure works to check if specified condition record exist that upadte table with multiple (If..Else If) and if not exist that table insert query executed.

here i include this stored procedure code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertNewFlag]
(
    @IsRead bit = NULL,
    @IsImportant bit = NULL,
    @IsTrashed bit = NULL,
    @IsRemoved bit = NULL,
    @User_id int,
    @Post_History_id int,
    @NewID int output
)
AS
BEGIN
    IF EXISTS(SELECT * FROM [FileSystem].[dbo].[tbl_Post_History_Status] WHERE(User_id=@User_id AND Post_History_id=@Post_History_id))
    BEGIN
     IF @IsRead <> NULL
        BEGIN
        UPDATE [FileSystem].[dbo].[tbl_Post_History_Status]
        SET [IsRead] = @IsRead
            ,[User_id] = @User_id
            ,[Post_History_id] = @Post_History_id
        WHERE (User_id=@User_id AND Post_History_id=@Post_History_id)
        SET @NewID = CAST(SCOPE_IDENTITY() AS INT)
        END
    ELSE IF @IsImportant <> NULL
        BEGIN
        UPDATE [FileSystem].[dbo].[tbl_Post_History_Status]
        SET [IsImportant] = @IsImportant
            ,[User_id] = @User_id
            ,[Post_History_id] = @Post_History_id
        WHERE (User_id=@User_id AND Post_History_id=@Post_History_id)
        SET @NewID = CAST(SCOPE_IDENTITY() AS INT)
        END
    ELSE IF @IsTrashed <> NULL
        BEGIN
        UPDATE [FileSystem].[dbo].[tbl_Post_History_Status]
        SET [IsTrashed] = @IsTrashed
            ,[User_id] = @User_id
            ,[Post_History_id] = @Post_History_id
        WHERE (User_id=@User_id AND Post_History_id=@Post_History_id)
        SET @NewID = CAST(SCOPE_IDENTITY() AS INT)
        END
    ELSE IF @IsRemoved <> NULL
        BEGIN
        UPDATE [FileSystem].[dbo].[tbl_Post_History_Status]
        SET [IsRemoved] = @IsRemoved
            ,[User_id] = @User_id
            ,[Post_History_id] = @Post_History_id
        WHERE (User_id=@User_id AND Post_History_id=@Post_History_id)
        SET @NewID = CAST(SCOPE_IDENTITY() AS INT)
        END
    END
    ELSE
        BEGIN
            INSERT INTO [FileSystem].[dbo].[tbl_Post_History_Status]
               ([IsRead]
               ,[IsImportant]
               ,[IsTrashed]
               ,[IsRemoved]
               ,[User_id]
               ,[Post_History_id])
            VALUES
               (@IsRead,@IsImportant,@IsTrashed,@IsRemoved,@User_id,@Post_History_id)
            SET @NewID = CAST(SCOPE_IDENTITY() AS INT)
        END
END

how ever this stored procedure are syntactically right and show message like "Command(s) completed successfully." but when i execute this stored procedure and pass value @IsRead to True and @User_id to 1 and @Post_History_id to 9 then record doesn't updated if exist.

how ever record exist.

please help me..

Answer 1

Try this,

 UPDATE [FileSystem].[dbo].[tbl_Post_History_Status]
SET
     [IsRead] =(CASE WHEN NOT @IsRead IS NULL THEN @IsRead ELSE [IsRead] END),
     [IsImportant] =(CASE WHEN NOT @IsImportant IS NULL THEN @IsImportant ELSE [IsImportant] END),
     [IsTrashed] =(CASE WHEN NOT @IsTrashed IS NULL THEN @IsTrashed ELSE [IsTrashed] END),
     [IsRemoved] =(CASE WHEN NOT @IsRemoved IS NULL THEN @IsRemoved ELSE [IsRemoved] END),
     [User_id] = @User_id,
     [Post_History_id] =@Post_History_id
WHERE (User_id=@User_id AND Post_History_id=@Post_History_id)

Answer 2

TRY THIS

ALTER PROCEDURE [dbo].[InsertNewFlag]
(
    @IsRead bit = NULL,
    @IsImportant bit = NULL,
    @IsTrashed bit = NULL,
    @IsRemoved bit = NULL,
    @User_id int,
    @Post_History_id int,
    @NewID int output
)
AS
BEGIN

    Declare @Cnt Int
    SET @Cnt = 0

    SELECT Top 1 @Cnt = ID FROM [FileSystem].[dbo].[tbl_Post_History_Status] WHERE(User_id=@User_id AND Post_History_id=@Post_History_id) 

    IF @Cnt > 0
    BEGIN

     SET @NewID = @Cnt

     IF Not @IsRead Is Null
        BEGIN
        UPDATE [FileSystem].[dbo].[tbl_Post_History_Status]
        SET [IsRead] = @IsRead
            ,[User_id] = @User_id
            ,[Post_History_id] = @Post_History_id
        WHERE (User_id=@User_id AND Post_History_id=@Post_History_id)
        --SET @NewID = CAST(SCOPE_IDENTITY() AS INT)
        END
    ELSE IF Not @IsImportant Is Null
        BEGIN
        UPDATE [FileSystem].[dbo].[tbl_Post_History_Status]
        SET [IsImportant] = @IsImportant
            ,[User_id] = @User_id
            ,[Post_History_id] = @Post_History_id
        WHERE (User_id=@User_id AND Post_History_id=@Post_History_id)
        --SET @NewID = CAST(SCOPE_IDENTITY() AS INT)
        END
    ELSE IF Not @IsTrashed Is Null
        BEGIN
        UPDATE [FileSystem].[dbo].[tbl_Post_History_Status]
        SET [IsTrashed] = @IsTrashed
            ,[User_id] = @User_id
            ,[Post_History_id] = @Post_History_id
        WHERE (User_id=@User_id AND Post_History_id=@Post_History_id)
        --SET @NewID = CAST(SCOPE_IDENTITY() AS INT)
        END
    ELSE IF Not @IsRemoved Is Null
        BEGIN
        UPDATE [FileSystem].[dbo].[tbl_Post_History_Status]
        SET [IsRemoved] = @IsRemoved
            ,[User_id] = @User_id
            ,[Post_History_id] = @Post_History_id
        WHERE (User_id=@User_id AND Post_History_id=@Post_History_id)
        --SET @NewID = CAST(SCOPE_IDENTITY() AS INT)
        END
    END
    ELSE
        BEGIN
            INSERT INTO [FileSystem].[dbo].[tbl_Post_History_Status]
               ([IsRead]
               ,[IsImportant]
               ,[IsTrashed]
               ,[IsRemoved]
               ,[User_id]
               ,[Post_History_id])
            VALUES
               (@IsRead,@IsImportant,@IsTrashed,@IsRemoved,@User_id,@Post_History_id)
            --SET @NewID = CAST(SCOPE_IDENTITY() AS INT)
            SET @NewID = @@IDENTITY
        END
END

Answer 3

Replace all null checking to like this

IF Not @IsRead Is Null --Instead of IF @IsRead <> NULL
BEGIN
  .....Other CODE
END

ELSE IF Not @IsImportant  Is Null --Instead of ELSE IF @IsImportant <> NULL
BEGIN
 .....and SO ON