CODEWITHSUNDEEP
sqlsql-serverstringstored-procedureswhere-clause
aantiix
aantiix

Reputation: 544

Returning values not ending in digits based upon variable

I'm passing through a variable into my stored procedure and was writing a case statement in my WHERE clause. Normally I would write it something like this:

CodeID like CASE WHEN @Duration != '0' 
                THEN '%' + @Duration 
                ELSE '%' END

The problem, however, is that this isn't quite what I need. What I actually need is something with the logic of IF @Duration != '0' THEN CodeID like '%' + @Duration ELSE CodeID doesn't end with a digit.

Is this possible? I considered writing a couple of IF statements with the respective queries in each, but as there isn't a wildcard for digit, I'm having some trouble with this logic as well.

Upvotes: 1

Views: 85

Answers (2)

GMB
GMB

Reputation: 222632

What I actually need is something with the logic of IF @Duration != '0' THEN CodeID like '%' + @Duration ELSE CodeID doesn't end with a digit.

Use boolean logic:

where 
    (@duration <> '0' and CodeID like '%' + @Duration)
    or (@duration = '0' and CodeID not like '%[0-9]')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

How about:

(@Duration <> '0' and codeid like '%' + @duration) or
(@Duration = '0' and codeid like '%[0-9]')

Upvotes: 0

Related Questions