Database

wajid ansari wrote:

numbers between 12 to 20 or from 100 to 150.

create table #numbers (value int )<br />
<br />
declare @i as int<br />
set @i = (select min(number) from myTable)<br />
<br />
while @i<(select max(number) from myTable)-1<br />
begin<br />
set @i=@i+1<br />
<br />
insert into #numbers values (@i)<br />
<br />
end<br />
select * from #numbers <br />
drop table #numbers 

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

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

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

SET @XMLDOC.modify('
replace value of (/DATAMATRIX/FSDATAMATRIX/<code>FIELD2[../FIELD1="BELGIUM"]</code>/text())[1]
with sql:variable("@XMAXPPM")
')