SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


call a cursor in a procedure


call a cursor in a procedure

Author
Message
BeginnerBug
BeginnerBug
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1359 Visits: 350
Hi...


how to call a cursor in a stored procedure.



any help pls...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)

Group: General Forum Members
Points: 369855 Visits: 46948
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
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65776 Visits: 20214
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
Exploring Recursive CTEs by Example Dwain Camps
BeginnerBug
BeginnerBug
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1359 Visits: 350
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..
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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)SSC Guru (369K reputation)

Group: General Forum Members
Points: 369855 Visits: 46948
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
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65776 Visits: 20214
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
Exploring Recursive CTEs by Example Dwain Camps
BeginnerBug
BeginnerBug
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1359 Visits: 350
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
BeginnerBug
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1359 Visits: 350
oh....Thanks a lot.... thanks Chris and gail....
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65776 Visits: 20214
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 Smile

“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
Exploring Recursive CTEs by Example Dwain Camps
BeginnerBug
BeginnerBug
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1359 Visits: 350
ya...i implemented this... works fine... Thanks chris...otherwise i wud have done an ugly coding.. thanks gail shaw
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search