Assistance with SQL Server SELECT query

Question

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?

Answer 1

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)

Answer 2

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.