Cursor Logic

19 Dec 2008 7:32 PM

Janet

I am working on writing my first SQL cursor. I've borrowed code from
a few existing cursors that are similar to what I need and I think I
am close but not quite there. I've included it below.

Basically, I need to fetch data row by row from
view_mdl_courseattendance and update the attend_status in
TranscriptDetail based on a match between the people_code_id,
academic_year, academic_term, academic_session, event_id,
event_sub_type, section in the view_mdl_courseattendance and the
transcriptdetail table.

Any help would be great.

----------------------------------------------------------------------------------------

CREATE PROCEDURE mdl_courseattendance
AS
    -- Add the parameters for the stored procedure here

DECLARE @PEOPLE_CODE_ID varchar
(10)
DECLARE @ACADEMIC_YEAR varchar
(4)
DECLARE @ACADEMIC_TERM varchar
(10)
DECLARE @ACADEMIC_SESSION varchar
(10)
DECLARE @EVENT_ID varchar
(10)
DECLARE @EVENT_SUB_TYPE varchar
(4)
DECLARE @SECTION varchar(4)

DECLARE @ATTEND_STATUS varchar(4)

--other PC variables

DECLARE @PB_DATE DATETIME
DECLARE @PB_TIME DATETIME
DECLARE @DATE_TEMP DATETIME
DECLARE @COUNT INT
DECLARE @CHARCOUNT CHAR(6)
DECLARE @PROCESSING_MSG VARCHAR(255)
DECLARE @PREVIOUS_DATE DATETIME
DECLARE @RECORD_COUNT INT
DECLARE @TEMP_ID VARCHAR(9)
DECLARE @PROCESS_KEYS VARCHAR(255)
DECLARE @ERROR VARCHAR(255)
DECLARE @OPID VARCHAR(8)
DECLARE @TERMINAL VARCHAR(4)
DECLARE @AFFILIATION_NAME VARCHAR(50)

SELECT @PREVIOUS_DATE=GETDATE()
SELECT @RECORD_COUNT = COUNT(*) FROM view_mdl_courseattendance

EXEC SP_GETPBDATE @PB_DATE OUTPUT
EXEC SP_GETPBTIME @PB_TIME OUTPUT

SELECT @COUNT = 0
SELECT @OPID = 'SCT'
SELECT @TERMINAL = '0001'

--calling the data from moodle
SET NOCOUNT ON

DECLARE c1 CURSOR FAST_FORWARD FOR
select
    people_code_id,
    academic_year,
    academic_term,
    academic_session,
    event_id,
    event_sub_type,
    section,
    attend_status
FROM view_mdl_courseattendance

OPEN c1
FETCH c1 INTO
    @people_code_id,
    @academic_year,
    @academic_term,
    @academic_session,
    @event_id,
    @event_sub_type,
    @section,
    @attend_status

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN

    SELECT @COUNT = @COUNT + 1
    SELECT @CHARCOUNT = CONVERT(CHAR(6),@COUNT)

    IF @COUNT %100 = 0
    BEGIN
      SELECT @PROCESSING_MSG = 'PROCESSING RECORD ' + @CHARCOUNT +
'OF: ' + CONVERT(VARCHAR,@RECORD_COUNT) + ' - INSERTING EVENT RECORDS
= ' +
         --+ @CHARCOUNT + ' OF ' +
         --CONVERT(VARCHAR,@RECORD_COUNT) + ' IN TRANSCRIPTDETAIL
TABLE, MINUTES = ' +
         CONVERT(CHAR,DATEDIFF(MI,@PREVIOUS_DATE,GETDATE()))
      RAISERROR (@PROCESSING_MSG,0,1) WITH NOWAIT
      SELECT @PREVIOUS_DATE = GETDATE()
    END

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- Insert statements for procedure here
select * from transcriptdetail where people_code_id = @people_code_id
and
           academic_year = @academic_year and academic_term =
@academic_term and
       academic_session = @academic_session and event_id = @event_id and
           event_sub_type = @event_sub_type and section = @section

Update transcriptdetail
set
attend_status = @attend_status
where people_code_id = @people_code_id and
           academic_year = @academic_year and academic_term =
@academic_term and
       academic_session = @academic_session and event_id = @event_id and
           event_sub_type = @event_sub_type and section = @section
END

CLOSE c1
DEALLOCATE c1

GO

19 Dec 2008 9:00 PM

Tom Moreau

The best help I can give you is not to use a cursor at all. Could you
please tell us, briefly, what the business requirement is? Cursors are
notorious performance killers.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Janet" <ckau***@gmail.com> wrote in message
news:fdc72122-c43c-4af3-b732-b6a45998ecdd@x14g2000yqk.googlegroups.com...

