SQL: short-circuiting not working. "Null or empty full-text predicate" after upgrading to SQL Server 2012

Question

I have the following query in SQL Server 2005 which works fine:

DECLARE @venuename NVARCHAR(100)
DECLARE @town NVARCHAR(100)

SET @venuename =  NULL -- normally these are parameters in the stored proc.
SET @town = 'London'

SELECT COUNT(*) FROM dbo.Venue
WHERE 
    (@VenueName IS NULL OR CONTAINS((Venue.VenueName), @VenueName))
AND 
    (@Town IS NULL OR Town LIKE @Town + '%')

It uses short-circuiting when null values are passed for the parameters (there are many more in the real SP than shown in my example).

However after upgrading to SQL 2012, running this query with NULL passed for @VenueName fails with the error "Null or empty full-text predicate" as SQL Server seems to be running (or evaluating) the CONTAINS statement for @VenueName even when @VenueName is set to NULL.

Is there a way to use short-circuiting in 2012 or is this no longer possible? I'd hate to have to rewrite all of my SPs as we've used this technique in dozens of stored procedures across multiple projects over the years.

Answer 1

I do not know much about sql 2012 but can you please try following

DECLARE @venuename NVARCHAR(100)  
DECLARE @town NVARCHAR(100)

SET @venuename =  '""' -- -- **Yes '""' instead of null**.
SET @town = 'London'

SELECT COUNT(*) FROM dbo.Venue
WHERE 
    (@VenueName ='""' OR CONTAINS((Venue.VenueName), @VenueName))
AND 
    (@Town IS NULL OR Town LIKE @Town + '%')

Answer 2

Check out this thread: OR Operator Short-circuit in SQL Server Within SQL server, there is no guarantee that an OR clause breaks early. It's always been that way, so I guess you've just been lucky that it worked with SQL Server 2005.

To workaround your problem, consider using the ISNULL function every time you supply a parameter value that might be NULL, to the CONTAINS function.