SQL : WHERE 절 내의 IF 절

SQL : WHERE 절 내의 IF 절

---

MS SQL 의 WHERE 절 내에서 IF 절 을 사용할 수 있습니까?

예:

WHERE
    IF IsNumeric(@OrderNumber) = 1
        OrderNumber = @OrderNumber
    ELSE
        OrderNumber LIKE '%' + @OrderNumber + '%'

---

CASE 문 사용
UPDATE : 이전 구문 (몇 사람이 지적한대로)이 작동하지 않습니다. 다음과 같이 CASE를 사용할 수 있습니다.

WHERE OrderNumber LIKE
  CASE WHEN IsNumeric(@OrderNumber) = 1 THEN 
    @OrderNumber 
  ELSE
    '%' + @OrderNumber
  END

또는 @ NJ Reed 와 같은 IF 문을 사용할 수 있습니다 .

---

IF 또는 CASE 없이이 작업을 수행 할 수 있어야합니다

 WHERE 
   (IsNumeric(@OrderNumber) AND
      (CAST OrderNumber AS VARCHAR) = (CAST @OrderNumber AS VARCHAR)
 OR
   (NOT IsNumeric(@OrderNumber) AND
       OrderNumber LIKE ('%' + @OrderNumber))

SQL의 특징에 따라 내재 된 캐스트가 지원되는지 여부에 따라 주문 번호의 캐스트를 INT 또는 VARCHAR로 조정해야 할 수도 있습니다.

이것은 WHERE 절에서 매우 일반적인 기술입니다. WHERE 절에 "IF"논리를 적용하려면 부울 AND가있는 추가 조건을 적용해야하는 섹션에 추가하면됩니다.

---

IF 문이 전혀 필요하지 않습니다.

WHERE
    (IsNumeric(@OrderNumber) = 1 AND OrderNumber = @OrderNumber)
OR (IsNumeric(@OrderNumber) = 0 AND OrderNumber LIKE '%' + @OrderNumber + '%')

---

SQL에서 이것을 수행하는 좋은 방법은 없습니다. 내가 본 몇 가지 접근법 :

1) 부울 연산자와 결합 된 CASE를 사용하십시오.

WHERE
    OrderNumber = CASE 
        WHEN (IsNumeric(@OrderNumber) = 1)
        THEN CONVERT(INT, @OrderNumber)
        ELSE -9999 -- Some numeric value that just cannot exist in the column
    END
    OR 
    FirstName LIKE CASE
        WHEN (IsNumeric(@OrderNumber) = 0)
        THEN '%' + @OrderNumber
        ELSE ''
    END

2) SELECT 외부에서 IF 사용

IF (IsNumeric(@OrderNumber)) = 1
BEGIN
    SELECT * FROM Table
    WHERE @OrderNumber = OrderNumber
END ELSE BEGIN
    SELECT * FROM Table
    WHERE OrderNumber LIKE '%' + @OrderNumber
END

3) 긴 문자열을 사용하여 조건부로 SQL 문을 작성한 다음 EXEC를 사용하십시오.

세 번째 접근 방식은 끔찍하지만, 이와 같은 다양한 변수 조건이있는 경우에는 이것이 유일한 방법이라고 생각합니다.

---

IF 대신 CASE 문을 사용하십시오 .

---

CASE 문을 원합니다

WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END

---

where ... like / = ... case ... then ... 부울과 함께 작동 할 수 있다고 생각합니다. T-SQL을 사용하고 있습니다.

Scenario: Let's say you want to get Person-30's hobbies if bool is false, and Person-42's hobbies if bool is true. (According to some, hobby-lookups comprise over 90% of business computation cycles, so pay close attn.).

CREATE PROCEDURE sp_Case
@bool   bit
AS
SELECT Person.Hobbies
FROM Person
WHERE Person.ID = 
    case @bool 
        when 0 
            then 30
        when 1
            then 42
    end;

---

WHERE (IsNumeric(@OrderNumber) <> 1 OR OrderNumber = @OrderNumber) 
             AND (IsNumber(@OrderNumber) = 1 OR OrderNumber LIKE '%' 
                                              + @OrderNumber + '%')

---

The following example executes a query as part of the Boolean expression and then executes slightly different statement blocks based on the result of the Boolean expression. Each statement block starts with BEGIN and completes with END.

USE AdventureWorks2012;
GO
DECLARE @AvgWeight decimal(8,2), @BikeCount int
IF 
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
BEGIN
   SET @BikeCount = 
        (SELECT COUNT(*) 
         FROM Production.Product 
         WHERE Name LIKE 'Touring-3000%');
   SET @AvgWeight = 
        (SELECT AVG(Weight) 
         FROM Production.Product 
         WHERE Name LIKE 'Touring-3000%');
   PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.'
   PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.';
END
ELSE 
BEGIN
SET @AvgWeight = 
        (SELECT AVG(Weight)
         FROM Production.Product 
         WHERE Name LIKE 'Touring-3000%' );
   PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ;
END ;
GO

Using nested IF...ELSE statements The following example shows how an IF … ELSE statement can be nested inside another. Set the @Number variable to 5, 50, and 500 to test each statement.

DECLARE @Number int
SET @Number = 50
IF @Number > 100
   PRINT 'The number is large.'
ELSE 
   BEGIN
      IF @Number < 10
      PRINT 'The number is small'
   ELSE
      PRINT 'The number is medium'
   END ;
GO

---

CASE Statement is better option than IF always.

  WHERE  vfl.CreatedDate >= CASE WHEN @FromDate IS NULL THEN vfl.CreatedDate ELSE  @FromDate END
    AND vfl.CreatedDate<=CASE WHEN @ToDate IS NULL THEN vfl.CreatedDate ELSE @ToDate END 

---

    WHERE OrderNumber LIKE CASE WHEN IsNumeric(@OrderNumber) = 1 THEN @OrderNumber ELSE  '%' + @OrderNumber END

In line case Condition will work properly.

---

If @LstTransDt is Null
                begin
                    Set @OpenQty=0
                end
            else
                begin
                   Select   @OpenQty=IsNull(Sum(ClosingQty),0)  
                   From  ProductAndDepotWiseMonitoring  
                   Where   Pcd=@PCd And PtpCd=@PTpCd And TransDt=@LstTransDt      
                end 

See if this helps.

---

USE AdventureWorks2012;
GO
IF 
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
PRINT 'There are more than 5 Touring-3000 bicycles.'
ELSE PRINT 'There are 5 or less Touring-3000 bicycles.' ;
GO

참고URL : https://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause