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.