Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11).
So far I have two stored procedures:
1) sp_copyDetailsOne
/*Create a recordset of the desired items to be copied*/
CREATE PROCEDURE sp_copyDetailsOne @.invoiceIdFrom INT, @.outCrsr CURSOR VARYING OUTPUT AS
SELECT itemId, itemPrice, itemDescription, itemQuantity
FROM tblSalesItems
WHERE (invoiceId = @.invoiceIdFrom)
OPEN @.outCrsr
2) sp_copyDetailsTwo
CREATE PROCEDURE sp_copyDetailsTwo @.invoiceIdFrom INT, @.invoiceIdTo INT
/*Allocate a cursor variable*/
DECLARE @.crsrVar CURSOR
/*Execute sp_copyDetailsOne to instantiate @.crsrVar*/
EXEC sp_copyDetailsOne @.invoiceIdFrom, @.outCrsr = @.crsrVar OUTPUT
/*Loop through the recordset and insert a new record using the new invoiceId*/
FETCH NEXT FROM @.crsrVar
WHILE @.@.FETCH_STATUS = 0
BEGIN
/*Run the insert here*/
INSERT INTO tblSalesItems
(invoiceId, itemId, itemPrice, itemDescription, itemQuantity)
VALUES (@.invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30)
/*Fetch next record from cursor*/
FETCH NEXT FROM @.crsrVar
END
CLOSE @.crsrVar
DEALLOCATE @.crsrVar
My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome.
Thanksis this a one time operation? If so, won't insert (...) (select ... +1) do?|||Your FETCH just displays the data accessed by the cursor. You need to FETCH...INTO a list of variables that would hold the values that you'll use in VALUES clause of your INSERT (@.itemPrice, @.itemDescription, @.itemQuantity)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment