procFoo (par1 int, par2 int);

create type tbType the table (col1 int, col2 int)
create newProc (@ tbPar tbType)
as
    --What to do here!? (Without using cursor or while)
go

create type tbType the table (col1 int, col2 int)
create newProc (@tbPar tbType)
as
..
select
from @tbPar 
join othertable
....

quote:

---

he trick is to introduce a table of values (named, in the example below, MyTableOfIntegers) which contains all the integer values between 1 and (at least) some value (in the case at hand, that would be the biggest possible Quantity value from OrderTransaction table).

INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId])
SELECT OT.OrderTransactionId, P.MediaTypeId
FROM #OrderTransaction OT WITH (NOLOCK)
INNER JOIN #Product P WITH (NOLOCK)
ON OT.ProductId = P.ProductId
JOIN MyTableOfIntegers I ON I.Num <= OT.Quantity
--WHERE some optional conditions

Essentially the extra JOIN on MyTableOfIntegers, produces as many duplicate rows as OT.Quantity, and that seems to be what the purpose of the cursor was: to insert that many duplicated rows in the OrderDelivery table.

I didn't check the rest of the logic with the temporary tables and all (I'm assuming these are temp tables for the purpose of checking the logic rather than being part of the process proper), but it seems that the above is the type of construct needed to express the needed logic in declarative fashion only, without any cursor or even any loop.

---

quote:

---

Originally posted by paultech

Hi ,Welcome first to sql Team you can take a look to below idea and apply it on your Code

quote:

---

he trick is to introduce a table of values (named, in the example below, MyTableOfIntegers) which contains all the integer values between 1 and (at least) some value (in the case at hand, that would be the biggest possible Quantity value from OrderTransaction table).

INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId])
SELECT OT.OrderTransactionId, P.MediaTypeId
FROM #OrderTransaction OT WITH (NOLOCK)
INNER JOIN #Product P WITH (NOLOCK)
ON OT.ProductId = P.ProductId
JOIN MyTableOfIntegers I ON I.Num <= OT.Quantity
--WHERE some optional conditions

Essentially the extra JOIN on MyTableOfIntegers, produces as many duplicate rows as OT.Quantity, and that seems to be what the purpose of the cursor was: to insert that many duplicated rows in the OrderDelivery table.

I didn't check the rest of the logic with the temporary tables and all (I'm assuming these are temp tables for the purpose of checking the logic rather than being part of the process proper), but it seems that the above is the type of construct needed to express the needed logic in declarative fashion only, without any cursor or even any loop.

---

For further details goto
http://stackoverflow.com/questions/2209771/sql-server-improve-procedure-without-using-cursor


paul Tech

---