create table #stock
(
[id] int,
stockcode varchar(10),
startrange int,
endrange int,
nextavailablenumber int,
StartPick int,
EndPick int
)

INSERT INTO #stock values (1,'A',1,20,1,NULL,NULL)
INSERT INTO #stock values (2,'A',201,250,201,NULL,NULL)
INSERT INTO #stock values (3,'A',401,500,401,NULL,NULL)

/*
declare @qty_to_pick int
set @qty_to_pick =30

 if @qty_to_pick is 30 then 
help me in writing a query 
to update table with following values
id	stockcode	startrange	endrange	nextavailablenumber	StartPick	EndPick
1	A		1		20		21			1		20
2	A		201		250		211			201		210
3	A		401		500		401			NULL		NULL
*/

/*
declare @qty_to_pick int
set @qty_to_pick =80

 if @qty_to_pick is 80 then help me in writing a query 
to update table with following values

id	stockcode	startrange	endrange	nextavailablenumber	StartPick	EndPick
1	A		1		20		21			1		20
2	A		201		250		251			201		250
3	A		401		500		411			401		410
*/

select * from #stock



DROP TABLE #stock

create table #stock
(
[id] int,
stockcode varchar(10),
startrange int,
endrange int,
nextavailablenumber int,
StartPick int,
EndPick int
)

INSERT INTO #stock values (1,'A',1,20,1,NULL,NULL)
INSERT INTO #stock values (2,'A',201,250,201,NULL,NULL)
INSERT INTO #stock values (3,'A',401,500,401,NULL,NULL)

Declare @qty int,@id int, @start int,@end int,@qtyavailable int,@diff int,@startpick int,@endpick int,@nextpick int
Set @qty=10

while (@qty>0)
Begin 
	select @id=min(id) from #stock where nextavailablenumber<endrange
	select @start=nextavailablenumber,@end=endrange	from #stock where id=@id
	set @diff=@end  - @start + 1
	if @diff > @qty
		BEGIN
		set @startpick=@start
		set @endpick=@start+@qty - 1
		set @nextpick=@start+ @qty
		set @qty=@qty-@diff
		update #stock set startpick=@startpick,endpick=@endpick,nextavailablenumber=@nextpick where id=@id
		END
	else
		BEGIN
		set @startpick=@start
		set @endpick=@end
		set @nextpick=@end+1
		set @qty=@qty-@diff
		update #stock set startpick=@startpick,endpick=@endpick,nextavailablenumber=@nextpick where id=@id
		end
End

select * from #stock

DROP TABLE #stock