cursors - 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

General SQL Server Forums

New to SQL Server Programming

cursors

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-28 : 18:03:32

Can I replace a cursor that has while and if logic inside using temp tables?

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.sp_UPDATE_ALPHA_INDEX_PARTY
@start_date datetime,
@end_date datetime
AS
set nocount on

DECLARE @instrument_id decimal(28, 0)
DECLARE @party_id decimal(28, 0)
DECLARE @party_data varchar(600)
DECLARE @last_inst_id decimal(28, 0)
DECLARE @sort_order int

TRUNCATE TABLE ALPHA_INDEX_PARTY_DATA

SET @sort_order = 0
SET @last_inst_id = 0

DECLARE main_Cursor CURSOR STATIC FOR
SELECT I.INSTRUMENT_ID, P.PARTY_ID, '('
+ CASE P.PARTY_TYPE WHEN 1 THEN 'D' WHEN 4 THEN 'I' ELSE 'N' END + ') '
+ P.LAST_NAME + CASE WHEN P.FIRST_NAME IS NOT NULL AND P.FIRST_NAME <> '' THEN ', ' + P.FIRST_NAME ELSE '' END
+ CASE WHEN P.MIDDLE_NAME IS NOT NULL AND P.MIDDLE_NAME <> '' THEN ' ' + P.MIDDLE_NAME ELSE '' END
+ CASE WHEN P.NAME_SUFFIX IS NOT NULL AND P.NAME_SUFFIX <> '' THEN ' ' + P.NAME_SUFFIX ELSE '' END AS PARTY_DATA
FROM INSTRUMENT I JOIN PARTY P ON I.INSTRUMENT_ID = P.INSTRUMENT_ID
WHERE I.RECORDING_DATE BETWEEN @start_date AND @end_date AND I.DELETED_FLAG <> 1

OPEN main_Cursor

FETCH NEXT FROM main_Cursor
INTO @instrument_id, @party_id, @party_data

WHILE @@FETCH_STATUS = 0
BEGIN
IF @instrument_id != @last_inst_id
BEGIN
SET @sort_order = 0
END

WHILE LEN(@party_data) > 0
BEGIN
SET @sort_order = @sort_order + 1
INSERT INTO ALPHA_INDEX_PARTY_DATA (INSTRUMENT_ID, PARTY_ID, SORT_ORDER, PARTY_DATA) VALUES (@instrument_id, @party_id, @sort_order, LEFT(@party_data, 36))
IF LEN(@party_data) > 36
BEGIN
SET @party_data = ' ' + LTRIM(RIGHT(@party_data, LEN(@party_data) - 36))
END
ELSE
BEGIN
SET @party_data = ''
END
END

SET @last_inst_id = @instrument_id

FETCH NEXT FROM main_Cursor
INTO @instrument_id, @party_id, @party_data
END

CLOSE main_Cursor
DEALLOCATE main_Cursor

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

http://www.sqlserverstudy.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-28 : 18:55:26

Maybe but without DDL for the tables, INSERT INTO statements for sample data, expected output using that sample data, and an explanation as to what it's doing, it's very hard to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-28 : 19:11:41

I have something like this.

------------------------------------------------------------------------
-- DROP TABLE TEMP1

SET NOCOUNT ON
TRUNCATE TABLE ALPHA_INDEX_PARTY_DATA

SELECT instrument_id
INTO INST_TEMP
FROM INSTRUMENT
WHERE RECORDING_DATE BETWEEN '2007-01-01' and '2007-12-31'
AND DELETED_FLAG <> 1

-- select count(*) from inst_temp
-- select top 1 * from inst_temp

SELECT I.INSTRUMENT_ID,
P.PARTY_ID, '(' + CASE P.PARTY_TYPE WHEN 1 THEN 'D' WHEN 4 THEN 'I' ELSE 'N' END + ') '
+ P.LAST_NAME + CASE WHEN P.FIRST_NAME IS NOT NULL AND P.FIRST_NAME <> '' THEN ', ' + P.FIRST_NAME ELSE '' END
+ CASE WHEN P.MIDDLE_NAME IS NOT NULL AND P.MIDDLE_NAME <> '' THEN ' ' + P.MIDDLE_NAME ELSE '' END
+ CASE WHEN P.NAME_SUFFIX IS NOT NULL AND P.NAME_SUFFIX <> '' THEN ' ' + P.NAME_SUFFIX ELSE '' END AS PARTY_DATA
INTO TEMP1
FROM INST_TEMP I
JOIN PARTY P
ON I.INSTRUMENT_ID = P.INSTRUMENT_ID

-- select top 10 * from temp1

/*
select instrument_id, party_id,
sort_order, len(party_data) as Length, party_data
from GG_ATRECORDING_QAT..ALPHA_INDEX_PARTY_DATA
order by length
*/

INSERT ALPHA_INDEX_PARTY_DATA
SELECT instrument_id, party_id, 1, LEFT(party_data, 36)
FROM TEMP1
WHERE LEN(PARTY_DATA) > 0

