How to findout a number between two column in SQL Server 2008

Suppose I have table called dbo.tbl_Search

Id    From    To
-------------------
1     200     210
2     212     220
3     203     215
4     225     240

Suppose, I search for No.-205 then I would to like result like this

Id    From    To
-------------------
1     200     210
3     203     215

Because 205 No. falls between this two ids.

Note: I don’t have any other table to join with this.

I have tried this SQL but it’s bot working:

SELECT * 
FROM dbo.tbl_Search 
WHERE 
    (ISNULL(From, 0) = (CASE WHEN ISNULL(205, 0) = 0 
                                THEN ISNULL(From, 0) 
                                ELSE ISNULL(205, 0) 
                        END ) 
    OR ISNULL(To, 0) = (CASE WHEN ISNULL(205, 0) = 0 
                                THEN ISNULL(To, 0) 
                                ELSE ISNULL(205, 0) 
                        END ))

Answer

SELECT * 
FROM dbo.tbl_Search 
WHERE ISNULL(@number, 0) BETWEEN FROM AND TO

Using between operator you can find a records between range.

Advertisement

Answer