Case Statement in SQL using Like
You can also do like this. I think this will work.
select *
from table
where columnName like '%' + case when @varColumn is null then '' else @varColumn end + ' %'
For one, I would update your SQL to this so you are using a JOIN
on your SELECT
statement instead of placing this in a WHERE
clause.
INSERT INTO Foundation.TaxLiability.EmpowerSystemCalendarCode
SELECT SystemTax.SystemTaxID,
EmpowerCalendarCode.CalendarCodeID
,CASE WHEN EmpowerCalendarCode.CalendarName LIKE '%Monthly%' THEN 3
WHEN EmpowerCalendarCode.CalendarName LIKE '%Annual%' THEN 2
WHEN EmpowerCalendarCode.CalendarName LIKE '%Quarterly%' THEN 4
ELSE 0
END
FROM Foundation.Common.SystemTax SystemTax
INNER JOIN Foundation.TaxLiability.EmpowerCalendarCode EmpowerCalendarCode
ON SystemTax.EmpowerTaxCode = EmpowerCalendarCode.LongAgencyCode
AND SystemTax.EmpowerTaxType = EmpowerCalendarCode.EmpowerTaxType
two, what happens if you remove the INSERT INTO
?