alternative for using cursor

  • hi,

    i copy/pasted the actual code of the SP below.  can anyone translate it into something that does not use cursor?

    CREATE PROCEDURE TelstraData_Form @paramNumber varchar(30)

    AS

    declare @brief_description varchar(920)

    declare @number varchar(30)

    declare @work_order_no varchar(110)

    declare @work_order_no1 varchar(50)

    declare @work_order_no2 varchar(60)

    declare @incident_id varchar(30)

    declare @work_item_id varchar(60)

    declare @work_item_description varchar(210)

    declare @unit_cost float

    declare @work_item_quantity float

    declare @works_type varchar(60)

    declare @sc_addr1 varchar(100)

    declare @sc_addr2 varchar(100)

    declare @varDescription varchar(8000)

    declare @description varchar(2000)

    declare @category varchar(60)

    declare @request_phone varchar(60)

    declare @requested_by_first varchar(40)

    declare @requested_by_last varchar(90)

    DELETE tempo_telstraData

    DECLARE Quotation_Cursor CURSOR FOR

    SELECT     dbo.cdminvoicingm1.work_item_id, dbo.cdminvoicingm1.work_item_description, dbo.cdminvoicingm1.unit_cost,

                          dbo.cdminvoicingm1.work_item_quantity, dbo.cdminvoicingm1.works_type, dbo.cm3rm1.number, dbo.cm3rm1.category, dbo.cm3rm1.brief_description,

                          dbo.cm3rm2.work_order_no, dbo.cm3rm2.incident_id, dbo.cm3rm2.work_order_no1, dbo.cm3rm2.work_order_no2, dbo.cm3rm1.request_phone,

                          dbo.cm3rm1.requested_by_first, dbo.cm3rm1.requested_by_last

    --, dbo.cm3ra4.description

    FROM       dbo.cm3rm1 LEFT OUTER JOIN

               dbo.cdminvoicingm1 ON dbo.cm3rm1.number = dbo.cdminvoicingm1.change_number INNER JOIN

               dbo.cm3rm2 ON dbo.cm3rm1.number = dbo.cm3rm2.number

    --INNER JOIN dbo.cm3ra4 ON dbo.cm3rm1.number = dbo.cm3ra4.number

    WHERE     (dbo.cm3rm1.category = 'CDM Quote') AND

    --(dbo.cm3ra4.record_number = 1)

    cm3rm1.number = @paramNumber

    OPEN Quotation_Cursor

    FETCH NEXT FROM Quotation_Cursor INTO @work_item_id,@work_item_description,@unit_cost,@work_item_quantity,@works_type,@number,@category,@brief_description,

                                          @work_order_no,@incident_id,@work_order_no1,@work_order_no2,@request_phone,@requested_by_first,@requested_by_last

    WHILE @@FETCH_STATUS = 0

    BEGIN  

       SET @varDescription = ''

       DECLARE Description_Cursor CURSOR FOR

       SELECT dbo.cm3ra4.[description] FROM dbo.cm3ra4 WHERE dbo.cm3ra4.number = @number

       OPEN Description_Cursor

       FETCH NEXT FROM Description_Cursor INTO @description  

       WHILE @@FETCH_STATUS = 0

       BEGIN

          SET @varDescription = @varDescription + @description + char(13) + char(10)

          FETCH NEXT FROM Description_Cursor INTO @description     

       END

       INSERT INTO tempo_telstraData (work_item_id,work_item_description,unit_cost,work_item_quantity,works_type,number,category,brief_description,

                                      work_order_no,incident_id,work_order_no1,work_order_no2,request_phone,requested_by_first,requested_by_last,xdescription)

      VALUES (@work_item_id,@work_item_description,@unit_cost,@work_item_quantity,@works_type,@number,@category,@brief_description,

                                          @work_order_no,@incident_id,@work_order_no1,@work_order_no2,@request_phone,@requested_by_first,@requested_by_last,@varDescription)

       CLOSE Description_Cursor

       DEALLOCATE Description_Cursor

       FETCH NEXT FROM Quotation_Cursor INTO @work_item_id,@work_item_description,@unit_cost,@work_item_quantity,@works_type,@number,@category,@brief_description,

                                          @work_order_no,@incident_id,@work_order_no1,@work_order_no2,@request_phone,@requested_by_first,@requested_by_last

    END

    CLOSE Quotation_Cursor

    DEALLOCATE Quotation_Cursor

    select * from tempo_telstraData

    GO

    thanks

    ann

  • Could you post definitions of all tables involved and a description of what you are trying to achieve? Some sample data would also be helpful.

  • hi,

    pls have a look at this..u can do away with the cursor..

    CREATE PROCEDURE TelstraData_Form @paramNumber varchar(30)

    AS

    INSERT INTO tempo_telstraData (work_item_id,work_item_description,unit_cost,

    work_item_quantity,works_type,number,category,

    brief_description,

    work_order_no,incident_id,work_order_no1,work_order_no2,

    request_phone,requested_by_first,requested_by_last,xdescription)

    SELECT dbo.cdminvoicingm1.work_item_id,

    dbo.cdminvoicingm1.work_item_description,

    dbo.cdminvoicingm1.unit_cost,

    dbo.cdminvoicingm1.work_item_quantity,

    dbo.cdminvoicingm1.works_type,

    dbo.cm3rm1.number, dbo.cm3rm1.category,

    dbo.cm3rm1.brief_description,

    dbo.cm3rm2.work_order_no, dbo.cm3rm2.incident_id,

    dbo.cm3rm2.work_order_no1, dbo.cm3rm2.work_order_no2,

    dbo.cm3rm1.request_phone,

    dbo.cm3rm1.requested_by_first, dbo.cm3rm1.requested_by_last

    , dbo.cm3ra4.description

    FROM dbo.cm3rm1 LEFT OUTER JOIN

    dbo.cdminvoicingm1

    ON dbo.cm3rm1.number = dbo.cdminvoicingm1.change_number

    INNER JOIN dbo.cm3rm2

    ON dbo.cm3rm1.number = dbo.cm3rm2.number

    INNER JOIN dbo.cm3ra4 ON dbo.cm3rm1.number = dbo.cm3ra4.number

    WHERE (dbo.cm3rm1.category = 'CDM Quote') AND

    cm3rm1.number = @paramNumber

    i see the inner cursor is used just to retreive the descriptons..Rite? u can use that table within ur prior select statement and do away with the cursor...sorry, if there are any typo mistakes in my query becoz i didnt get a chance to execute it..Hope that helps

    Regards,

    Dilip

  • Provided your using sql2k you could create a function to return the concatenated descriptions and use it instead of the cursor

    Far away is close at hand in the images of elsewhere.
    Anon.

  • The description-concatenation function could be:

    CREATE FUNCTION dbo.ufnConcatDescription (@number as varchar(30))

    RETURNS varchar(8000)

    AS BEGIN

       DECLARE @varDescription varchar(8000)

       SET @varDescription = ''

       SELECT @varDescription = @varDescription + dbo.cm3ra4.[description] + char(13) + char(10)

       FROM dbo.cm3ra4 WHERE dbo.cm3ra4.number = @number

       { ORDER BY ??? }

       RETURN @varDescription

    END

    Then your whole proc can be replaced by:

    DELETE tempo_telstraData

    INSERT INTO tempo_telstraData (...)

    SELECT ..., dbo.ufnConcatDescription(number)

    FROM dbo.cm3rm1 ...

Viewing 5 posts - 1 through 4 (of 4 total)

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