cursor query - 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 2005 Forums

Transact-SQL (2005)

cursor query

Author

Topic

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-10-17 : 09:14:55

I am rtying to learn how to use the cursor function in sql.

I have the below which i got from the help files but would like to know how to write back to the field within the cursor function

In this example my field is crr_id
can i just reffer to it as a parameter for example @ccr_id = 'a'

DECLARE Upload_Cursor CURSOR FOR
SELECT ccr_id
FROM Table WHERE (ccr_id IS NULL)

OPEN Upload_Cursor

FETCH NEXT FROM Upload_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN

--My code would go here

FETCH NEXT FROM Upload_Cursor
END

CLOSE Upload_Cursor
DEALLOCATE Upload_Cursor

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:18:59

Use WHERE CURRENT OF to update current position of cursor.
Or better, rewrite whole code to set-based instead!

Please post your attempt together with proper and accurate sample data.
Also provide expected output.

E 12°55'05.25"
N 56°04'39.16"

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-10-17 : 09:30:34

i tried this

DECLARE Upload_Cursor CURSOR FOR
SELECT ccr_id
FROM d_dialler_v2.dbo.DIALLER_CDR_HISTORY
WHERE (ccr_id IS NULL)

OPEN Upload_Cursor

FETCH NEXT FROM Upload_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN

--My Routine Code Here
Where Current of ccr_id = 'a'

FETCH NEXT FROM Upload_Cursor
END

CLOSE Upload_Cursor
DEALLOCATE Upload_Cursor

but i just got a syntax error when i tried to apply the code. Also not sure what you meant by

Or better, rewrite whole code to set-based instead!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:39:03

If you are unsure of WHERE CURRENT OF does, please look in Books Online.

Something similar to this
UPDATE d_dialler_v2.dbo.DIALLER_CDR_HISTORY
SET Col1 = 'werwerwe'
WHERE CURRENT OF Upload_Cursor

Are you just trying to update the DIALLER_CDR_HISTORY table?
Rethink your logic and use set-based approach instead of CURSORs.

UPDATE d_dialler_v2.dbo.DIALLER_CDR_HISTORY
SET ccr_id = 'a'
WHERE ccr_id IS NULL

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:43:20

Please provide sample data is this format

DECLARE	@Sample TABLE (ID SMALLINT, Amount INT)

INSERT	@Sample
SELECT	2000, 1 UNION ALL
SELECT	2000, 2 UNION ALL
SELECT	2000, 3 UNION ALL
SELECT	2000, NULL UNION ALL
SELECT	2001, 6 UNION ALL
SELECT	2001, 7 UNION ALL
SELECT	2001, NULL UNION ALL
SELECT	2001, 9

SELECT * FROM @Sample

And then tell us how you want to modify the table.
If you want to be good at T-SQL, stay away from CURSORS as long as you can.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources