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
Select 0 as is_valid
Select 1 as is_valid
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;