cursors - 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

SQL Server 2000 Forums

SQL Server Development (2000)

cursors

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-05 : 20:52:15

Art Ramos writes "I am tring to find a way to loop with in a cursor.

Decalre cursor
Select invoice_no,price,qty from tblinvoice
as

Cursor

fetch next into @invoice_no,@qty,@price

for i = 0 to @qty

insert into tblBilling
Values(@invoice,@price,@qty)

next i

fetch next into @invoice_no,@qty,@price

What I am trying to do is insert a number of rows equal to the quantity(@qty). Then move to the next until I go thru the whole cursor. I am kind of new to cursors so any assistance will be appreciated."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-05-06 : 02:31:17

Not using cursors is a whole lot easier here. Use a table of integers:


INSERT INTO tblBilling
SELECT invoice_no, price, qty
FROM tblInvoice
INNER JOIN Numbers ON n BETWEEN 1 AND qty

This will create 0-999 (although a 1-based table would be more suitable in this case):


CREATE TABLE Numbers (n int PRIMARY KEY)

INSERT INTO Numbers SELECT T1.n + T2.n*10 + T3.n*100 FROM
  (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
   UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T1,
  (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
   UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T2,
  (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
   UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources