CODEWITHSUNDEEP
sqlsql-server
Nils Ozols
Nils Ozols

Reputation: 5

Making SQL statement efficient and easy to understand

I have problem that I have to make my code shorter . code:

IF @result_var = @expected_value

    BEGIN
      INSERT INTO reports.consistencycheckhistory VALUES
                  (
                              Getdate(),
                              @rule_guid,
                              'Ok',
                              @result_var
                  )
      IF CONVERT(DATE,@check_time) <> CONVERT(DATE, Sysdatetime())
      BEGIN
        UPDATE reports.consistencycheckrules
        SET    ok_days_count =@ok_days_count + 1 ,
                  last_check_time=@check_time
        where  rule_guid=@rule_guid
      END
    END
    ELSE
    BEGIN
      INSERT INTO reports.consistencycheckhistory VALUES
                  (
                              Getdate(),
                              @rule_guid,
                              'Error',
                              @result_var
                  )
      UPDATE reports.consistencycheckrules
      SET    ok_days_count=0,
             last_check_time=@check_time
      WHERE  rule_guid=@rule_guid
    END

There have to be only 1 insert and 1 update that is what my boss is saying but I don't know if it is possible.

Upvotes: 0

Views: 83

Answers (5)

Nils Ozols
Nils Ozols

Reputation: 5

     IF @result_var = @expected_value
BEGIN
  SET @Status = 'Ok'
END
ELSE
BEGIN
  SET @Status = 'Error'
END
IF @Status = 'Ok'
BEGIN
  IF CONVERT(DATE, @check_time) <> CONVERT(DATE, Sysdatetime())
  BEGIN
    SET @ok_days_count = @ok_days_count + 1;
  END
  ELSE
  BEGIN
    @ok_days_count=@ok_days_count
  END
end
ELSE
BEGIN
  SET @ok_days_count = 0;
end
INSERT INTO reports.consistencycheckhistory VALUES
            (
                        Getdate(),
                        @rule_guid,
                        @Status,
                        @result_var
            )
UPDATE reports.consistencycheckrules
SET    ok_days_count = @ok_days_count,
       last_check_time = @check_time
WHERE  rule_guid = @rule_guid

This seems like the right answer

Upvotes: 0

Sagar Shirke
Sagar Shirke

Reputation: 686

In the query there is case where you do not have to update columns, i.e. @result_var = @expected_value and CONVERT(DATE,@check_time) = CONVERT(DATE, Sysdatetime()) 

DECLARE @Should_Update bit
SET @Should_Update=0

IF @result_var = @expected_value
BEGIN
    SET @Status = 'Ok'
END
ELSE
BEGIN
    SET @Status = 'Error'
END



    IF(@result_var = @expected_value)
    BEGIN
        IF CONVERT(DATE,@check_time) <> CONVERT(DATE, Sysdatetime())
        BEGIN
                SET @Should_Update=1
                SET @ok_days_count = @ok_days_count + 1;

        END     
    END
    ELSE
    BEGIN
        SET @Should_Update=1
        SET @ok_days_count = 0;
    END



INSERT INTO reports.consistencycheckhistory VALUES
(
          Getdate(),
          @rule_guid,
          @Status,
          @result_var
)

UPDATE reports.consistencycheckrules
SET    ok_days_count = @ok_days_count ,
          last_check_time=@check_time
where  rule_guid=@rule_guid AND @Should_Update=1

Upvotes: 0

user4650542
user4650542

Reputation:

Try this (might need a bit of tweaking):

INSERT INTO reports.consistencycheckhistory 
    VALUES 
    ( 
    Getdate(), 
    @rule_guid, 
    CASE 
        WHEN @result_var = @expected_value 
            then 'Ok'
        ELSE 'Error"
    END, 
    @result_var 
    )
UPDATE reports.consistencycheckrules 
SET 
    ok_days_count =@ok_days_count +
    CASE 
        WHEN CONVERT(DATE,@check_time) <> CONVERT(DATE,Sysdatetime())
            then 1
        ELSE
                0
        END,
    last_check_time=@check_time 
    where rule_guid=@rule_guid

Upvotes: 0

sv_vs
sv_vs

Reputation: 112

IF @result_var = @expected_value
BEGIN
    SET @Status = 'Ok'
END
ELSE
BEGIN
    SET @Status = 'Error'
END

IF CONVERT(DATE,@check_time) <> CONVERT(DATE, Sysdatetime())
BEGIN
    SET @ok_days_count = @ok_days_count + 1;
END
ELSE
BEGIN
    SET @ok_days_count = 0;
END


INSERT INTO reports.consistencycheckhistory VALUES
(
          Getdate(),
          @rule_guid,
          @Status,
          @result_var
)

UPDATE reports.consistencycheckrules
SET    ok_days_count = @ok_days_count ,
          last_check_time=@check_time
where  rule_guid=@rule_guid

Upvotes: 1

Tomasz Kuryło
Tomasz Kuryło

Reputation: 122

You could use case statements within insert and within update. Not that it would make it much easier to understand, but would fulfill your boss' wish.

Upvotes: 0

Related Questions