Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

call a cursor in a procedure Expand / Collapse
Author
Message
Posted Wednesday, October 20, 2010 5:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
Hi...


how to call a cursor in a stored procedure.



any help pls...
Post #1007616
Posted Wednesday, October 20, 2010 5:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 40,200, Visits: 36,602
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 2008, MVP
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

Post #1007622
Posted Wednesday, October 20, 2010 5:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,799, Visits: 14,012
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
Post #1007624
Posted Thursday, October 21, 2010 4:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, 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
Post #1008282
Posted Thursday, October 21, 2010 4:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 40,200, Visits: 36,602
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 2008, MVP
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

Post #1008291
Posted Thursday, October 21, 2010 4:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,799, Visits: 14,012
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
Post #1008297
Posted Thursday, October 21, 2010 4:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, 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 ?
Post #1008299
Posted Thursday, October 21, 2010 4:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
oh....Thanks a lot.... thanks Chris and gail....
Post #1008302
Posted Thursday, October 21, 2010 4:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 6,799, Visits: 14,012
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1008306
Posted Thursday, October 21, 2010 5:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 4:32 AM
Points: 127, Visits: 350
ya...i implemented this... works fine... Thanks chris...otherwise i wud have done an ugly coding.. thanks gail shaw
Post #1008329
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse