Reputation: 561
I have the ff table.
Series
startno | endno
1 | 99
and 2 inputs
DECLARE @inputstart int = 2, @inputend int = 100
I want to select where my 2 inputs lies in range of the ones in the table.
Upvotes: 0
Views: 49
Reputation: 1522
Suppose We have Created a temp Table
CREATE TABLE #DeleteMe(startNo INT , endNo INT )
then we are inserting all the records in temp table For e.g. 1 to 100
;WITH nums AS
(SELECT 1 AS startNo,1 AS endNo
UNION ALL
SELECT startNo + 1 AS startNo,endNo + 1 AS endNo
FROM nums
WHERE nums.startNo <= 99)
INSERT INTO #DeleteMe
SELECT *
FROM nums
/* Here is Start Main Part that you want.... */
DECLARE @inputstart as int ,@inputend AS INT
SET @inputstart= 2
SET @inputend = 50
SELECT * FROM #DeleteMe WHERE
convert(INT,startNo) BETWEEN Convert(INT,CASE WHEN isnull(@inputstart,'')='' THEN startNo ELSE isnull(@inputstart,'') END)
AND Convert(INT, CASE WHEN isnull(@inputend,'')='' THEN endNo ELSE isnull(@inputend,'') END)
Upvotes: 0
Reputation: 6193
Try this.
SELECT *
FROM Your_Table
WHERE (StartNo BETWEEN @inputstart AND @inputend) OR (endno BETWEEN @inputstart AND @inputend)
If you want in which column lies between your inputs, then try it.
SELECT MAX(CASE WHEN StartNo BETWEEN @inputstart AND @inputend THEN StartNo
WHEN endno BETWEEN @inputstart AND @inputend THEN endno END)
FROM Your_Table
Upvotes: 1
Reputation: 13026
try this
declare @inputstart int = 2, @inputend int = 100;
with cte as (
select 1 as startno, 99 as endno
) select * from cte where @inputstart between startno and endno
and @inputend between startno and endno
Upvotes: 0