Darrell Norton's Blog [MVP]

Sponsors

The Lounge

Wicked Cool Jobs

News

  • Darrell Norton pic

    MVP logo

    View Darrell Norton's profile on LinkedIn

    Currently Reading:

    weewar.com

Advertisement

Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Determining if a T-SQL variable is in a range of values

Here’s a neat little T-SQL trick.

You can use set-based operations on variables. For example, copy this in SQL Server Query Analyzer and running it prints “Vowel” to the output window:

DECLARE @letter char(1)
SELECT @letter = 'A'
IF @letter IN ('A', 'E', 'I', 'O', 'U')
            PRINT 'Vowel'
ELSE
            PRINT 'Consonant'

Yes the example is simple, but where I find it useful is with a varchar variable passed into a stored proc called by other stored procs. I need to check for one of 10 values (out of hundreds), and this is a whole lot easier than writing
IF @letter = ‘A’ OR @letter = ‘E’ etc.


Posted Wed, Oct 6 2004 12:28 PM by Darrell Norton

[Advertisement]

Comments

Jason Row wrote re: Determining if a T-SQL variable is in a range of values
on Thu, Oct 7 2004 12:54 AM
The "IF @letter = 'A' OR @letter = 'E'" ... wasn't that an entry on <a href="http://thedailywtf.com/">The Daily WTF</a> ? Was that your code that I saw :)
Jason Row wrote re: Determining if a T-SQL variable is in a range of values
on Thu, Oct 7 2004 12:57 AM
Grrr, it's too early in the morning. I'm sure people can figure out the URL even though the link doesn't work. Good thing it's Friday.
Darrell wrote re: Determining if a T-SQL variable is in a range of values
on Thu, Oct 7 2004 1:34 AM
The correct link for readers is http://thedailywtf.com/

And this was an *example*, not the actual codes, sheesh. :)
Jason Row wrote re: Determining if a T-SQL variable is in a range of values
on Thu, Oct 7 2004 3:39 AM
I'm going to take a closer look at your URLScanWatcher code now to see what I can submit!
Steve wrote re: Determining if a T-SQL variable is in a range of values
on Thu, Oct 7 2004 7:56 AM
Or put it in a function and call it directly from a SELECT statement. Just be mindful of the implications of OR conditions on the query optimizer, because this is essentially what an 'IN' statement is.
me wrote re: Determining if a T-SQL variable is in a range of values
on Thu, Oct 14 2004 1:23 AM
"...be mindful of the implications or OR conditions..." Come on, Steve. Either you need to meet the condition or you don't.
Devlicio.us