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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics