Umer Umer - 2 months ago 6
SQL Question

How to return 0 or 1 to check available number in a range in sql?

I am writing a stored procedure where i want to check available number in a range. The stored procedure will return 0 or 1 as bit.

Case 0 means, the number is not available

Case 1 means, the number is available.

I have written this query

Declare @recivedNo As INT = 82
Declare @minSourceNo as INT
Declare @maxSourceNo as INT

Select @minSourceNo = min(Source_no) from Source
Select @maxSourceNo = max(Source_no) from Source

IF(Select COunt(1) from Source
WHere Source_no = @recivedNo AND @recivedNo Between @minSourceNo AND @maxSourceNo

)>0
begin
Select 0 as is_valid
end
else
Select 1 as is_valid


Suppose the min number is 1 and max is 88. And available number is 5.
If i check number 10. It reruns 0 which is correct. If i check number 5 it returns 1 which is fine. Note the both number 5 and 10 are within the range. But if i check number 100. It also returns 1 which is not correct for me. It should return 0 because 100 is not in the range. Is there any way to check this?

Answer

Your evaluation "in the table and between min and max" is incorrect. You are interested in whether the number is available (i.e. "not in the table and between min and max") or not.

select case when @recivedNo between @minSourceNo and @maxSourceNo 
            and not exists (select * from source where source_no = @recivedNo)
       then 1 else 0 end as is_valid;

Or vice versa:

select case when @recivedNo not between @minSourceNo and @maxSourceNo 
            or exists (select * from source where source_no = @recivedNo)
       then 0 else 1 end as is_valid;