Bdtjtk

I've just started to learn SQL and would be very thankful if you can help with SELECT query. There are input variables: @p_IPN and @p_SecondName, the table has SECONDNAME, FIRSTNAME, MIDDLENAME and IPN columns.

I need to perform search in my table by the specified input characters with the following conditions:

• if none of variables is specified, the query should return the entire table;




• if @p_IPN OR @p_SecondName is specified, the query should perform search by IPN LIKE @p_IPN OR SECONDNAME LIKE @p_SecondName;




• if both variables are specified, the query should return rows with input characters of @p_IPN AND @p_SecondName (IPN LIKE @p_IPN AND SECONDNAME LIKE @p_SecondName)

I used the following query:

IF (@p_IPN IS NULL AND @p_SecondName IS NULL)

    SELECT 

        CAST(IPN AS NVARCHAR(MAX)) AS IPN, FIRSTNAME, SECONDNAME, MIDDLENAME 

    FROM

        dbo.BE_BlackList

ELSE IF (@p_IPN IS NULL)

    SELECT 

        CAST(IPN AS NVARCHAR(MAX)) AS IPN, FIRSTNAME, SECONDNAME, MIDDLENAME    

    FROM

        dbo.BE_BlackList

    WHERE 

        SECONDNAME LIKE @p_SecondName

ELSE IF (@p_SecondName IS NULL)

    SELECT 

        CAST(IPN AS NVARCHAR(MAX)) AS IPN, FIRSTNAME, SECONDNAME, MIDDLENAME    

    FROM

        dbo.BE_BlackList

    WHERE 

        IPN LIKE @p_IPN

ELSE

    SELECT 

        CAST(IPN AS NVARCHAR(MAX)) AS IPN, FIRSTNAME, SECONDNAME, MIDDLENAME    

    FROM

        dbo.BE_BlackList

    WHERE 

        IPN LIKE @p_IPN AND SECONDNAME LIKE @p_SecondName 

It works well, still, I need to have the same result using SELECT query.

I tried this:

    SELECT CASE WHEN @p_IPN IS NULL AND @p_Secondname IS NULL THEN

            (SELECT cast (IPN AS nvarchar(max)) AS IPN, FIRSTNAME, MIDDLENAME, SECONDNAME FROM dbo.BE_BlackList)



               WHEN @p_SecondName IS NULL THEN

            (SELECT cast(IPN AS nvarchar(max)) AS IPN, FIRSTNAME, MIDDLENAME, SECONDNAME FROM dbo.BE_BlackList

             WHERE IPN LIKE @p_IPN)



               WHEN @p_IPN IS NULL THEN

            (SELECT cast(IPN AS nvarchar(max)) AS IPN, FIRSTNAME, MIDDLENAME, SECONDNAME FROM dbo.BE_BlackList

             WHERE SECONDNAME LIKE @p_Secondname)



         ELSE 

            (SELECT cast (IPN AS nvarchar(max)) AS IPN, FIRSTNAME, MIDDLENAME, SECONDNAME FROM dbo.BE_BlackList

             WHERE SECONDNAME LIKE @p_Secondname AND IPN LIKE @p_IPN)

         END

FROM dbo.BE_BlackList

I get this error:

Msg 116, Level 16, State 1, Line 10

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I am not sure how to use CASE WHEN EXISTS here (if possible at all). Could you help, please?

Instead of using IF ELSE, you could use WHERE for your condition filter

SELECT 

    CAST(IPN AS NVARCHAR(MAX)) AS IPN, FIRSTNAME, SECONDNAME, MIDDLENAME 

FROM

    dbo.BE_BlackList

WHERE 

   (@p_IPN IS NULL OR IPN LIKE @p_IPN)

   AND

   (@p_SecondName IS NULL OR SECONDNAME LIKE @p_SecondName)

The issue is with your CASE WHEN syntax. Think of CASE WHEN as a column. In other words, SELECT CASE WHEN a = b, THEN c FROM dbo.BE_BlackList

You are saying SELECT CASE WHEN a = b then SELECT c from dbo.BE_Blacklist - Notice there are two select statements here.