Best way to handle reversed values in BETWEEN clause  
Author Message
Flight58





PostPosted: Thu Oct 28 12:30:06 CDT 2004 Top

SQL Server Developer >> Best way to handle reversed values in BETWEEN clause

I have come across an interesting situation.
I have a form that allows a user to search records based on a value
range (i.e 100-150)
The values from the ASP form are passed as input parameters to the
stored procedure BETWEEN clause.

However, if the user entered the range backwards (i.e 150-100) no
results are returned as expected.

I am wondering what is the best way to handle this:
1. Do nothing.
2. Prohibit a reverse range from being entered. I can do that in the
client side with form validation. (no problem)
3. Handle it directly in the stored procedure. (not sure how to do that
the best way)

If I do handle it, I think it would be better to do #2.

What do you think? I did some research and could not really find this
issue addressed from the stored procedure side.

Steve

--
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com

SQL Server297  
 
 
Michael





PostPosted: Thu Oct 28 12:30:06 CDT 2004 Top

SQL Server Developer >> Best way to handle reversed values in BETWEEN clause If the numbers are reversed, swap them.

Michael Coles, MCDBA



> I have come across an interesting situation.
> I have a form that allows a user to search records based on a value
> range (i.e 100-150)
> The values from the ASP form are passed as input parameters to the
> stored procedure BETWEEN clause.
>
> However, if the user entered the range backwards (i.e 150-100) no
> results are returned as expected.
>
> I am wondering what is the best way to handle this:
> 1. Do nothing.
> 2. Prohibit a reverse range from being entered. I can do that in the
> client side with form validation. (no problem)
> 3. Handle it directly in the stored procedure. (not sure how to do that
> the best way)
>
> If I do handle it, I think it would be better to do #2.
>
> What do you think? I did some research and could not really find this
> issue addressed from the stored procedure side.
>
> Steve
>
> --
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com


 
 
Steve





PostPosted: Thu Oct 28 14:49:49 CDT 2004 Top

SQL Server Developer >> Best way to handle reversed values in BETWEEN clause Thanks, I decided just to swap the variables on the client side. To me,
it did not make sense to do that in the stored procedure.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com




> If the numbers are reversed, swap them.
>
> Michael Coles, MCDBA
>


>
>>I have come across an interesting situation.
>>I have a form that allows a user to search records based on a value
>>range (i.e 100-150)
>>The values from the ASP form are passed as input parameters to the
>>stored procedure BETWEEN clause.
>>
>>However, if the user entered the range backwards (i.e 150-100) no
>>results are returned as expected.
>>
>>I am wondering what is the best way to handle this:
>>1. Do nothing.
>>2. Prohibit a reverse range from being entered. I can do that in the
>>client side with form validation. (no problem)
>>3. Handle it directly in the stored procedure. (not sure how to do that
>>the best way)
>>
>>If I do handle it, I think it would be better to do #2.
>>
>>What do you think? I did some research and could not really find this
>>issue addressed from the stored procedure side.
>>
>>Steve
>>
>>--
>>____________________________
>>Steven K. Lewis
>>Website Nation, LLC
>>Website Development Services
>>www dot Website Nation dot com
>
>
>
 
 
Steve





PostPosted: Thu Oct 28 18:58:47 CDT 2004 Top

SQL Server Developer >> Best way to handle reversed values in BETWEEN clause Steve,

You could also modify your stored procedure to say

between

and


Steve Kass
Drew University



> Thanks, I decided just to swap the variables on the client side. To
> me, it did not make sense to do that in the stored procedure.
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
>
>

>
>> If the numbers are reversed, swap them.
>>
>> Michael Coles, MCDBA
>>


>>
>>> I have come across an interesting situation.
>>> I have a form that allows a user to search records based on a value
>>> range (i.e 100-150)
>>> The values from the ASP form are passed as input parameters to the
>>> stored procedure BETWEEN clause.
>>>
>>> However, if the user entered the range backwards (i.e 150-100) no
>>> results are returned as expected.
>>>
>>> I am wondering what is the best way to handle this:
>>> 1. Do nothing.
>>> 2. Prohibit a reverse range from being entered. I can do that in the
>>> client side with form validation. (no problem)
>>> 3. Handle it directly in the stored procedure. (not sure how to do that
>>> the best way)
>>>
>>> If I do handle it, I think it would be better to do #2.
>>>
>>> What do you think? I did some research and could not really find this
>>> issue addressed from the stored procedure side.
>>>
>>> Steve
>>>
>>> --
>>> ____________________________
>>> Steven K. Lewis
>>> Website Nation, LLC
>>> Website Development Services
>>> www dot Website Nation dot com
>>
>>
>>
>>
 
 
Michael





PostPosted: Thu Oct 28 19:35:28 CDT 2004 Top

SQL Server Developer >> Best way to handle reversed values in BETWEEN clause The advantage to doing it in the stored procedure is that, if you modify the
client in the future, it's one less thing you have to worry about on that
end - it will always automatically take care of itself whenever you call the
stored procedure.

Michael C., MCDBA



> Thanks, I decided just to swap the variables on the client side. To me, it
> did not make sense to do that in the stored procedure.
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
>
>

