t sql“选择案例"与“如果……否则"以及关于“开始"的解释 [英] t sql "select case" vs "if ... else" and explaination about "begin"

问题描述

我对 t sql 的经验很少，我必须编写一个存储.

I have few experiences with t sql and I have to write a stored.

这是我存储的:

USE myDatabase
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[myStored]
(
@myPar1 INT,
@myPar2 SMALLDATETIME
)

AS

BEGIN
  SET NOCOUNT ON

  IF EXISTS (
            SELECT 
            1 

            FROM 
            myTable1 

            WHERE 
            myPar1 = @myPar1
            AND myPar2 = @myPar2
            )

    BEGIN
      DELETE FROM  
      myTable1  

      WHERE 
      myPar1 = @myPar1
      AND myPar2 = @myPar2
    END

  ELSE 

    IF EXISTS (
              SELECT 
              1 

              FROM 
              myTable2 

              WHERE 
              myPar2 = @myPar2
              )

      BEGIN
        INSERT INTO  
        myTable1
        (myField1, myField2, myField3, myField4)

        VALUES
        (@myPar1, @myPar2, '', 1)
      END    

    ELSE

      IF EXISTS (
                SELECT 
                1 

                FROM 
                myTable3 

                WHERE 
                myPar2 = @myPar2
                )

        BEGIN
          INSERT INTO  
          myTable1
          (myField1, myField2, myField3, myField4)

          VALUES
          (@myPar1, @myPar2, '', 1)
        END
END

这些是我的问题:

1 - 是否存在宏观错误?

1 - Are there macroscopic errors?

2 - 有人建议使用 "SELECT CASE" 其他人使用 "IF ... ELSE"，有什么区别?我存储的最佳选择是什么?

2 - Someone suggest to use "SELECT CASE" someone else to use "IF ... ELSE", what's the difference? And what is the best option for my stored?

3 - 我不确定BEGIN ... END"语句的使用，特别是与IF ... ELSE"语句结合使用.这是什么意思?是否有必要将BEGIN ... END"放在IF ... ELSE"语句中?也用于执行单个指令?

3 - I'm not sure about the use of the "BEGIN ... END" statement, in particular in combination with "IF ... ELSE" statement. What does it mean? Is it necessary to put "BEGIN ... END" inside the "IF ... ELSE" statement? Also for executing a single instruction?

推荐答案

对于单个 IF 语句

IF (Some Condition)     --<-- If condition is true control will get inside the 
 BEGIN                     -- BEGIN ..END Block and execute the Code inisde
   /* Your Code Here*/
 END

所有单个 IF 语句都将独立检查条件.

All the single IF statements will check the Conditions Independently.

一个 IF 和一个 ELSE

IF (Some Condition)     --<-- If condition is true control will get inside the 
 BEGIN                     -- BEGIN ..END Block and execute the Code inisde
   /* Your Code Here*/     -- IF not true control will jump to Else block
 END
ELSE       --<-- You dont mention any condition here
 BEGIN                    
   /* Your Code Here*/
 END

只有一个代码块会执行 IF 真，然后是第一块其他代码块.

Only One block of code will execute IF true then 1st block Otherwsie ELSE block of code.

多个 IF 和 ELSE

IF (Some Condition)     --<--1) If condition is true control will get inside the 
  BEGIN                     -- BEGIN ..END Block and execute the Code inisde
    /* Your Code Here*/     -- IF not true control will check next ELSE IF Blocl
  END
ELSE IF (Some Condition)       --<--2) This Condition will be checked
  BEGIN                    
      /* Your Code Here*/
  END
ELSE IF (Some Condition)       --<--3) This Condition will be checked
  BEGIN                    
     /* Your Code Here*/
  END
ELSE                --<-- No condition is given here Executes if non of
  BEGIN                --the previous IFs were true just like a Default value   
     /* Your Code Here*/
  END

只有第一个代码块会被执行，如果条件为真，其余的将被忽略.

Only the very 1st block of code will be executed WHERE IF Condition is true rest will be ignored.

BEGIN ..END 块

在任何 IF、ELSE IF 或 ELSE 之后，如果您要执行多个语句，则必须将它们包装在 BEGIN..END 块中.如果您只执行一个语句，则没有必要，但始终使用 BEGIN END 块是一个好习惯，这样可以更轻松地阅读您的代码.

After any IF, ELSE IF or ELSE if you are Executing more then one Statement you MUST wrap them in a BEGIN..END block. not necessary if you are executing only one statement BUT it is a good practice to always use BEGIN END block makes easier to read your code.

您的程序

我已经取出了 ELSE 语句，使每个 IF 语句都独立地检查给定的条件，现在您对如何处理 IF 和 ELSE 有了一些想法，所以请自己尝试一下，因为我不知道您要在这里应用什么逻辑.

I have taken out ELSE statements to make every IF Statement check the given Conditions Independently now you have some Idea how to deal with IF and ELSEs so give it a go yourself as I dont know exactly what logic you are trying to apply here.

CREATE PROCEDURE [dbo].[myStored]
(
@myPar1 INT,
@myPar2 SMALLDATETIME
)

AS

BEGIN
  SET NOCOUNT ON

  IF EXISTS (SELECT 1 FROM myTable1 WHERE myPar1 = @myPar1
            AND myPar2 = @myPar2)
    BEGIN
      DELETE FROM  myTable1  
      WHERE myPar1 = @myPar1 AND myPar2 = @myPar2
    END


    IF EXISTS (SELECT 1 FROM myTable2 WHERE myPar2 = @myPar2)
      BEGIN
         INSERT INTO  myTable1(myField1, myField2, myField3, myField4)
         VALUES(@myPar1, @myPar2, '', 1)
      END    


      IF EXISTS (SELECT 1 FROM myTable3  WHERE myPar2 = @myPar2)
        BEGIN
           INSERT INTO  myTable1(myField1, myField2, myField3, myField4)
           VALUES(@myPar1, @myPar2, '', 1)
        END
END

这篇关于t sql“选择案例"与“如果……否则"以及关于“开始"的解释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！