I've got a query that I am building to have an application. The database is setup in SQL Server 2008. I wish to make use of a query much like below, however, I'll be by using this 'Where' clause for around 4 other posts utilizing the same needs. Is the right method to test for null or '' inside a column that's VarChar(255) and does allow nulls.

Ideally, when the variable @UutSerialNumber is null or empty (''), I would like all of the results, but when it's not, I wish to make use of the 'LIKE' clause. Is the right way to do this and can it work? It appears to operate until I start adding more posts towards the Where clause.

Also, wouldso would I handle a "text" datatype utilizing the same kind of query?

SELECT DeviceName, UutStatus FROM MyTable WHERE (UutSerialNumber LIKE '%' + @UutSerialNumber + '%' OR UutSerialNumber LIKE '%%' AND (@UutSerialNumber = '' OR @UutSerialNumber IS NULL)) AND ...

Assistance is appreciated. Thanks everybody!

It amy appear like duplication of SQL but the easiest method to do that is when it comes to performace is applying IF ... ELSE

IF ISNULL(@UutSerialNumber, '') = '' 
    BEGIN
        SELECT  DeviceName, UutStatus 
        FROM    MyTable 
        -- MORE EXPRESSIONS
    END
ELSE 
    BEGIN
        SELECT  DeviceName, UutStatus 
        FROM    MyTable 
        WHERE   (UutSerialNumber LIKE '%' + @UutSerialNumber + '%' 
        -- MORE EXPRESSIONS
    END

It is possible inside the WHERE clause if you're doing the work on multiple posts and also the query you published wasn't remote it had been just missing additional parenthesis together with getting a redundant clause.

SELECT  DeviceName, UutStatus 
FROM    MyTable 
WHERE   (ISNULL(@UutSerialNumber, '') = '' OR UutSerialNumber LIKE '%' + @UutSerialNumber + '%')
AND     (ISNULL(@AnotherParameter, '') = '' OR AnotherColumn LIKE '%' + @AnotherParameter + '%')

Convert the written text type to VARCHAR(MAX).

like a footnote, Personally, i would make use of the CHARINDEX instead of concatenating strings within the like:

WHERE   (ISNULL(@UutSerialNumber, '') = '' OR CHARINDEX(@UutSerialNumber, UutSerialNumber) > 0)

This really is simply a footnote however when i did no performance testing, I simply believe it is simpler around the eye!

SELECT DeviceName, UutStatus 
FROM MyTable 
WHERE ((@UutSerialNumber = '') OR (@UutSerialNumber is null)) OR (UutSerialNumber like @UutSerialNumber)

add '%' towards the last @UutSerialNumber if you feel you'll need