SQL Help!

Factual Questions
1

SQL the bane of my existence.

I’m having trouble creating a stored procedure that returns a single record from a table that looks like this:



ID     Min      Max
1       10       20
2       25       45
3       46       99
4      120      145

Where the argument coming in from the request is between min and max. That’s simple, but there’s another layer. As you cna see the ranges are not always sequential accross records, so IF the value is NOT between the min and max of any record, then I need to return the record where the max is closest to the passed argument.

So for an input of 20 I should get record Id#1, obviously, but for an input of 105 I should get record id#3.

Anyone care to help me out?

2

Without much thinking, so not an optimal solution (but a starting point, hopefully) :

DECLARE @Response INT = NULL

SET @Response = ID from tableName WHERE argument BETWEEN Min AND Max

IF @Response = NULL --no interval found
BEGIN
SET @Response = (SELECT TOP 1 ID FROM tableName ORDER BY Max - argument)
END

3

Kinthalis loves purple too! Thanks, this helps a lot.