>> If the numbers are reversed, swap them.
>>
>> Michael Coles, MCDBA
>>


>>
>>>I have come across an interesting situation.
>>>I have a form that allows a user to search records based on a value
>>>range (i.e 100-150)
>>>The values from the ASP form are passed as input parameters to the
>>>stored procedure BETWEEN clause.
>>>
>>>However, if the user entered the range backwards (i.e 150-100) no
>>>results are returned as expected.
>>>
>>>I am wondering what is the best way to handle this:
>>>1. Do nothing.
>>>2. Prohibit a reverse range from being entered. I can do that in the
>>>client side with form validation. (no problem)
>>>3. Handle it directly in the stored procedure. (not sure how to do that
>>>the best way)
>>>
>>>If I do handle it, I think it would be better to do #2.
>>>
>>>What do you think? I did some research and could not really find this
>>>issue addressed from the stored procedure side.
>>>
>>>Steve
>>>
>>>--
>>>____________________________
>>>Steven K. Lewis
>>>Website Nation, LLC
>>>Website Development Services
>>>www dot Website Nation dot com
>>
>>

 
 
Steve





PostPosted: Sat Oct 30 23:50:46 CDT 2004 Top

SQL Server Developer >> Best way to handle reversed values in BETWEEN clause Thanks, I will consider using that! It looks a little more simple than
my client side solution.

Steve
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com




> Steve,
>
> You could also modify your stored procedure to say
>
> between

> and

>
> Steve Kass
> Drew University
>

>
>> Thanks, I decided just to swap the variables on the client side. To
>> me, it did not make sense to do that in the stored procedure.
>> ____________________________
>> Steven K. Lewis
>> Website Nation, LLC
>> Website Development Services
>> www dot Website Nation dot com
>>
>>
>>

>>
>>> If the numbers are reversed, swap them.
>>>
>>> Michael Coles, MCDBA
>>>


>>>
>>>> I have come across an interesting situation.
>>>> I have a form that allows a user to search records based on a value
>>>> range (i.e 100-150)
>>>> The values from the ASP form are passed as input parameters to the
>>>> stored procedure BETWEEN clause.
>>>>
>>>> However, if the user entered the range backwards (i.e 150-100) no
>>>> results are returned as expected.
>>>>
>>>> I am wondering what is the best way to handle this:
>>>> 1. Do nothing.
>>>> 2. Prohibit a reverse range from being entered. I can do that in the
>>>> client side with form validation. (no problem)
>>>> 3. Handle it directly in the stored procedure. (not sure how to do that
>>>> the best way)
>>>>
>>>> If I do handle it, I think it would be better to do #2.
>>>>
>>>> What do you think? I did some research and could not really find this
>>>> issue addressed from the stored procedure side.
>>>>
>>>> Steve
>>>>
>>>> --
>>>> ____________________________
>>>> Steven K. Lewis
>>>> Website Nation, LLC
>>>> Website Development Services
>>>> www dot Website Nation dot com
>>>
>>>
>>>
>>>
>>>
 
 
Gert-Jan





PostPosted: Sun Oct 31 04:38:48 CST 2004 Top

SQL Server Developer >> Best way to handle reversed values in BETWEEN clause I am a bit late to this discussion, but you could also add another
BETWEEN clause:



However, the performance of it might be terrible.

HTH,
Gert-Jan



>
> Thanks, I will consider using that! It looks a little more simple than
> my client side solution.
>
> Steve
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>

> > Steve,
> >
> > You could also modify your stored procedure to say
> >
> > between

> > and

> >
> > Steve Kass
> > Drew University
> >

> >
> >> Thanks, I decided just to swap the variables on the client side. To
> >> me, it did not make sense to do that in the stored procedure.
> >> ____________________________
> >> Steven K. Lewis
> >> Website Nation, LLC
> >> Website Development Services
> >> www dot Website Nation dot com
> >>
> >>
> >>

> >>
> >>> If the numbers are reversed, swap them.
> >>>
> >>> Michael Coles, MCDBA
> >>>


> >>>
> >>>> I have come across an interesting situation.
> >>>> I have a form that allows a user to search records based on a value
> >>>> range (i.e 100-150)
> >>>> The values from the ASP form are passed as input parameters to the
> >>>> stored procedure BETWEEN clause.
> >>>>
> >>>> However, if the user entered the range backwards (i.e 150-100) no
> >>>> results are returned as expected.
> >>>>
> >>>> I am wondering what is the best way to handle this:
> >>>> 1. Do nothing.
> >>>> 2. Prohibit a reverse range from being entered. I can do that in the
> >>>> client side with form validation. (no problem)
> >>>> 3. Handle it directly in the stored procedure. (not sure how to do that
> >>>> the best way)
> >>>>
> >>>> If I do handle it, I think it would be better to do #2.
> >>>>
> >>>> What do you think? I did some research and could not really find this
> >>>> issue addressed from the stored procedure side.
> >>>>
> >>>> Steve
> >>>>
> >>>> --
> >>>> ____________________________
> >>>> Steven K. Lewis
> >>>> Website Nation, LLC
> >>>> Website Development Services
> >>>> www dot Website Nation dot com
> >>>
> >>>
> >>>
> >>>
> >>>