Solved

check range with sql

Posted on 2014-01-22
3
228 Views
Last Modified: 2014-01-23
I have a variable in my sql server store proc and i want to check if the variable is in between certain numbers

so if  @variable is in between 100-199 or @variable in between 600-699 Or variable in between 900-999

begin
 -- do my query of over here
end

How do i check if my variable falls in that range that i have mentioned above
0
Comment
Question by:pratikshahse
3 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39801344
declare @test int


select @test = case when @var between 100 and 199 then 1
                                 when @var between 600 and 699 then 1
                                 when @var between 900 and 999 then 1
                                 else 0 end

if @test = 1
begin
  -- query
end
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39801345
ok you can add the between clause to the where clause of the query

select * 
from <your Table>
where <your cond>
AND @VAR BETWEEN <Start> and <end>

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39801352
The below is easier to read...
SELECT blah, blah, blah
FROM YourTable
WHERE 
   @variable between 100 AND 199 OR
   @variable between 600 AND 699 OR
   @variable between 900 AND 999

Open in new window

... although Kyle's code is preferred, as if the @variable does not fall in these ranges then the query will not be performed, saving that unneeded execution time.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now