'Why can't the SQL contains predicate be shortcut using an is null
I get the error Null or empty full-text predicate. for the query
declare @searchTerm varchar(50) = null
select *
from Products
where @searchTerm is null or contains(Details, @searchTerm)
Why is this? I was hoping the is null condition will shortcut the contains.
This is how the table is prepared
create table Products (Id int identity(1,1), Details varchar(500))
alter table Products add constraint PK_Products primary key clustered (Id asc)
create fulltext catalog ProductsFTS
create fulltext index on Products (Details) key index PK_Products on ProductsFTS
And the SQL Server version - Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 19044: )
Worth noting that I managed to work around the issue, following the answer here - https://stackoverflow.com/a/3411391/1355646
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
