|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| ||||||||||||||||||||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
For Loop Cursor
I am trying to write a for loop cursor, and this is my code
DECLARE slip_Update CURSOR FOR SELECT * FROM SlipsTEST, SlipdetailsTEST WHERE SlipsTEST.SlipIdOld = SlipdetailsTEST.SlipIdOld FOR UPDATE; BEGIN OPEN slip_Update; FOR slip_Update IN slip_Update LOOP UPDATE SlipdetailsTEST SET SlipdetailsTEST.SNO = SlipsTEST.SNO WHERE CURRENT OF slip_Update; END LOOP; CLOSE slip_Update; END; Can any one tell me what is wrong with my cursor? I run it in SQL Query Analyzer and it always shows this message: Server: Msg 156, Level 15, State 1, Line 9 Incorrect syntax near the keyword 'FOR'. Server: Msg 170, Level 15, State 1, Line 14 Line 14: Incorrect syntax near 'LOOP'. Server: Msg 156, Level 15, State 1, Line 16 Incorrect syntax near the keyword 'END'. |
|
#2
|
|||
|
|||
|
The following is how I do cursors on MS SQL Server 2000. According to my experience:
1. you need to specify the columns you're putting into the cursor 2. You need variables for each of the cursor's fields 3. The fetching and looping structure I outlined below. For Example: +---------------------------------------------------+ -- declare the cursor DECLARE slip_Update CURSOR FOR SELECT x, y, z FROM SlipsTEST , SlipdetailsTEST WHERE SlipsTEST.SlipIdOld = SlipdetailsTEST.SlipIdOld -- declare what to put the cursor variable into DECLARE @x int DECLARE @y varchar(25) DECLARE @Z datetime -- Open cursor OPEN slip_Update -- Go to first cursor item (make sure fields same order) FETCH NEXT FROM slip_Update INTO @x, @y, @z -- Loop through each item (loops between BEGIN and END) WHILE @@Fetch_Status=0 BEGIN -- do whatever code you want with the variables here UPDATE SlipdetailsTEST SET SlipdetailsTEST.SNO = @x -- go to next cursor item FETCH NEXT FROM slip_Update INTO @x, @y, @z END -- clean up or if you run this again you'll get errors Close slip_Update Deallocate slip_Update +---------------------------------------------------+ Hope this helps! Like I said, though, it works for me with MS SQL Server 2000 so I'm not sure if it will work in your environment. |
|
#3
|
|||
|
|||
|
Thank you dragonbite for helping me... I really appreciate it
I will try it |
![]() |
| Viewing: Dev Articles Community Forums > Databases > General SQL Development > For Loop Cursor |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|