CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Slinky
Slinky

Reputation: 5832

Using a Conditional to populate a variable SQL Server

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

Answers (2)

user2724280
user2724280

Reputation: 

SELECT
case when Description like '%KK%'
then 'KDues'
ELSE
'BDues' end as Description ,AmountDue
[...]
WHERE order_date BETWEEN @fiscalYearBeginDate AND @fiscalYearEndDate

Upvotes: 2

Gordon Linoff
Gordon Linoff

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

Related Questions