I am working on writing my first SQL cursor. I've borrowed code from
a few existing cursors that are similar to what I need and I think I
am close but not quite there. I've included it below.

Basically, I need to fetch data row by row from
view_mdl_courseattendance and update the attend_status in
TranscriptDetail based on a match between the people_code_id,
academic_year, academic_term, academic_session, event_id,
event_sub_type, section in the view_mdl_courseattendance and the
transcriptdetail table.

Any help would be great.

----------------------------------------------------------------------------------------

CREATE PROCEDURE mdl_courseattendance
AS
-- Add the parameters for the stored procedure here

DECLARE @PEOPLE_CODE_ID varchar
(10)
DECLARE @ACADEMIC_YEAR varchar
(4)
DECLARE @ACADEMIC_TERM varchar
(10)
DECLARE @ACADEMIC_SESSION varchar
(10)
DECLARE @EVENT_ID varchar
(10)
DECLARE @EVENT_SUB_TYPE varchar
(4)
DECLARE @SECTION varchar(4)

DECLARE @ATTEND_STATUS varchar(4)

--other PC variables

DECLARE @PB_DATE DATETIME
DECLARE @PB_TIME DATETIME
DECLARE @DATE_TEMP DATETIME
DECLARE @COUNT INT
DECLARE @CHARCOUNT CHAR(6)
DECLARE @PROCESSING_MSG VARCHAR(255)
DECLARE @PREVIOUS_DATE DATETIME
DECLARE @RECORD_COUNT INT
DECLARE @TEMP_ID VARCHAR(9)
DECLARE @PROCESS_KEYS VARCHAR(255)
DECLARE @ERROR VARCHAR(255)
DECLARE @OPID VARCHAR(8)
DECLARE @TERMINAL VARCHAR(4)
DECLARE @AFFILIATION_NAME VARCHAR(50)

SELECT @PREVIOUS_DATE=GETDATE()
SELECT @RECORD_COUNT = COUNT(*) FROM view_mdl_courseattendance

EXEC SP_GETPBDATE @PB_DATE OUTPUT
EXEC SP_GETPBTIME @PB_TIME OUTPUT

SELECT @COUNT = 0
SELECT @OPID = 'SCT'
SELECT @TERMINAL = '0001'

--calling the data from moodle
SET NOCOUNT ON

DECLARE c1 CURSOR FAST_FORWARD FOR
select
people_code_id,
academic_year,
academic_term,
academic_session,
event_id,
event_sub_type,
section,
attend_status
FROM view_mdl_courseattendance

OPEN c1
FETCH c1 INTO
@people_code_id,
@academic_year,
@academic_term,
@academic_session,
@event_id,
@event_sub_type,
@section,
@attend_status

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN

    SELECT @COUNT = @COUNT + 1
    SELECT @CHARCOUNT = CONVERT(CHAR(6),@COUNT)

    IF @COUNT %100 = 0
    BEGIN
      SELECT @PROCESSING_MSG = 'PROCESSING RECORD ' + @CHARCOUNT +
'OF: ' + CONVERT(VARCHAR,@RECORD_COUNT) + ' - INSERTING EVENT RECORDS
= ' +
         --+ @CHARCOUNT + ' OF ' +
         --CONVERT(VARCHAR,@RECORD_COUNT) + ' IN TRANSCRIPTDETAIL
TABLE, MINUTES = ' +
         CONVERT(CHAR,DATEDIFF(MI,@PREVIOUS_DATE,GETDATE()))
      RAISERROR (@PROCESSING_MSG,0,1) WITH NOWAIT
      SELECT @PREVIOUS_DATE = GETDATE()
    END

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

    -- Insert statements for procedure here
select * from transcriptdetail where people_code_id = @people_code_id
and
           academic_year = @academic_year and academic_term =
@academic_term and
   academic_session = @academic_session and event_id = @event_id and
           event_sub_type = @event_sub_type and section = @section

Update transcriptdetail
set
attend_status = @attend_status
where people_code_id = @people_code_id and
           academic_year = @academic_year and academic_term =
@academic_term and
   academic_session = @academic_session and event_id = @event_id and
           event_sub_type = @event_sub_type and section = @section
END

CLOSE c1
DEALLOCATE c1

GO

19 Dec 2008 10:11 PM

Janet

Thanks for responding. I know what you are saying. The process will
ultimately run as a nightly process, so I am not super concerned about
performance. I am happy to consider other options but since I have
lots of cursor examples readily available and my skills are limited, I
thought this to be the best option.

