call a cursor in a procedure

Question

call a cursor in a procedure

BeginnerBug

Hall of Fame

Points: 3539
More actions
October 20, 2010 at 5:22 am

#149039

Hi...
how to call a cursor in a stored procedure.
any help pls...

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What do you mean by 'call a cursor'? A cursor isn't an object that you call, it's an object that you declare, use and then deallocate.

Why do you want a cursor in the first place?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

You don't. You DECLARE it, then use it, exactly the same as you might in an SSMS window.

Can you give us a few more details of what it is you are trying to do? Cursors are rarely appropriate.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 3

GilaMonster (10/20/2010)
What do you mean by 'call a cursor'? A cursor isn't an object that you call, it's an object that you declare, use and then deallocate.
Why do you want a cursor in the first place?

Hi..

within a procedure i m using following cursor... from a table i m taking every row and updating it to some other table..

DECLARECURSOR_UPDATE_LIVECURSOR FOR SELECT ACTUAL_LINE_ID,TEMP_LINE_ID,ATTRIBUTE1,ATTRIBUTE2,CONVERT(VARCHAR(11),LTRIM(RTRIM(ATTRIBUTE3)), 106),CONVERT(VARCHAR(11),LTRIM(RTRIM(ATTRIBUTE4)), 106),ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7 FROM TEMP_table WHERE ACTION_FLAG='U' AND SESSION_ID=@PBI_SESSION_ID AND TRN=@PBI_TRN
OPEN CURSOR_UPDATE_LIVE
FETCH NEXT FROM CURSOR_UPDATE_LIVE INTO @li_actual_line_id,@li_temp_line_id,@li_calendar_header_id,@lvc_period_name,@ld_from_date_1,@ld_to_date_1,@li_quarter_num ,@lvc_f_year ,@li_adjusting_period
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE GL_CALENDAR_LINES SETCAL_HEADER_ID=@PVC_CALENDAR_HEADER_ID,
PERIOD_NAME=@lvc_period_name,
FROM_DATE=@ld_from_date_1,
TO_DATE=@ld_to_date_1,
QUARTER_NUM=@li_quarter_num,
F_YEAR=@lvc_f_year,
ADJUSTING_PERIOD=@li_adjusting_period,
TRN=@PBI_TRN,
STATUS=1,
SESSION_ID=@PBI_SESSION_ID
WHERE CAL_LINE_ID=@li_actual_line_id
FETCH NEXT FROM CURSOR_UPDATE_LIVE INTO @li_actual_line_id,@li_temp_line_id,@li_calendar_header_id,@lvc_period_name,@ld_from_date_1,@ld_to_date_1,@li_quarter_num ,@lvc_f_year ,@li_adjusting_period
END
CLOSE CURSOR_UPDATE_LIVE
DEALLOCATE CURSOR_UPDATE_LIVE

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Reformatted to make that readable

DECLARE CURSOR_UPDATE_LIVE CURSOR

FOR

SELECT ACTUAL_LINE_ID, TEMP_LINE_ID, ATTRIBUTE1, ATTRIBUTE2,

CONVERT(VARCHAR(11), LTRIM(RTRIM(ATTRIBUTE3)), 106),

CONVERT(VARCHAR(11), LTRIM(RTRIM(ATTRIBUTE4)), 106), ATTRIBUTE5,

ATTRIBUTE6, ATTRIBUTE7

FROM TEMP_table

WHERE ACTION_FLAG = 'U'

AND SESSION_ID = @PBI_SESSION_ID

AND TRN = @PBI_TRN

OPEN CURSOR_UPDATE_LIVE

FETCH NEXT FROM CURSOR_UPDATE_LIVE INTO @li_actual_line_id, @li_temp_line_id,

@li_calendar_header_id, @lvc_period_name, @ld_from_date_1, @ld_to_date_1,

@li_quarter_num, @lvc_f_year, @li_adjusting_period

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE GL_CALENDAR_LINES

