使SQL状态有效且易于理解

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 dont know if it is possible.

必须只有1个插入和1个更新,这是我老板说的,但我不知道是否可能。

5 个解决方案

#1

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

#2

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.

您可以在insert和update中使用case语句。并不是说它会更容易理解,而是会满足老板的愿望。

#3

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

#4

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())

在查询中,您可以不必更新列,即@result_var = @expected_value和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

#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

这似乎是正确的答案

#1

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

#2