while fetch - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

while fetch

Author

Topic

seeker62
Starting Member

40 Posts

Posted - 2013-04-12 : 09:34:34

I have a table that has contracts with several shipping orders assinged to that contract. I am updateing that table by setting the contract volume of the contract. Once I populate that i want to fetch the next contract that is different than the one i was just on which would mean that i would move through several records in table before i populated the next contract volume. This would be done in a loop until i reached eof. Does fetch next have an option to say fetch next 10 as an example? This update will be in an sp and will be ran hourly.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-12 : 09:45:05

Post the sample data in consumable format as well as expected output...

explain the problem with your code?

seeker62
Starting Member

40 Posts

Posted - 2013-04-12 : 10:32:33

Here is more exact fetch relative (@move) is saying it is looking for ( or select

DECLARE @contract VARCHAR(15)
DECLARE @move int
DECLARE tb_cur CURSOR
FOR
SELECT contractnum
FROM table
WHERE ProductType = 'co2'
ORDER BY ContractNum
OPEN tb_cur
FETCH NEXT FROM tb_cur INTO @contract
set @move = (SELECT COUNT(*) FROM table
WHERE ContractNum = @contract)

WHILE @@fetch_status = 0
BEGIN
set @move = (SELECT COUNT(*) FROM table
WHERE ContractNum = @contract)
UPDATE table
set contractedvolume = (SELECT TOP 1 quantity
FROM table idq
INNER JOIN table ch ON ch.ContractID = idq.RecordID
WHERE ch.ContractNum = @contract)
FETCH RELATIVE(@move) FROM tb_cur INTO @contract
END
CLOSE tb_cur
DEALLOCATE tb_cur

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources