By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,033 Members | 1,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,033 IT Pros & Developers. It's quick & easy.

Stored Procedure Cursor is slow

P: 1
Expand|Select|Wrap|Line Numbers
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. ALTER PROCEDURE [dbo].[QUOTE_LIST]
  6.  
  7. AS
  8. declare 
  9.     @QUOTEHEADERID Integer,
  10.     @QUOTEHEADERSYSTEMID Integer,
  11.     @INSURANCEID Integer,
  12.     @CUSTOMERID Integer,
  13.     @VEHICLEID Integer,
  14.     @QUH_CIN_ID Integer,
  15.     @QUOTENUMBER Varchar(20),
  16.     @POLICYNO Varchar(30),
  17.     @TAGNO Varchar(25),
  18.     @EXCESS Double precision,
  19.     @OWNERCONTRIBUTION Double precision,
  20.     @DRIVER Varchar(200),
  21.     @DRIVERLICENSE Varchar(30),
  22.     @DRIVERPHONE Varchar(15),
  23.     @ACTIVE1 Char(1),
  24.     @DELETED Char(1),
  25.     @CREATEDON Datetime,
  26.     @UPDATEDON Datetime,
  27.     @CREATEDBY Integer,
  28.     @UPDATEDBY Integer,
  29.     @QUOTEBODYID Integer,   
  30.     @CREDITINVOICEID Integer,
  31.     @JOBID Integer,
  32.     @EMPID Integer,
  33.     @CLAIMTYPEID Integer,
  34.     @NUMBER Integer,
  35.     @LOCKED Char(1),
  36.     @CLAIMNO Varchar(30),
  37.     @ORDERNO Varchar(30),
  38.     @RATEID Integer,
  39.     @DATEASSESSED Datetime,
  40.     @DATE1 Datetime,    
  41.     @DATEAUTHORISED Datetime,    
  42.     @ACTUALIN Datetime,
  43.     @current_Datetime Datetime,
  44.     @ACTUALOUT Datetime,
  45.     @EXPECTEDIN Datetime,
  46.     @EXPECTEDOUT Datetime,
  47.     @QUB_ACTIVE Char(1),
  48.     @QUB_DELETED Char(1),
  49.     @JOBNO Varchar(20),
  50.     @COMPANYNAME Varchar(50),
  51.     @FIRSTNAME Varchar(40),
  52.     @LASTNAME Varchar(40),
  53.     @CUSTOMER Varchar(100),
  54.     @CUSTOMERDIGITSONLYPHONENOONE Varchar(15),
  55.     @CUSTOMERDIGITSONLYPHONENOTWO Varchar(15),
  56.     @CUSTOMERDIGITSONLYMOBILE Varchar(15),
  57.     @CUSTOMEREMAIL Varchar(50),
  58.     @MAKEID Integer,
  59.     @MODELID Integer,
  60.     @REGONO Varchar(20),
  61.     @VIN Varchar(50),
  62.     @MAKE Varchar(30),
  63.     @MODEL Varchar(100),
  64.     @COLOUR Varchar(100),
  65.     @INSURANCE Varchar(50),
  66.     @GATEWAYID Integer,
  67.     @INSURANCEDIGITSONLYPHONENOONE Varchar(15),
  68.     @INSURANCEDIGITSONLYPHONENOTWO Varchar(15),
  69.     @EXCESSINVOICENUMBER Varchar(20),
  70.     @QUOTEINVOICENUMBER Varchar(500),
  71.     @QUOTEINVOICEDATE Datetime,
  72.     @ASSESSINGCOMPANY Varchar(50),
  73.     @ASSESSOR Varchar(30),
  74.     @ASSESSOR1 Varchar(30),
  75.     @ASSESSORPHONENO1 Varchar(15),
  76.     @ASSESSORPHONENO2 Varchar(15),
  77.     @ASSESSORMOBILE Varchar(15),    
  78.     @JPS_JPC_ID Integer,
  79.     @JOBPROCESSSTATUS Varchar(50),
  80.     @CREATEDBYUSER Varchar(20),
  81.     @UPDATEDBYUSER Varchar(20),
  82.     @ISINVOICED Char(1),
  83.     @ISUNINVOICED Char(1),
  84.     @ISINPROGRESS Char(1),
  85.     @ISBOOKEDIN Char(1),
  86.     @ISNOTBOOKEDIN Char(1),
  87.     @ISPAID Char(1),
  88.     @HAVEADDITIONAL Char(1),
  89.     @ISINPROGRESSINVOICE Char(1),
  90.     @QUH_IS_AUDATEX Char(1),
  91.     @EMP_FIRST_NAME Varchar(50),
  92.     @EMP_LAST_NAME Varchar(50) 
  93.  
  94.  
  95.  
  96. declare @Result table (
  97.     QUOTEHEADERID Integer,
  98.     QUOTEHEADERSYSTEMID Integer,
  99.     INSURANCEID Integer,
  100.     CUSTOMERID Integer,
  101.     VEHICLEID Integer,
  102.     QUH_CIN_ID Integer,
  103.     QUOTENUMBER Varchar(20),
  104.     POLICYNO Varchar(30),
  105.     TAGNO Varchar(25),
  106.     EXCESS Double precision,
  107.     OWNERCONTRIBUTION Double precision,
  108.     DRIVER Varchar(200),
  109.     DRIVERLICENSE Varchar(30),
  110.     DRIVERPHONE Varchar(15),
  111.     ACTIVE1 Char(1),
  112.     DELETED Char(1),
  113.     CREATEDON Datetime,
  114.     UPDATEDON Datetime,
  115.     CREATEDBY Integer,
  116.     UPDATEDBY Integer,
  117.     QUOTEBODYID Integer,    
  118.     CREDITINVOICEID Integer,
  119.     JOBID Integer,
  120.     EMPID Integer,
  121.     CLAIMTYPEID Integer,
  122.     NUMBER Integer,
  123.     LOCKED Char(1),
  124.     CLAIMNO Varchar(30),
  125.     ORDERNO Varchar(30),
  126.     RATEID Integer,
  127.     DATEASSESSED Datetime,
  128.     DATE1 Datetime,
  129.     DATEAUTHORISED Datetime,    
  130.     ACTUALIN Datetime,
  131.     current_Datetime Datetime,
  132.     ACTUALOUT Datetime,
  133.     EXPECTEDIN Datetime,
  134.     EXPECTEDOUT Datetime,
  135.     QUB_ACTIVE Char(1),
  136.     QUB_DELETED Char(1),
  137.     JOBNO Varchar(20),
  138.     COMPANYNAME Varchar(50),
  139.     FIRSTNAME Varchar(40),
  140.     LASTNAME Varchar(40),
  141.     CUSTOMER Varchar(100),
  142.     CUSTOMERDIGITSONLYPHONENOONE Varchar(15),
  143.     CUSTOMERDIGITSONLYPHONENOTWO Varchar(15),
  144.     CUSTOMERDIGITSONLYMOBILE Varchar(15),
  145.     CUSTOMEREMAIL Varchar(50),
  146.     MAKEID Integer,
  147.     MODELID Integer,
  148.     REGONO Varchar(20),
  149.     VIN Varchar(50),
  150.     MAKE Varchar(30),
  151.     MODEL Varchar(100),
  152.     COLOUR Varchar(100),
  153.     INSURANCE Varchar(50),
  154.     GATEWAYID Integer,
  155.     INSURANCEDIGITSONLYPHONENOONE Varchar(15),
  156.     INSURANCEDIGITSONLYPHONENOTWO Varchar(15),
  157.     EXCESSINVOICENUMBER Varchar(20),
  158.     QUOTEINVOICENUMBER Varchar(500),
  159.     QUOTEINVOICEDATE Datetime,
  160.     ASSESSINGCOMPANY Varchar(50),
  161.     ASSESSOR Varchar(30),
  162.     ASSESSOR1 Varchar(30),
  163.     ASSESSORPHONENO1 Varchar(15),
  164.     ASSESSORPHONENO2 Varchar(15),
  165.     ASSESSORMOBILE Varchar(15),
  166.  
  167.     JPS_JPC_ID Integer,
  168.     JOBPROCESSSTATUS Varchar(50),
  169.     CREATEDBYUSER Varchar(20),
  170.     UPDATEDBYUSER Varchar(20),
  171.     ISINVOICED Char(1),
  172.     ISUNINVOICED Char(1),
  173.     ISINPROGRESS Char(1),
  174.     ISBOOKEDIN Char(1),
  175.     ISNOTBOOKEDIN Char(1),
  176.     ISPAID Char(1),
  177.     HAVEADDITIONAL Char(1),
  178.     ISINPROGRESSINVOICE Char(1),
  179.     QUH_IS_AUDATEX Char(1),
  180.     EMP_FIRST_NAME Varchar(50),
  181.     EMP_LAST_NAME Varchar(50) )
  182.  
  183.  
  184.     declare @TOTAL_INVOICE Integer, 
  185.         @TOTAL_BODY Integer, 
  186.         @TOTAL_PAID_INVOICE Integer,
  187.         @CIN_NUMBER Varchar(20),
  188.         @CIN_DATE datetime
  189.         BEGIN 
  190.  
  191.         DECLARE temp_cus cursor LOCAL READ_ONLY FORWARD_ONLY FOR SELECT QUH_ID, QUH_SYSTEM_ID, QUH_INS_ID, QUH_CUS_ID, QUH_QUV_ID, QUH_CIN_ID, QUH_NUMBER, QUH_POLICY_NO, 
  192.             QUH_TAG_NO, QUH_EXCESS, QUH_OWNER_CONTRIBUTION, QUH_DRIVER, QUH_DRIVER_LICENSE, QUH_DRIVER_PHONE, 
  193.             QUH_ACTIVE, QUH_DELETED, QUH_CREATED_ON, QUH_UPDATED_ON, QUH_CREATED_BY, QUH_UPDATED_BY,
  194.  
  195.             QUB_ID, QUB_CIN_ID, QUB_JOB_ID, QUB_EMP_ID, QUB_CLT_ID, QUB_NUMBER, QUB_LOCKED, QUB_CLAIM_NO, QUB_ORDER_NO, QB.QUB_RAT_ID, QUB_DATE_ASSESSED,
  196.             QUB_DATE, QUB_DATE_AUTHORISED, QUB_ACTUAL_IN, QUB_ACTUAL_OUT, QUB_EXPECTED_IN, QUB_EXPECTED_OUT, QUB_ACTIVE, QUB_DELETED, JOB_NUMBER
  197.             ,@CURRENT_DATETIME,
  198.             CUS_COMPANY_NAME, CUS_FIRST_NAME, CUS_LAST_NAME, CUS_PHONE_NO1, CUS_PHONE_NO2, CUS_MOBILE, CUS_EMAIL, 
  199.  
  200.             INS_NAME, INS_GWY_ID, INS_PHONE_NO1, INS_PHONE_NO2, 
  201.             ASC_COMPANY_NAME, ASS_FIRST_NAME, ASS_LAST_NAME, ASS_PHONE_NO1, ASS_PHONE_NO2, ASS_MOBILE, QUH_IS_AUDATEX, EMP_FIRST_NAME, EMP_LAST_NAME
  202.  
  203.             FROM QUOTE_HEADER 
  204.             LEFT OUTER JOIN CUSTOMER  ON (CUS_ID = QUH_CUS_ID)            
  205.             LEFT OUTER JOIN INSURANCE ON (INS_ID = QUH_INS_ID)
  206.             LEFT OUTER JOIN QUOTE_BODY QB ON (QUB_QUH_ID = QUH_ID) 
  207.             LEFT OUTER JOIN EMPLOYEE ON (EMP_ID = QUB_EMP_ID)
  208.             LEFT OUTER JOIN JOB ON (JOB_ID = QUB_JOB_ID)
  209.             LEFT OUTER JOIN ASSESSING_COMPANY ON (ASC_ID = QUB_ASC_ID) 
  210.             LEFT OUTER JOIN ASSESSOR ASS ON (ASS_ID = QUB_ASS_ID) 
  211.  
  212.             WHERE QUH_ARCHIVED = 0 AND QUB_NUMBER = 0 and QUH_ID > 0 
  213.  
  214.     OPEN temp_cus    
  215.     Fetch next From temp_cus INTO @QuoteHeaderID, @QuoteHeaderSystemID, @InsuranceID, @CustomerID, @VehicleID, @QUH_CIN_ID, @QuoteNumber, @PolicyNo, @TagNo, 
  216.             @Excess, @OwnerContribution, @Driver, @DriverLicense, @DriverPhone, 
  217.             @Active1, @Deleted, @CreatedOn, @UpdatedOn, @CreatedBy, @UpdatedBy,
  218.  
  219.             @QuoteBodyID, @CreditInvoiceID, @JobID, @EmpID, @ClaimTypeID, @Number, @Locked, @ClaimNo, @OrderNo, @RateID, @DateAssessed,
  220.             @Date1, @DateAuthorised, @ActualIn, @ActualOut, @ExpectedIn, @ExpectedOut, @QUB_ACTIVE, @QUB_DELETED, @JobNo,@current_Datetime,
  221.  
  222.             @CompanyName, @FirstName, @LastName, @CustomerDigitsOnlyPhoneNoOne, @CustomerDigitsOnlyPhoneNoTwo, @CustomerDigitsOnlyMobile, @CustomerEmail, 
  223.  
  224.             @Insurance, @GatewayID, @InsuranceDigitsOnlyPhoneNoOne, @InsuranceDigitsOnlyPhoneNoTwo,
  225.             @AssessingCompany, @Assessor, @Assessor1, @AssessorPhoneNo1, @AssessorPhoneNo2, @AssessorMobile, @QUH_IS_AUDATEX, @EMP_FIRST_NAME, @EMP_LAST_NAME
  226.             WHILE @@FETCH_STATUS = 0 BEGIN 
  227.  
  228.             SET @TOTAL_INVOICE=0;          
  229.             SET @TOTAL_BODY = 0; 
  230.             SET @TOTAL_PAID_INVOICE = 0; 
  231.  
  232.  
  233.  
  234.              SELECT @MAKEID = QUV_VEH_ID, @MODELID = QUV_VEM_ID, @REGONO = QUV_REGO_NO, @VIN = QUV_VIN,
  235.               @MAKE = VEH_NAME, @MODEL = VEM_NAME, @COLOUR = VCR_NAME
  236.                   FROM QUOTE_VEHICLE 
  237.                     LEFT OUTER JOIN VEHICLE_MAKE ON (VEH_ID= QUV_VEH_ID) 
  238.                     LEFT OUTER JOIN VEHICLE_MODEL ON (VEM_ID = QUV_VEM_ID)
  239.                     LEFT OUTER JOIN VEHICLE_COLOUR ON (VCR_ID = QUV_VCR_ID)
  240.                       WHERE QUV_ID = @VehicleID
  241.  
  242.                 SET @ISINPROGRESSINVOICE = 0;        
  243.                 SET @QUOTEINVOICENUMBER=''; 
  244.                 SET @CIN_NUMBER ='';
  245.                 SET @EXCESSINVOICENUMBER=''; 
  246.                 SET @HAVEADDITIONAL = 0;
  247.               SET @ISUNINVOICED = 0;
  248.               SET  @ISBOOKEDIN = 0; 
  249.               SET  @ISNOTBOOKEDIN = 0;
  250.               SET @ISINPROGRESS = 0;
  251.               SET @ISPAID = 0;
  252.               SET  @ISINVOICED = 0; 
  253.               SET @ISNOTBOOKEDIN = 0;
  254.  
  255.               SET @TOTAL_BODY = (SELECT COUNT(QUB_ID) FROM QUOTE_BODY QB WHERE QB.QUB_ACTIVE = 1 AND 
  256.                     QB.QUB_DELETED = 0 AND QUB_QUH_ID = @QUOTEHEADERID);  
  257.                     IF(@TOTAL_BODY > 1)  BEGIN
  258.                     SET @HAVEADDITIONAL = 1;
  259.                 END
  260.  
  261.                  IF(@QUH_CIN_ID != 0 AND @QUH_CIN_ID IS NOT NULL)  BEGIN
  262.                 SELECT @EXCESSINVOICENUMBER = CIN_NUMBER FROM CREDIT_INVOICE WHERE CIN_ID = @QUH_CIN_ID;
  263.                  END
  264.  
  265.             SELECT @JPS_JPC_ID = JPS_JPC_ID, @JOBPROCESSSTATUS = JPC_NAME FROM JOB_PROCESS LEFT OUTER JOIN JOB_PROCESS_CATEGORY JPC ON (JPS_JPC_ID = JPC_ID) 
  266.                 WHERE JPS_ID = (SELECT MAX(JP.JPS_ID) FROM JOB_PROCESS JP WHERE JP.JPS_JOB_ID = @JobID AND
  267.                 JP.JPS_ACTIVE = 1 AND JP.JPS_DELETED = 0)  
  268.  
  269.                 SET @CreatedByUser = (SELECT USR_USERNAME FROM VQS_USER WHERE USR_ID = @CreatedBy);  
  270.  
  271.                 SET @UpdatedByUser = (SELECT USR_USERNAME FROM VQS_USER WHERE USR_ID = @UpdatedBy);  
  272.  
  273.  
  274.                 IF(@COMPANYNAME IS NOT NULL)  BEGIN
  275.                    SET @CUSTOMER = @COMPANYNAME;   
  276.                 END
  277.              ELSE 
  278.                 BEGIN
  279.                   -- SET @CUSTOMER = @FIRSTNAME || ' ' || @LASTNAME;         
  280.                    SET @CUSTOMER = @FIRSTNAME;         
  281.                 END
  282.                 IF(@CreditInvoiceID != 0 AND @CreditInvoiceID IS NOT NULL)  BEGIN 
  283.  
  284.  
  285.  
  286.  
  287.                 DECLARE temp_cus1 cursor LOCAL READ_ONLY FORWARD_ONLY FOR SELECT CIN_NUMBER, CIN_DATE FROM QUOTE_BODY LEFT OUTER JOIN CREDIT_INVOICE  ON(QUB_CIN_ID = CIN_ID) 
  288.                 WHERE QUB_DELETED = 0 AND QUB_QUH_ID = @QuoteHeaderID AND CIN_ACTIVE = 1 AND CIN_DELETED = 0
  289.                 GROUP BY CIN_NUMBER, CIN_DATE
  290.                 OPEN temp_cus1    
  291.                 Fetch next From temp_cus1 INTO @CIN_NUMBER, @CIN_DATE
  292.                 WHILE @@FETCH_STATUS = 0  
  293.                 BEGIN 
  294.  
  295.                         IF(@CIN_NUMBER IS NOT NULL)  BEGIN
  296.  
  297.                             IF(@QuoteInvoiceNumber != '')  BEGIN
  298.                                 --SET QuoteInvoiceNumber =  QuoteInvoiceNumber ||','|| @CIN_NUMBER;
  299.                                 SET @QuoteInvoiceNumber =  @QuoteInvoiceNumber;
  300.                             END
  301.  
  302.                             ELSE 
  303.                                 SET @QuoteInvoiceNumber = @CIN_NUMBER;
  304.                                 SET @QuoteInvoiceDate = @CIN_DATE;
  305.                         END
  306.                 Fetch next From temp_cus1 INTO @CIN_NUMBER, @CIN_DATE
  307.                 END
  308.                 Close temp_cus1;
  309.                 Deallocate temp_cus1;
  310.                  SELECT @TOTAL_INVOICE = COUNT(QUB_ID) FROM QUOTE_BODY QB WHERE QB.QUB_ACTIVE = 1 AND 
  311.                         QB.QUB_DELETED = 0 AND QUB_QUH_ID = @QuoteHeaderID AND QUB_CIN_ID != 0;
  312.  
  313.  
  314.  
  315.  
  316.                     IF(@TOTAL_BODY = @TOTAL_INVOICE)  BEGIN
  317.                                 SET @ISInvoiced = 1;
  318.  
  319.                         IF (@ActualIn IS NOT NULL AND @ActualOut IS NULL)  BEGIN 
  320.                             SET @ISInProgressInvoice = 1; 
  321.                             SET @ISInvoiced = 0;
  322.                         END 
  323.                         SET @TOTAL_PAID_INVOICE = (SELECT COUNT(QUB_CIN_ID) FROM QUOTE_BODY LEFT OUTER JOIN CREDIT_INVOICE  ON(QUB_CIN_ID = CIN_ID) 
  324.                             WHERE QUB_DELETED = 0 AND QUB_QUH_ID = @QuoteHeaderID AND CIN_ACTIVE = 1 AND
  325.                             CIN_DELETED = 0 AND CIN_PAID_STATUS = 'CLOSED');
  326.  
  327.                             IF(@TOTAL_PAID_INVOICE = @TOTAL_INVOICE)  BEGIN
  328.                               SET  @ISPaid = 1; 
  329.                               SET @ISInvoiced  = 0;
  330.                               SET  @ISInProgress = 0;
  331.                             END
  332.                      END
  333.  
  334.                      ELSE IF (@ISPaid = 0 AND @ISInvoiced = 0)  BEGIN
  335.  
  336.                             SET @TOTAL_BODY = 0;
  337.  
  338.                              SELECT @TOTAL_BODY = COUNT(QUB_ID) FROM QUOTE_BODY WHERE QUB_ACTIVE = 1 AND 
  339.                                 QUB_DELETED = 0 AND QUB_QUH_ID = @QuoteHeaderID AND QUB_CIN_ID = 0 AND QUB_NUMBER != 0;
  340.  
  341.                             IF(@TOTAL_BODY != 0 AND @CreditInvoiceID != 0)  BEGIN
  342.                                 SET @ISUnInvoiced =  1;
  343.  
  344.                                 END     
  345.  
  346.                             ELSE IF (@ActualIn IS NOT NULL AND @current_Datetime >= @ActualIn AND
  347.                                     @ActualOut IS NULL)  BEGIN
  348.                                  SET @ISInProgress = 1;
  349.                             END
  350.  
  351.                             ELSE IF (@ExpectedIn IS NOT NULL AND (@ActualIn IS NULL OR (@ActualIn IS NOT NULL
  352.                                     AND @ActualIn > @current_Datetime)))  BEGIN
  353.                                     SET @ISBookedIn = 1;
  354.  
  355.                             END
  356.  
  357.                             ELSE IF (@ExpectedIn IS NULL)  BEGIN
  358.                                 SET @ISNotBookedIn = 1;
  359.  
  360.                             END
  361.  
  362.                             ELSE 
  363.                                 SET @ISUnInvoiced = 1;
  364.                     END
  365.  
  366.             END    
  367.  
  368.              ELSE IF (@ActualIn IS NOT NULL AND @current_Datetime >= @ActualIn AND 
  369.                         @ActualIn IS NULL)  BEGIN
  370.                         SET @ISInProgress = 1;
  371.             END
  372.  
  373.             ELSE IF (@ExpectedIn IS NOT NULL AND (@ActualIn IS NULL OR (@ActualIn IS NOT NULL
  374.                         AND @ActualIn > @current_Datetime)))  BEGIN
  375.                         SET @ISBookedIn = 1;
  376.  
  377.              END
  378.  
  379.             ELSE IF (@ExpectedIn IS NULL)  BEGIN
  380.                          SET @ISNotBookedIn = 1;
  381.             END
  382.  
  383.             ELSE 
  384.                 SET @ISUnInvoiced = 1;
  385.  
  386.  
  387.  
  388.     INSERT into @Result Values(
  389.     @QUOTEHEADERID,
  390.     @QUOTEHEADERSYSTEMID,
  391.     @INSURANCEID,
  392.     @CUSTOMERID,
  393.     @VEHICLEID,
  394.     @QUH_CIN_ID,
  395.     @QUOTENUMBER,
  396.     @POLICYNO,
  397.     @TAGNO,
  398.     @EXCESS,
  399.     @OWNERCONTRIBUTION,
  400.     @DRIVER,
  401.     @DRIVERLICENSE,
  402.     @DRIVERPHONE,
  403.     @ACTIVE1,
  404.     @DELETED,
  405.     @CREATEDON,
  406.     @UPDATEDON,
  407.     @CREATEDBY,
  408.     @UPDATEDBY,
  409.     @QUOTEBODYID ,
  410.     @CREDITINVOICEID,
  411.     @JOBID,
  412.     @EMPID,
  413.     @CLAIMTYPEID ,
  414.     @NUMBER ,
  415.     @LOCKED ,
  416.     @CLAIMNO,
  417.     @ORDERNO ,
  418.     @RATEID ,
  419.     @DATEASSESSED ,
  420.     @DATE1 ,
  421.     @DATEAUTHORISED ,
  422.     @ACTUALIN ,
  423.     @current_Datetime,
  424.     @ACTUALOUT ,
  425.     @EXPECTEDIN ,
  426.     @EXPECTEDOUT ,
  427.     @QUB_ACTIVE ,
  428.     @QUB_DELETED ,
  429.     @JOBNO,
  430.     @COMPANYNAME ,
  431.     @FIRSTNAME,
  432.     @LASTNAME ,
  433.     @CUSTOMER ,
  434.     @CUSTOMERDIGITSONLYPHONENOONE,
  435.     @CUSTOMERDIGITSONLYPHONENOTWO ,
  436.     @CUSTOMERDIGITSONLYMOBILE ,
  437.     @CUSTOMEREMAIL ,
  438.     @MAKEID ,
  439.     @MODELID ,
  440.     @REGONO ,
  441.     @VIN ,
  442.     @MAKE ,
  443.     @MODEL ,
  444.     @COLOUR ,
  445.     @INSURANCE ,
  446.     @GATEWAYID ,
  447.     @INSURANCEDIGITSONLYPHONENOONE ,
  448.     @INSURANCEDIGITSONLYPHONENOTWO ,
  449.     @EXCESSINVOICENUMBER ,
  450.     @QUOTEINVOICENUMBER ,
  451.     @QUOTEINVOICEDATE ,
  452.     @ASSESSINGCOMPANY,
  453.     @ASSESSOR ,
  454.     @ASSESSOR1 ,
  455.     @ASSESSORPHONENO1 ,
  456.     @ASSESSORPHONENO2,
  457.     @ASSESSORMOBILE ,    
  458.     @JPS_JPC_ID ,
  459.     @JOBPROCESSSTATUS ,
  460.     @CREATEDBYUSER ,
  461.     @UPDATEDBYUSER ,
  462.     @ISINVOICED ,
  463.     @ISUNINVOICED ,
  464.     @ISINPROGRESS ,
  465.     @ISBOOKEDIN ,
  466.     @ISNOTBOOKEDIN ,
  467.     @ISPAID ,
  468.     @HAVEADDITIONAL ,
  469.     @ISINPROGRESSINVOICE ,
  470.     @QUH_IS_AUDATEX ,
  471.     @EMP_FIRST_NAME,
  472.     @EMP_LAST_NAME )
  473.  
  474.             Fetch next From temp_cus INTO @QuoteHeaderID, @QuoteHeaderSystemID, @InsuranceID, @CustomerID, @VehicleID, @QUH_CIN_ID, @QuoteNumber, @PolicyNo, @TagNo, 
  475.             @Excess, @OwnerContribution, @Driver, @DriverLicense, @DriverPhone, 
  476.             @Active1, @Deleted, @CreatedOn, @UpdatedOn, @CreatedBy, @UpdatedBy,
  477.  
  478.             @QuoteBodyID, @CreditInvoiceID, @JobID, @EmpID, @ClaimTypeID, @Number, @Locked, @ClaimNo, @OrderNo, @RateID, @DateAssessed,
  479.             @Date1, @DateAuthorised, @ActualIn, @ActualOut, @ExpectedIn, @ExpectedOut, @QUB_ACTIVE, @QUB_DELETED, @JobNo,@current_Datetime,
  480.  
  481.             @CompanyName, @FirstName, @LastName, @CustomerDigitsOnlyPhoneNoOne, @CustomerDigitsOnlyPhoneNoTwo, @CustomerDigitsOnlyMobile, @CustomerEmail, 
  482.  
  483.             @Insurance, @GatewayID, @InsuranceDigitsOnlyPhoneNoOne, @InsuranceDigitsOnlyPhoneNoTwo,
  484.             @AssessingCompany, @Assessor, @Assessor1, @AssessorPhoneNo1, @AssessorPhoneNo2, @AssessorMobile, @QUH_IS_AUDATEX, @EMP_FIRST_NAME, @EMP_LAST_NAME
  485.  
  486. END                    
  487. Close temp_cus 
  488. Deallocate temp_cus
  489. END
  490. SET NOCOUNT ON;
  491. SELECT * FROM @Result
Oct 8 '15 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,430
Please use code tags when posting code or formatted data.

Cursors are always slow and rarely ever needed. You should convert your cursor code into regular SQL.
Oct 8 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.