Using the LIKE operator and "greater than" in a Case When expression

Question

This is for a section in a unit test that I'm writing.

I am trying to say pass if any row in a column contains a certain string. So in words, what I want is "if the number of row that contain astring is greater than zero than pass the test".

I have something like the code below, but it fails saying that myVariable needs to be declared. What am I doing wrong?

    DECLARE @myVariable BIT =
            (
                SELECT CASE
                           WHEN Count(Description) LIKE '%astring%' > 0
                                 THEN
                               1
                           ELSE
                               0
                       END
                FROM TABLE

SELECT @myVariable

Answer 1

I think you want:

DECLARE @myVariable BIT =
        (SELECT (CASE WHEN Count(*) > 0 THEN 1 ELSE 0 END)
         FROM TABLE
         WHERE Description LIKE '%astring%' 
        );

I wouldn't recommend a bit for this. SQL Server doesn't really support booleans. Integers (or tinyints even) are usually easier to work with than bits.