Multiple columns matching in where clause

The way I would approach is is by first creating a table with all your possible combinations of the 4 values. This can be done by creating a derived table with 4 rows, then joining it to itself 4 times, with each join ensuring that you are not picking a row that has already been picked, i.e.

DECLARE @Values TABLE
(
    Col1 VARCHAR(20),
    Col2 VARCHAR(20),
    Col3 VARCHAR(20),
    Col4 VARCHAR(20)
    UNIQUE (Col1, Col2, Col3, Col4)
);

WITH Data AS
(   SELECT  Value, v.Ordinal
    FROM    (VALUES (1, @Col1), (2, @Col2), (3, @Col3), (4, @Col4)) AS v (Ordinal, Value)
)
INSERT @Values(Col1, Col2, Col3, Col4)
SELECT  DISTINCT d1.Value, d2.Value, d3.Value, d4.Value
FROM    Data AS d1
        INNER JOIN Data AS d2
            ON d2.Ordinal NOT IN (d1.Ordinal)
        INNER JOIN Data AS d3
            ON d3.Ordinal NOT IN (d1.Ordinal, d2.Ordinal)
        INNER JOIN Data AS d4
            ON d4.Ordinal NOT IN (d1.Ordinal, D2.Ordinal, d3.Ordinal);

With a really simple example where @Col1 = 'A', and all others are NULL you'll end up with something like:

You can then use INTERSECT to check against your table. The benefit of this is that INTERSECT does NULL matching, i.e. SELECT NULL INTERSECT SELECT NULL will return a record, whereas SELECT NULL WHERE NULL = NULL will not.

IF EXISTS
    (   SELECT  Col1, Col2, Col3, Col4
        FROM    SampleData
        INTERSECT
        SELECT  Col1, Col2, Col3, Col4
        FROM    @Values
    )
BEGIN
    SELECT 'Data Matched';
END
ELSE
BEGIN
    SELECT 'Data not Matched';
END

The main advantage of this method (aside from returning your expected results) is that it can take advantage of an index on SampleData, so if you add:

CREATE INDEX IX_SampleData ON dbo.SampleData (Col1, Col2, Col3, Col4);

Then this will be used when using INTERSECT, but isn't with your logic as it stands:

With the small data set as it stands this doesn't make much difference, but with much larger data sets it will do. The above is with 200k rows in SampleData.

So your full procedure will be something like:

CREATE OR ALTER PROC dbo.ValidateSampleData(
 @Col1 VARCHAR(20) = NULL
,@Col2 VARCHAR(20) = NULL
,@Col3 VARCHAR(20) = NULL
,@Col4 VARCHAR(20) = NULL
)
AS
BEGIN

    DECLARE @Values TABLE
    (
        Col1 VARCHAR(20),
        Col2 VARCHAR(20),
        Col3 VARCHAR(20),
        Col4 VARCHAR(20)
        UNIQUE (Col1, Col2, Col3, Col4)
    );

    WITH Data AS
    (   SELECT  Value, v.Ordinal
        FROM    (VALUES (1, @Col1), (2, @Col2), (3, @Col3), (4, @Col4)) AS v (Ordinal, Value)
    )
    INSERT @Values(Col1, Col2, Col3, Col4)
    SELECT  DISTINCT d1.Value, d2.Value, d3.Value, d4.Value
    FROM    Data AS d1
            INNER JOIN Data AS d2
                ON d2.Ordinal NOT IN (d1.Ordinal)
            INNER JOIN Data AS d3
                ON d3.Ordinal NOT IN (d1.Ordinal, d2.Ordinal)
            INNER JOIN Data AS d4
                ON d4.Ordinal NOT IN (d1.Ordinal, D2.Ordinal, d3.Ordinal);

    IF EXISTS
        (   SELECT  Col1, Col2, Col3, Col4
            FROM    SampleData
            INTERSECT
            SELECT  Col1, Col2, Col3, Col4
            FROM    @Values
        )
    BEGIN
        SELECT 'Data Matched';
    END
    ELSE
    BEGIN
        SELECT 'Data not Matched';
    END
END 

ADDEDNUM

To answer the question in the comment:

I got this warning while creating Index on actual table. Can you explain ? Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IX_SampleData' has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail.

Presumably your actual columns are larger than specified in the question (VARCHAR(20))? As the warning says, the maximum key length is 1700bytes., so the required storage for all 4 columns must be less than that. Each VARCHAR character requires one byte of storage, so the maximum for VARCHAR(20) is 20 bytes, for 4 columns your maximum is 80 bytes. If however your columns are VARCHAR(500) then your maximum size is 2000 bytes. Since VARCHAR is variable, each value will only take as much space as needed. So if you enter A into a column defined as VARCHAR(500) it will still only take ` byte.

Assuming that your columns are VARCHAR(500) and you try and add the following:

INSERT INTO SampleData(Col1, Col2, Col3, Col4)
SELECT REPLICATE('A', 500), REPLICATE('B', 500), REPLICATE('C', 500), REPLICATE('D', 500)

In summary, if you are going to use all 500 characters in all 4 columns, then your insert will fail. If you have been very liberal with your column length, and the total length in all 4 columns is always going to be less than 1700 characters, then you will be fine (although, if you have been liberal with your column length, then perhaps resize them accordingly - although there is no cost in terms of additional storage there are other costs with defining columns larger than you need, so best to be as accurate as possible with column lengths).