Playing with parameters
Simple question from newbie (yes, question is simple),
select somethign from somewhere
where number between X and Y.
I would like to set values for X and Y depending what use set for parameter, and I have only one parameter
e.g user set parameter=3, then X=3+2 and Y=3-1, i.e +2 and -1 are constants
I use RS2005
Thanks!
February 26th, 2011 5:58am
the general idea of sqluser's response is correct, except the "between" operator for most DBMS's (including sql server which I'll assume trkp is using) also includes the lower and upper bounds
so between 2 and 5 should return 2,3,4,5
so it should be modified to: number <= @var+2 and number >= @var-1
or simply:
number between @var-1 and @var+2
Free Windows Admin Tool Kit Click here and download it now
February 26th, 2011 7:56am
HI trkp
CREATE TABLE #TMP (number int)
INSERT INTO #TMP Values (1)
INSERT INTO #TMP Values (2)
INSERT INTO #TMP Values (3)
INSERT INTO #TMP Values (4)
INSERT INTO #TMP Values (5)
INSERT INTO #TMP Values (6)
INSERT INTO #TMP Values (7)
INSERT INTO #TMP Values (8)
INSERT INTO #TMP Values (9)
--
SELECT * FROM #TMP -- 1 to 9
--
DECLARE @Var int
SET @Var=3
SELECT * FROM #TMP
WHERE
number < @Var + 2 -- less than (3+2) i.e, < 5
AND
number > @Var -1 -- greater than (3-1) i.e, > 2
--Result: 3,4 ( between 2 and 5)
I hope this helps.
Thanks.iPranith
February 26th, 2011 8:27am
Hi Nehemiah Willis,
I tried this query
DECLARE @Var int
SET @Var=3
SELECT number FROM #TMP
WHERE
number between @Var+2 AND @Var-1
But it returned no values, I don't understand whats the reason.
Let me know if I was doing anything wrong..
Thanks..!iPranith
Free Windows Admin Tool Kit Click here and download it now
February 26th, 2011 8:51am
Thanks to all
it works now, it was really easy because it works just I thought. I don't know why it didn't work in the first place
"select something from somewhere where number between @var-1 and @var+2"
When I did that first time, parameter was not selectable, it was just gray...
February 26th, 2011 9:07am
try switching the order, you are doing "between 5 and 2" instead of "between 2 and 5"
the lower condition has to come before the upper condition in a between statement
Free Windows Admin Tool Kit Click here and download it now
February 26th, 2011 9:23am