SET CAL_HEADER_ID = @PVC_CALENDAR_HEADER_ID,

PERIOD_NAME = @lvc_period_name, FROM_DATE = @ld_from_date_1,

TO_DATE = @ld_to_date_1, QUARTER_NUM = @li_quarter_num,

F_YEAR = @lvc_f_year, ADJUSTING_PERIOD = @li_adjusting_period,

TRN = @PBI_TRN, STATUS = 1, SESSION_ID = @PBI_SESSION_ID

WHERE CAL_LINE_ID = @li_actual_line_id

FETCH NEXT FROM CURSOR_UPDATE_LIVE INTO @li_actual_line_id,

@li_temp_line_id, @li_calendar_header_id, @lvc_period_name,

@ld_from_date_1, @ld_to_date_1, @li_quarter_num, @lvc_f_year,

@li_adjusting_period

END

CLOSE CURSOR_UPDATE_LIVE

DEALLOCATE CURSOR_UPDATE_LIVE

Honestly, I don't see anything in there that needs a cursor. A single update (Update ... from) should do the job.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

You don't need a cursor for this, it's a simple UPDATE...FROM:

UPDATE l SET

CAL_HEADER_ID= @PVC_CALENDAR_HEADER_ID,

PERIOD_NAME= t.ATTRIBUTE2, --@lvc_period_name,

FROM_DATE= CONVERT(VARCHAR(11),LTRIM(RTRIM(t.ATTRIBUTE3)), 106), --@ld_from_date_1,

TO_DATE= CONVERT(VARCHAR(11),LTRIM(RTRIM(t.ATTRIBUTE4)), 106), --@ld_to_date_1,

QUARTER_NUM= t.ATTRIBUTE5, --@li_quarter_num,

F_YEAR= t.ATTRIBUTE6, --@lvc_f_year,

ADJUSTING_PERIOD = t.ATTRIBUTE7, --@li_adjusting_period,

TRN= @PBI_TRN,

STATUS= 1,

SESSION_ID= @PBI_SESSION_ID

FROM GL_CALENDAR_LINES l

INNER JOIN TEMP_table t

ON t.ACTUAL_LINE_ID = l.CAL_LINE_ID

WHERE t.ACTION_FLAG = 'U'

AND t.SESSION_ID = @PBI_SESSION_ID

AND t.TRN=@PBI_TRN

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 6

how it possible?...

UPDATE GL_CALENDAR_LINES

SET CAL_HEADER_ID = @PVC_CALENDAR_HEADER_ID,

PERIOD_NAME = @lvc_period_name, FROM_DATE = @ld_from_date_1,

TO_DATE = @ld_to_date_1, QUARTER_NUM = @li_quarter_num,

F_YEAR = @lvc_f_year, ADJUSTING_PERIOD = @li_adjusting_period,

TRN = @PBI_TRN, STATUS = 1, SESSION_ID = @PBI_SESSION_ID

WHERE CAL_LINE_ID = @li_actual_line_id

since values comes from some other table.. how can u single update would be enough?..kindly can u pls explain ?

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 7

BeginnerBug

Hall of Fame

Points: 3539

October 21, 2010 at 4:47 am

#1240160

oh....Thanks a lot.... thanks Chris and gail....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

MonsterRocks (10/21/2010)
oh....Thanks a lot.... thanks Chris and gail....

Study the code, if there's anything you are unsure of, then please ask 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 9

ya...i implemented this... works fine... Thanks chris...otherwise i wud have done an ugly coding.. thanks gail shaw

WayneS SSC Guru Points: 95460 More actions · Answer 10

MonsterRocks (10/21/2010)
ya...i implemented this... works fine... Thanks chris...otherwise i wud have done an ugly coding.. thanks gail shaw

Do you understand the code that you implemented? If you can't explain to someone else what the code is doing, then please DON'T USE IT! Don't be afraid to ask questions here until you do understand it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2