Any thoughts on just making the cursor work?

19 Dec 2008 10:33 PM

Tom Moreau

I won't tell you the right way to do a wrong thing. :-) Cursors are like
narcotics - they make you feel good, are easy to take, and ruin your life,
even though you think everything is fine. You will thank me later. You may
even want to check other production code that currently uses cursors to
eliminate those as well.

Let me give you a war story. I spoke about cursors at a conference. An
attendee wrote me 2 weeks later to give me an update statement and asked my
opinion on it. I noticed only one trivial change but other than that, it
looked fine. He said that it ran in 2 min. The cursor solution it replaced
took over 20 hours to run.

So...

What do you need the update to do? I'll be happy to craft a solution. :-)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Janet" <ckau***@gmail.com> wrote in message
news:76a45878-7265-4d9a-908a-6e1bdff8b212@n21g2000vba.googlegroups.com...

Thanks for responding. I know what you are saying. The process will
ultimately run as a nightly process, so I am not super concerned about
performance. I am happy to consider other options but since I have
lots of cursor examples readily available and my skills are limited, I
thought this to be the best option.

Any thoughts on just making the cursor work?

19 Dec 2008 10:57 PM

Janet

I know what you mean. Cursors are used very commonly in this
application, but I am open to other options!

Basically, I need to fetch data row by row from
db1.view_mdl_courseattendance and update the attend_status in
db2.TranscriptDetail based on a match between the people_code_id,
academic_year, academic_term, academic_session, event_id,
event_sub_type, section in the view_mdl_courseattendance and the
transcriptdetail table.

The business need is that instructors are inputting attendance
information (attend_status) into a different database. The data is
contained in a view called view_mdl_courseattendance. On a nightly
basis I want to run a stored procedure to take the data from
view_mdl_courseattendance and update the attend_status in
TranscriptDetail based on the matching records (based on the fields
above which will exist in both). Students will have records in both
databases and I need to update the attend_status from db1 to db2 so it
is the same as well.

Hope this helps. I'd love to see another way to do this. Thanks!

20 Dec 2008 1:19 AM

JRStern

Properly coded cursors don't have to be performance killers, but the basic
rule is still, if you're using a relational database, odds are excellent that
you do not need, and should not be using, a cursor at all. That's why SQL
was invented!

Josh

Show quoteHide quote

"Tom Moreau" wrote:

> The best help I can give you is not to use a cursor at all. Could you
> please tell us, briefly, what the business requirement is? Cursors are
> notorious performance killers.
>
> --
>    Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON   Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Janet" <ckau***@gmail.com> wrote in message
> news:fdc72122-c43c-4af3-b732-b6a45998ecdd@x14g2000yqk.googlegroups.com...
> I am working on writing my first SQL cursor. I've borrowed code from
> a few existing cursors that are similar to what I need and I think I
> am close but not quite there. I've included it below.
>
> Basically, I need to fetch data row by row from
> view_mdl_courseattendance and update the attend_status in
> TranscriptDetail based on a match between the people_code_id,
> academic_year, academic_term, academic_session, event_id,
> event_sub_type, section in the view_mdl_courseattendance and the
> transcriptdetail table.
>
> Any help would be great.
>
> ----------------------------------------------------------------------------------------
>
> CREATE PROCEDURE mdl_courseattendance
> AS
> -- Add the parameters for the stored procedure here
>
> DECLARE @PEOPLE_CODE_ID varchar
> (10)
> DECLARE @ACADEMIC_YEAR varchar
> (4)
> DECLARE @ACADEMIC_TERM varchar
> (10)
> DECLARE @ACADEMIC_SESSION varchar
> (10)
> DECLARE @EVENT_ID varchar
> (10)
> DECLARE @EVENT_SUB_TYPE varchar
> (4)
> DECLARE @SECTION varchar(4)
>
> DECLARE @ATTEND_STATUS varchar(4)
>
> --other PC variables
>
> DECLARE @PB_DATE DATETIME
> DECLARE @PB_TIME DATETIME
> DECLARE @DATE_TEMP DATETIME
> DECLARE @COUNT INT
> DECLARE @CHARCOUNT CHAR(6)
> DECLARE @PROCESSING_MSG VARCHAR(255)
> DECLARE @PREVIOUS_DATE DATETIME
> DECLARE @RECORD_COUNT INT
> DECLARE @TEMP_ID VARCHAR(9)
> DECLARE @PROCESS_KEYS VARCHAR(255)
> DECLARE @ERROR VARCHAR(255)
> DECLARE @OPID VARCHAR(8)
> DECLARE @TERMINAL VARCHAR(4)
> DECLARE @AFFILIATION_NAME VARCHAR(50)
>
> SELECT @PREVIOUS_DATE=GETDATE()
> SELECT @RECORD_COUNT = COUNT(*) FROM view_mdl_courseattendance
>
> EXEC SP_GETPBDATE @PB_DATE OUTPUT
> EXEC SP_GETPBTIME @PB_TIME OUTPUT
>
> SELECT @COUNT = 0
> SELECT @OPID = 'SCT'
> SELECT @TERMINAL = '0001'
>
> --calling the data from moodle
> SET NOCOUNT ON
>
> DECLARE c1 CURSOR FAST_FORWARD FOR
> select
> people_code_id,
> academic_year,
> academic_term,
> academic_session,
> event_id,
> event_sub_type,
> section,
> attend_status
> FROM view_mdl_courseattendance
>
> OPEN c1
> FETCH c1 INTO
> @people_code_id,
> @academic_year,
> @academic_term,
> @academic_session,
> @event_id,
> @event_sub_type,
> @section,
> @attend_status
>
> WHILE (@@FETCH_STATUS <> -1)
> BEGIN
>   IF (@@FETCH_STATUS <> -2)
>   BEGIN
>
>     SELECT @COUNT = @COUNT + 1
>     SELECT @CHARCOUNT = CONVERT(CHAR(6),@COUNT)
>
>     IF @COUNT %100 = 0
>     BEGIN
>       SELECT @PROCESSING_MSG = 'PROCESSING RECORD ' + @CHARCOUNT +
> 'OF: ' + CONVERT(VARCHAR,@RECORD_COUNT) + ' - INSERTING EVENT RECORDS
> = ' +
>          --+ @CHARCOUNT + ' OF ' +
>          --CONVERT(VARCHAR,@RECORD_COUNT) + ' IN TRANSCRIPTDETAIL
> TABLE, MINUTES = ' +
>          CONVERT(CHAR,DATEDIFF(MI,@PREVIOUS_DATE,GETDATE()))
>       RAISERROR (@PROCESSING_MSG,0,1) WITH NOWAIT
>       SELECT @PREVIOUS_DATE = GETDATE()
>     END
>
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON
>
>     -- Insert statements for procedure here
> select * from transcriptdetail where people_code_id = @people_code_id
> and
>            academic_year = @academic_year and academic_term =
> @academic_term and
>    academic_session = @academic_session and event_id = @event_id and
>            event_sub_type = @event_sub_type and section = @section
>
> Update transcriptdetail
> set
> attend_status = @attend_status
> where people_code_id = @people_code_id and
>            academic_year = @academic_year and academic_term =
> @academic_term and
>    academic_session = @academic_session and event_id = @event_id and
>            event_sub_type = @event_sub_type and section = @section
> END
>
> CLOSE c1
> DEALLOCATE c1
>
> GO
>
>

19 Dec 2008 11:09 PM

Hugo Kornelis

On Fri, 19 Dec 2008 11:32:31 -0800 (PST), Janet wrote:

>I am working on writing my first SQL cursor. I've borrowed code from
>a few existing cursors that are similar to what I need and I think I
>am close but not quite there. I've included it below.
>
>Basically, I need to fetch data row by row from
>view_mdl_courseattendance and update the attend_status in
>TranscriptDetail based on a match between the people_code_id,
>academic_year, academic_term, academic_session, event_id,
>event_sub_type, section in the view_mdl_courseattendance and the
>transcriptdetail table.
>
>Any help would be great.

Hi Janet,

I think you can replace the complete cursor based code with this single
statement:

SET NOCOUNT ON;
UPDATE     transcriptdetail
SET        attend_status              = ca.attend_status
FROM       transcriptdetail          AS td
INNER JOIN view_mdl_courseattendance AS ca
      ON   td.people_code_id          = ca.people_code_id
      AND td.academic_year           = ca.academic_year
      AND td.academic_term           = ca.academic_term
      AND td.academic_session        = ca.academic_session
      AND td.event_id                = ca.event_id
      AND td.event_sub_type          = ca.event_sub_type
      AND td.section                 = ca.section;
GO

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

20 Dec 2008 12:06 AM

Janet

Wow! So much simplier. That is working great!! Thank you so much.

20 Dec 2008 12:53 AM

Tom Moreau

Told ya so! ;-) Thanx, Hugo. I had stepped out for supper and a beer.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"Janet" <ckau***@gmail.com> wrote in message
news:302144ff-bf74-4a49-85cc-a5756c8900ed@e3g2000vbe.googlegroups.com...

Wow! So much simplier. That is working great!! Thank you so much.