How to use CASE in SQL to set multiple WHERE conditions?-CodePudding

The Case is returning syntax error while trying to set more than one condition to the query. The Store Procedure takes a param @isAdmin which I use later to select different data based on User Type.

Below is the query of the Store Procedure

SELECT orU.userName as userOrder,orf.OrderID
FROM App.orderF orf
Inner Join app.Users orU
On orf.LastUpdatedBy = orU.UserID
   
Where
   -- ADDING THE CASE HERE
    Case @isAdmn
        WHEN 0 THEN LastUpdatedBy = @UserID AND SiteDataID=@SiteDataID 
        WHEN 1 THEN SiteDataID=@SiteDataID

-- ORDER BY IS NOT IN SIDE THE CASE
ORDER BY LastUpdatedDate DESC

The above query says there is an error near =

EDIT: Actual Query

SELECT orU.userName as userOrder,orf.OrderID, orf.OrderNumber, orf.TotalAmount, Convert(VARCHAR,orf.OrderDate,106) as OrderDate, orf.OrderStatus,orf.LastUpdatedBy as UserID
        FROM App.OrderFinal orf
        Inner Join app.Users orU
        On orf.LastUpdatedBy = orU.UserID
         
        Where
        Case @isAdmn
        WHEN 0 THEN LastUpdatedBy = @UserID AND SiteDataID=@SiteDataID 
        WHEN 1 THEN SiteDataID=@SiteDataID 

        ORDER BY LastUpdatedDate DESC

CodePudding user response：

You are missing the END for the CASE expression:

WHERE
  CASE @isAdmn
    WHEN 0 THEN LastUpdatedBy = @UserID AND SiteDataID = @SiteDataID 
    WHEN 1 THEN SiteDataID = @SiteDataID
  END

Update: You have changed your tags from MySQL to SQL Server. SQL Server SQL doesn't have a boolean data type, so the CASE expression result cannot be a boolean. SiteDataID = @SiteDataID is a boolean value (true or false), and so is LastUpdatedBy = @UserID AND SiteDataID = @SiteDataID. This does not work in SQL Server.

But usually you would not have a CASE expression in WHERE anyhow, because with CASE you create a boolean expression check, which the WHERE clause already is:

WHERE SiteDataID = @SiteDataID AND (@isAdmn = 1 OR LastUpdatedBy = @UserID)

CodePudding user response：

CASE does not control which lines of code run in this context. It returns a value. You need to use that value. You also need an END on it.

SELECT orU.userName as userOrder,orf.OrderID, orf.OrderNumber, orf.TotalAmount, Convert(VARCHAR,orf.OrderDate,106) as OrderDate, orf.OrderStatus,orf.LastUpdatedBy as UserID
        FROM App.OrderFinal orf
        Inner Join app.Users orU
        On orf.LastUpdatedBy = orU.UserID
         
        WHERE
            LastUpdatedBy = 
                CASE @isAdmn
                    WHEN 0 THEN @UserID
                    WHEN 1 THEN LastUpdatedBy 
                END
            AND SiteDataID = @SiteDataID
        ORDER BY LastUpdatedDate DESC

Currently, your cases are doing the same thing, but that is what your code says and I'm sure not what your intent is. At any rate, this is how you use CASE in a WHERE clause.

Update: Your various screenshots and code aren't doing the same thing and I can't tell what your actual desired outcome is.

Update2: Code updated to reflect the clarified requirement.