How do I do multiple CASE WHEN conditions using SQL Server 18 for filtering results?-CodePudding

I'm trying to filter the results based on the status of two parameters from the user. These parameters are TaxCode and CompanyName. Now I will tell you the rules and the query I am trying to write.

Rules:

1- If the name field is empty and only the taxcode field is full, only the columns with that taxcode should appear.

2- If the Taxcode field is empty and only the name field is full, only columns similar to that name should appear.

3- Finally, if both are filled, I want to combine the two expressions with or and I want them both to be common.

When TaxCode(IdNoPartNumber) is entered, I use this query to reach another table and reach its id(Uid):

select p.Uid FROM PartyIdentificationNumber as pin 
                join Party as p 
                on pin.Uid = p.Uid 
                where p.EI = 'E' and pin.IdNoPartNumber = @TaxCode

When searching for a name:

LastName LIKE @CompanyName

I don't know much about sql and the code I wrote started to get longer and confused me. Thank you from now. I hope that has been revealing:

SELECT * 
FROM dbo.Party 
WHERE Uid = CASE WHEN LastName IS NULL 
        THEN ( 
            select p.Uid FROM PartyIdentificationNumber as pin 
            join Party as p 
            on pin.Uid = p.Uid 
            where p.EI = 'E' and pin.IdNoPartNumber = @TaxCode) 

            END

        OR

        LastName LIKE  @CompanyName

CodePudding user response：

I don't believe you need a CASE statement and think you could possibly do something like this with IF STATEMENTS:

DECLARE @TaxCode varchar(10) = ''
DECLARE @CompanyName varchar(20) = ''

IF (ISNULL(LTRIM(RTRIM(@CompanyName)),'') = '')
    AND (ISNULL(LTRIM(RTRIM(@TaxCode)),'') <> '')
BEGIN
    SELECT p.Uid 
    FROM PartyIdentificationNumber as pin 
        join Party as p on pin.Uid = p.Uid 
    WHERE p.EI = 'E' and pin.IdNoPartNumber = @TaxCode
END
ELSE IF (ISNULL(LTRIM(RTRIM(@TaxCode)),'') = '')
    AND (ISNULL(LTRIM(RTRIM(@CompanyName)),'') <> '')
BEGIN
    SELECT p.Uid 
    FROM PartyIdentificationNumber as pin 
        join Party as p on pin.Uid = p.Uid 
    WHERE LastName LIKE '%'   LTRIM(RTRIM(@CompanyName))   '%'
END
ELSE IF (ISNULL(LTRIM(RTRIM(@TaxCode)),'') <> '')
    AND (ISNULL(LTRIM(RTRIM(@CompanyName)),'') <> '')
BEGIN
    PRINT 'REPLACE THIS WITH SELECT'
END

I am only guessing the SELECTS you need based on what you've written.

If you need to use CASE, then the basic format for that is as follows:

SELECT
    CASE 
        WHEN (ISNULL(LTRIM(RTRIM(@CompanyName)),'') = '')
            AND (ISNULL(LTRIM(RTRIM(@TaxCode)),'') <> '') THEN 'DO THIS 1'
        WHEN (ISNULL(LTRIM(RTRIM(@TaxCode)),'') = '')
            AND (ISNULL(LTRIM(RTRIM(@CompanyName)),'') <> '') THEN 'DO THIS 2'
        WHEN (ISNULL(LTRIM(RTRIM(@TaxCode)),'') <> '')
            AND (ISNULL(LTRIM(RTRIM(@CompanyName)),'') <> '') THEN 'DO THIS 3'
        ELSE 
            'DO THIS 4'
    END
FROM [TABLE_NAME]