Solved

check range with sql

Posted on 2014-01-22
3
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 66

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

728 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