Cursor problems - 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

Transact-SQL (2000)

Cursor problems

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2003-03-16 : 16:22:17

I am having a problem with the following statement
If I substitute the SQL UPDATE statements with a simple Print statement, I get the correct results.
But if the update statements are used the statemnt won't work.

Can anyone help me out

Paul

/*** This procedure will select all invoice header details from the API tables into a cursor. At each fetch statement it will compare the Sales Order Delivery address with existing addresses in the SL Addresses table. If there is a match it will use the existing address No, otherwise it will insert a new address.***/

Declare @orderNo INT
Declare @contact VARCHAR(50)
Declare @address VARCHAR(160)
Declare @account VARCHAR(4)
Declare @deliveryNo INT

Declare SOP_ORDERS CURSOR
FOR

SELECT INVNO, ACCOUNT, CONTACT, ADDRESS FROM IMPORT1 WHERE LINE_NO = 1 AND ([DATE] <= CONVERT(DATETIME, '1999-10-02 00:00:00', 102))

OPEN SOP_ORDERS
Fetch Next from SOP_ORDERS
Into @orderNo, @account, @contact, @address

while @@FETCH_STATUS = 0

BEGIN
Set @deliveryNo = (SELECT AD_CODE FROM SL_ADDRESSES WHERE AD_ACC_CODE = @account AND AD_CONTACT = @contact AND AD_ADDRESS = @address)
If @deliveryNo is null or @deliveryNo = ''

UPDATE SOP_TRN_TEMP SET SOP_STATUS_TEMP = 1 WHERE SOP_ORDER_ID = @orderNo AND SOP_ACCOUNT = @account

Else

UPDATE SOP_TRN_TEMP SET SOP_DELIVERY_ADDRESS = '', SOP_DELIVERY_CONTACT = '', SOP_DELIVERY_ADDRESS_NO = @deliveryNo, SOP_INVOICE_ADDRESS_NO = 0 WHERE SOP_ORDER_ID = @orderNo AND SOP_ACCOUNT = @account
UPDATE SOP_TRN_TEMP SET SOP_STATUS_TEMP = 1 WHERE SOP_ORDER_ID = @orderNo AND SOP_ACCOUNT = @account

Fetch Next from SOP_ORDERS
Into @orderNo, @account, @contact, @address

END
Close SOP_ORDERS
deallocate SOP_ORDERS

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-16 : 17:24:54

Why use a cursor at all?

UPDATE S
SET SOP_DELIVERY_ADDRESS = CASE WHEN IsNull(A.AD_CODE,'')=''
THEN S.SOP_DELIVERY_ADDRESS ELSE '' END,
SOP_DELIVERY_CONTACT = CASE WHEN IsNull(A.AD_CODE,'')=''
THEN S.SOP_DELIVERY_CONTACT ELSE '' END,
SOP_DELIVERY_ADDRESS_NO = CASE WHEN IsNull(A.AD_CODE,'')=''
THEN S.SOP_DELIVERY_ADDRESS_NO ELSE A.AD_CODE END,
SOP_INVOICE_ADDRESS_NO = 0,
SOP_STATUS_TEMP = 1
FROM SOP_TRN_TEMP S INNER JOIN IMPORT1 I ON I.INVNO=S.SOP_ORDER_ID AND I.ACCOUNT=S.SOP_ACCOUNT
INNER JOIN SL_ADDRESSES A ON I.Address=A.AD_ADDRESS AND I.Account=A.AD_ACC_CODE AND I.Contact=A.AD_CONTACT
WHERE I.LINE_NO = 1 AND I.[DATE] <= '19991002'

This avoids having multiple UPDATE statements running, and everything is done in one pass with no cursors at all.

Edited by - robvolk on 03/16/2003 17:32:15

wotrac
Yak Posting Veteran

98 Posts

Posted - 2003-03-17 : 14:40:05

Could you explain what the letters
S in UPDATE S
A in A.AD_CODE
I in IMPORT1 I ON I.INVNO

represent.
I have no problem with the logic in the rest of the statement.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-17 : 14:44:49

They are Label References to the tables:

UPDATE S
SET SOP_DELIVERY_ADDRESS = CASE WHEN IsNull(A.AD_CODE,'')=''
THEN S.SOP_DELIVERY_ADDRESS ELSE '' END,
SOP_DELIVERY_CONTACT = CASE WHEN IsNull(A.AD_CODE,'')=''
THEN S.SOP_DELIVERY_CONTACT ELSE '' END,
SOP_DELIVERY_ADDRESS_NO = CASE WHEN IsNull(A.AD_CODE,'')=''
THEN S.SOP_DELIVERY_ADDRESS_NO ELSE A.AD_CODE END,
SOP_INVOICE_ADDRESS_NO = 0,
SOP_STATUS_TEMP = 1
FROM SOP_TRN_TEMP S INNER JOIN IMPORT1 I ON I.INVNO=S.SOP_ORDER_ID AND I.ACCOUNT=S.SOP_ACCOUNT
INNER JOIN SL_ADDRESSES A ON I.Address=A.AD_ADDRESS AND I.Account=A.AD_ACC_CODE AND I.Contact=A.AD_CONTACT
WHERE I.LINE_NO = 1 AND I.[DATE] <= '19991002'

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources