using case and between in where clause of sql server

Assume in database table called tbl_HouseDetails contains the columns details as follows
house_id house_owner_id room otherdeatils
int(pk) int int nvarchar(10)
1 2 1 own
2 1 2 own
3 3 2 own
4 2 0 own
5 4 0 own
6 5 3 own
7 2 2 own
8 9 1 own
9 1 1 own
10 3 0 own

If you want to get the house details based on min_room and max_room then use this stored procedure
if you pass minroom as -1 or maxroom as -1 then it gives all the housedetails other wise between condition will be executed
Check out the outputs

Create Proc Getdeatils(@minroom int,maxroom int)
as begin
select * from tbl_HouseDetails
where (room=case when @minroom=-1 or @maxroom=-1 then room when room between @minroom and @maxroom then room end)
end

exec Getdeatils 1,2
gives the house details of 1,2,3,7,8,9
exec Getdeatils -1,1
gives the house details of 1,2,3,4,5,6,7,8,9,10
exec Getdeatils -1,-1
gives the house details of 1,2,3,4,5,6,7,8,9,10
exec Getdeatils 0,1
gives the house details of 1,4,5,8,9,10
exec Getdeatils 3,3
gives the house details of 6


Tag: case and between in where sql server, using case and between in where clause of sql server