Reputation: 5832
I am coming from MySQL now working in SQL Server, writing a stored procedure that needs to set a value in the "like" clause based on the value in the Description column. Not sure if I should declare a variable (@desc) and then set it somehow of if there is an easier way?
Pseudo-code:
DECLARE @desc varchar(255);
IF [Description] LIKE '%KK%'
SET @desc = 'KDues';
ELSE
SET @desc = 'BDues';
The actual query
SELECT
Description,AmountDue
[...]
WHERE order_date BETWEEN @fiscalYearBeginDate AND @fiscalYearEndDate
AND [Description] LIKE '%'+@desc+'%'
What's the best way to approach this? NOt sure how to use IF/ELSE or CASE in this particular scenario. Thanks
Upvotes: 0
Views: 129
Reputation:
SELECT
case when Description like '%KK%'
then 'KDues'
ELSE
'BDues' end as Description ,AmountDue
[...]
WHERE order_date BETWEEN @fiscalYearBeginDate AND @fiscalYearEndDate
Upvotes: 2
Reputation: 1271231
You can use a case
statement:
SELECT Description, AmountDue
[...]
WHERE order_date BETWEEN @fiscalYearBeginDate AND @fiscalYearEndDate and
Description like (case when Description LIKE '%KK%' then '%KDues%' else '%BDues%' end)
However, that is ugly. You can eliminate the case
using logic:
WHERE order_date BETWEEN @fiscalYearBeginDate AND @fiscalYearEndDate and
((Description like '%KK%' and Description like '%KDues%') or
(Description not like '%KK%' and Description like '%BDues%')
)
By the way, both of these forms are the same in MySQL and SQL Server.
Upvotes: 2