lower and upper limit problem

wajans

I have a table with 3 columns as follows: lower upper id 12 20 100 100 150 200 I want to select range of numbers based on lower and upper column. For example I want to display numbers between 12 to 20 or from 100 to 150. How to do it. thanks in advance.

Blue_Boy

wajid ansari wrote:

numbers between 12 to 20 or from 100 to 150.

Exists stored in table or not?

I Love T-SQL "Don't torture yourself,let the life to do it for you."

Blue_Boy

Here I made a sample test with temporary table. create table #numbers (value int ) declare @i as int set @i = (select min(number) from myTable) while @i<(select max(number) from myTable)-1 begin set @i=@i+1 insert into #numbers values (@i) end select * from #numbers drop table #numbers

I Love T-SQL "Don't torture yourself,let the life to do it for you."

PIEBALDconsult

Your question doesn't give much idea of what you want, so here are some choices:

CREATE FUNCTION GetRange
(
@Lower INTEGER
,
@Upper INTEGER
)
RETURNS @IntegerRange TABLE
(
Member INTEGER
)
AS
BEGIN
WHILE @Lower<=@Upper
BEGIN
INSERT INTO @IntegerRange VALUES (@Lower)
SET @Lower=@Lower+1
END

RETURN

END

SELECT * FROM GetRange ( 12 , 20 )

CREATE FUNCTION GetRangeById
(
@ID INTEGER
)
RETURNS @IntegerRange TABLE
(
Member INTEGER
)
AS
BEGIN
DECLARE @Lower INTEGER
DECLARE @Upper INTEGER

SET @Lower = (SELECT \[Lower\] FROM Series WHERE ID=@ID)
SET @Upper = (SELECT \[Upper\] FROM Series WHERE ID=@ID)

WHILE @Lower<=@Upper
BEGIN
	INSERT INTO @IntegerRange VALUES (@Lower)
	SET @Lower=@Lower+1
END

RETURN

END

SELECT * FROM GetRange ( 100 )

CREATE FUNCTION GetAllRanges
(
)
RETURNS @IntegerRange TABLE
(
ID INTEGER
,
Member INTEGER
)
AS
BEGIN
DECLARE @ID INTEGER
DECLARE @Lower INTEGER
DECLARE @Upper INTEGER

DECLARE SeriesCursor CURSOR FOR SELECT \* FROM Series 

OPEN SeriesCursor

FETCH NEXT FROM SeriesCursor
INTO @ID , @Lower , @Upper

WHILE @@FETCH\_STATUS=0
BEGIN
	WHILE @Lower<=@Upper
	BEGIN
		INSERT INTO @IntegerRange VALUES (@ID , @Lower)
		SET @Lower=@Lower+1
	END

	FETCH NEXT FROM SeriesCursor
	INTO @ID , @Lower , @Upper

END

CLOSE SeriesCursor

RETURN

END

SELECT * FROM GetAllRanges()