INSERT ALPHA_INDEX_PARTY_DATA
SELECT instrument_id, party_id, 2, ' ' + LTRIM(RIGHT(party_data, LEN(party_data) - 36)),
RIGHT(party_data, LEN(party_data) - 36), LEN(party_data) - 36, PARTY_DATA, LEN(PARTY_DATA)
FROM TEMP1
WHERE LEN(PARTY_DATA) > 36

http://www.sqlserverstudy.com

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-28 : 19:46:18

I don't really know what it's doing as I was requested to make it faster. But looks like it's getting records from instrument and party tables.

http://www.sqlserverstudy.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-28 : 21:14:23

We can't really help if you don't even have a firm grasp on it plus you didn't provide any of the requested information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-28 : 22:06:28

I'm not looking for an answer I just wanna find out if i could replace the cursor that has the while loop and if statements.

http://www.sqlserverstudy.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-29 : 02:02:10

And the answer is maybe. We can't answer the riddle unless you provide more information.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-31 : 12:44:36

got some data.

Party table
-----------------------------------------
instrument_id party_id last_name first_name middle_name name_suffix
3196655 8329324 CLUBB STACY L
3318918 10350608 CROOKE,BESSIE J EST
3318918 10350858 JACKSON LLOYD D

output
-------------------
instrument_id party_id sort_order party_data
3196655 8329324 1 (I) CLUBB, STACY L
3318918 10350608 2 (I) CROOKE,BESSIE J EST
3318918 10350858 3 (I) JACKSON, LLOYD D

http://www.sqlserverstudy.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-31 : 13:51:57

I'll qualify my answer by saying that I never use cursors. But what are cursors? They are basically a looping mechanism. So, I cannot imagine a scenario where you cannot replace a cursor with a while loop. So, I think they short answer is: Yes. You can replace a cursor with a while loop.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-31 : 14:45:17

There is no point to replacing a cursor with a while loop, but I don't think that's what he is asking to do. My understanding is that he wants to lose the looping mechanism altogether and perhaps use temp tables instead.

But who knows given the information that he has provided.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-31 : 15:21:20

Ahh, now I see. funketekun, can you provide a description of what you are trying to achomplish in words?

Just going by your first sample bit of code I put this together, but I'm not sure exactly what you are trying to do:

INSERT 
	ALPHA_INDEX_PARTY_DATA 
	(
		INSTRUMENT_ID, 
		PARTY_ID, 
		SORT_ORDER, 
		PARTY_DATA
	) 
SELECT 
	I.INSTRUMENT_ID, 
	P.PARTY_ID, 
	ROW_NUMBER() OVER(PARTITION BY I.INSTRUMENT_ID ORDER BY P.PARTY_ID) AS RowNum,
	LEFT
	(	'(' 
		+ CASE P.PARTY_TYPE WHEN 1 THEN 'D' WHEN 4 THEN 'I' ELSE 'N' END + ') '
		+ P.LAST_NAME 
		+ CASE WHEN P.FIRST_NAME IS NOT NULL AND P.FIRST_NAME <> '' THEN ', ' + P.FIRST_NAME ELSE '' END 
		+ CASE WHEN P.MIDDLE_NAME IS NOT NULL AND P.MIDDLE_NAME <> '' THEN ' ' + P.MIDDLE_NAME ELSE '' END 
		+ CASE WHEN P.NAME_SUFFIX IS NOT NULL AND P.NAME_SUFFIX <> '' THEN ' ' + P.NAME_SUFFIX ELSE '' END
	,36) AS PARTY_DATA
FROM 
	INSTRUMENT I 
JOIN 
	PARTY P 
	ON I.INSTRUMENT_ID = P.INSTRUMENT_ID
WHERE 
	I.RECORDING_DATE BETWEEN @start_date AND @end_date 
	AND I.DELETED_FLAG <> 1

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-31 : 16:15:03

what i want to do is performance tuning.

http://www.sqlserverstudy.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-31 : 17:09:59

It's like pulling teeth. Perhaps he doesn't want help after all.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-31 : 17:29:41

i just want to know how i could eliminate the cursor. its not a hard question for experts....

http://www.sqlserverstudy.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-31 : 17:45:09

See my post on 15:21:20. Cursor eliminated.

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-03-31 : 18:43:39

Server: Msg 195, Level 15, State 10, Line 12
'ROW_NUMBER' is not a recognized function name.

http://www.sqlserverstudy.com

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-01 : 01:12:58

Seems like it can't be done.
I wonder if there is any expert here.

http://www.sqlserverstudy.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 13:35:48

You are too funny.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-04-01 : 13:40:39

quote:
Originally posted by funketekun

Seems like it can't be done.
I wonder if there is any expert here.

Does anybody else get tired of these type questions? You know, the ones that say:

quote:
I have this question. I'm not going to give you any information about the data, the schema, or the expected results. I'm also not going to answer any follow-up questions you may have, although I may respond with other nonsensical posts that add nothing to the discussion. Can you help me?

And then later:

quote:
Isn't there an expert here who can help me? You guys must all be dummies, cuz you haven't answered me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 13:45:50

We are used to it. Funketekun (and his other alias gongxia649) have done this numerous times the past years...

Look here what was posted 07/12/2007 : 10:06:59
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69568&whichpage=29

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

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-01 : 16:07:45

i gave u all u want..what else do u want.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources