Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Posts
    4

    Unanswered: SQL Server script just "spins"...

    Hi all,

    I'm new to SQL Server (my work experience is with PL/SQL), and I'm now putting together a little SQL course using SQL Server. I started a script to get the sum for each appointment (if greater than 0) from bill_detail (which may have multiple rows for each appointment), and populate the total in the appointment table (my code is below). However, when I run this, it just spins and spins, even though it doesn't indicate that there's any error. I left it for an hour and a half at one point and nothing happened. Inevitably I must quit running the query. I've made some tweaks but just can't get it to run. Any suggestions/insight into what I'm doing wrong would be greatly appreciated...

    Thanks!



    SET NOCOUNT ON;
    DECLARE @appt_id int;
    DECLARE @total smallmoney;
    --DECLARE @cur_appt as CURSOR;


    DECLARE cur_appt CURSOR
    STATIC FOR
    SELECT appointment_id from appointment;

    OPEN cur_appt;
    IF @@CURSOR_ROWS > 0
    BEGIN
    FETCH NEXT FROM cur_appt INTO @appt_id;
    WHILE @@Fetch_status = 0
    BEGIN

    set @total =
    (select sum(s.service_fee)
    from bill_detail b, service s
    where b.service_code = s.service_id
    and b.appointment_id = @appt_id);

    if @total > 0
    begin

    update appointment
    set bill_amount =
    (select sum(s.service_fee) from bill_detail b, service s
    where b.service_code = s.service_id
    and b.appointment_id = @appt_id);
    end

    END
    END
    CLOSE cur_appt;


    DEALLOCATE cur_appt;
    SET NOCOUNT OFF

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That is a pretty reasonable approach for PL/SQL but it will perform poorly in Microsoft SQL. I'd use:
    Code:
    UPDATE appointment 
       SET bill_amount = (SELECT Sum(s.service_fee)
          FROM bill_detail AS b
          INNER JOIN service AS s
             ON b.service_code = s.service_id
          WHERE b.appointment_id = appointment.appointment_id);
    PL/SQL needs cursors for almost everything. In Transact-SQL, I avoid cursors like the plague because of their negative impact on performance.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2008
    Posts
    4

    Thanks!

    Perfect, worked like a charm! Thanks so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •