Link to home
Start Free TrialLog in
Avatar of Taitor
TaitorFlag for United States of America

asked on

sql server stored procedure review

I am having some locking issues after running this vendor written stored procedure and wondered if someone could take a look at it and see if there were any logic in here that might cause some locking issues or something that may be hanging up after the stored procedure is called the first time.  thanks.  

CREATE PROCEDURE [dbo].[UTILITYBILL_IN]
   with RECOMPILE
 AS
BEGIN
    DECLARE

     @Spc VARCHAR(15),
     @Id1 VARCHAR(5),
     @Id2 VARCHAR(5),
     @Id3 VARCHAR(5),
     @per_type VARCHAR(30),
     @per_sub_type varchar(30),
     @Checkbox_Type VARCHAR(30),
     @PermitNum VARCHAR(30),
     @AccountNum VARCHAR(6),
     @ServiceNum  VARCHAR(6),
     @AccountNum_loaded  VARCHAR(6),
     @ServiceNum_loaded  VARCHAR(6),
     @rc int,
     @count int,
     @c CURSOR
  BEGIN
    SET NOCOUNT ON;
    TRUNCATE TABLE  stg_ubill_in
    TRUNCATE TABLE  stg_exception_report
   
    GO

      EXECUTE   @rc = master.dbo.xp_cmdshell "bcp.exe dbmain.dbo.stg_ubill_in in \\homedir\cstar2\WB\Water.txt -f \\homedir\star\WB\Water.fmt -F 2 -r \n -S PRMSQL -T"

    IF @rc != 0
     INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)
     VALUES ('NA','Bulk Copy In', 'There was an error with the bulk copy when attempting to copy the data from the Water.txt file')
       
    SET @c = CURSOR FAST_FORWARD FOR
       SELECT a.serv_prov_code, a.b1_per_id1, a.b1_per_id2, a.b1_per_id3, a.b1_alt_id, a.b1_per_type, a.b1_per_sub_type,
          rtrim(ltrim(b.accountnum)), rtrim(ltrim(b.servicenum))  
       FROM b1permit a, stg_ubill_in b
       WHERE a.serv_prov_code = 'TEST'
         AND  upper(rtrim(ltrim(a.b1_alt_id))) = upper(rtrim(ltrim(b.permitnum)))
     
    OPEN @c
    FETCH NEXT FROM  @c  INTO @spc, @Id1, @Id2, @Id3, @PermitNum, @Per_Type, @per_sub_type, @accountnum, @serviceNum
    WHILE (@@FETCH_STATUS = 0 )            
     BEGIN
            IF @Per_Type = 'WaterMeter'
                  SET @Checkbox_Type = 'ENGINEERING WATER METER'
            ELSE
                  SET @Checkbox_Type = 'ENGINEERING WATER TAP'

       -- logic for account number and service location if value already exists

        SELECT  @count = count(*)
        FROM  bchckbox
        WHERE  serv_prov_code = @spc
          AND b1_per_id1 = @id1
            AND b1_per_id2 = @id2
          AND b1_per_id3 = @id3
          AND b1_checkbox_type = @Checkbox_Type
          AND b1_checkbox_desc = 'Utility Billing Account Number'
          AND b1_checklist_comment = @accountnum

        IF @count > 0
          INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)
          VALUES (@permitnum,'Data Already Exists', @permitnum + ' The account number was already in database')

        SELECT  @count = count(*)
        FROM  bchckbox
        WHERE  serv_prov_code = @spc
          AND b1_per_id1 = @id1
            AND b1_per_id2 = @id2
          AND b1_per_id3 = @id3
          AND b1_checkbox_type = @Checkbox_Type
          AND b1_checkbox_desc = 'Utility Billing Service Location Number'
          AND b1_checklist_comment = @ServiceNum


        IF  @count > 0
          INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)
          VALUES (@permitnum,'Data Already Exists', @permitnum + ' The service location number was already in database')

         -- data already exists but  incoming value is different than that of the existing data.

        SELECT  @accountnum_loaded  = isnull(b1_checklist_comment,' ')
        FROM  bchckbox
        WHERE  serv_prov_code = @spc
          AND b1_per_id1 = @id1
            AND b1_per_id2 = @id2
          AND b1_per_id3 = @id3
          AND b1_checkbox_type = @Checkbox_Type
          AND b1_checkbox_desc = 'Utility Billing Account Number'

        if @@rowcount =1
           BEGIN  
            if @accountnum != @accountnum_loaded and @accountnum_loaded != ' '
             BEGIN
              INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)
              VALUES (@permitnum, 'Different Data', @permitnum + ' - Account number is different from existing data Incoming Value ' + @accountnum+ ' Existing Value ' + @accountnum_loaded )
             END

             UPDATE bchckbox
               SET b1_checklist_comment = @accountnum
                 , REC_FUL_NAM = 'UB ADMIN'
                 , REC_DATE = GETDATE()
              WHERE  serv_prov_code = @spc
               AND b1_per_id1 = @id1
               AND b1_per_id2 = @id2
               AND b1_per_id3 = @id3
               AND b1_checkbox_type = @Checkbox_Type
               AND b1_checkbox_desc = 'Utility Billing Account Number'
           END
        ELSE   -- record does not exist
           BEGIN

             INSERT INTO BCHCKBOX (
               SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3,
               B1_PER_TYPE, B1_PER_SUB_TYPE, B1_CHECKBOX_TYPE, B1_CHECKBOX_DESC,
               B1_CHECKBOX_IND, B1_ACT_STATUS, B1_START_DATE, B1_END_DATE,
               B1_CHECKLIST_COMMENT, REC_DATE, REC_FUL_NAM, REC_STATUS, B1_DISPLAY_ORDER,
               B1_FEE_INDICATOR, B1_ATTRIBUTE_VALUE,  B1_ATTRIBUTE_UNIT_TYPE, B1_ATTRIBUTE_VALUE_REQ_FLAG, B1_VALIDATION_SCRIPT_NAME, RELATION_SEQ_ID,
               SD_STP_NUM, B1_CHECKBOX_GROUP, MAX_LENGTH, DISPLAY_LENGTH, B1_DEFAULT_SELECTED,
               B1_GROUP_DISPLAY_ORDER , VCH_DISP_FLAG , R1_TASK_STATUS_REQ_FLAG, B1_REQ_FEE_CALC, B1_SUPERVISOR_EDIT_ONLY_FLAG )
             SELECT   @spc, @id1, @id2, @id3,
               @PER_TYPE, @PER_SUB_TYPE, R1_CHECKBOX_TYPE, R1_CHECKBOX_DESC,
               R1_CHECKBOX_IND, R1_CHECKBOX_CODE, NULL, NULL,
               @ACCOUNTNUM, GETDATE(), 'UB ADMIN' , 'A', R1_DISPLAY_ORDER,
               R1_FEE_INDICATOR, R1_ATTRIBUTE_VALUE,  R1_ATTRIBUTE_UNIT_TYPE, R1_ATTRIBUTE_VALUE_REQ_FLAG, R1_VALIDATION_SCRIPT_NAME, 0,
               0, R1_CHECKBOX_GROUP, MAX_LENGTH, DISPLAY_LENGTH, R1_DEFAULT_SELECTED,
               R1_GROUP_DISPLAY_ORDER , VCH_DISP_FLAG , R1_TASK_STATUS_REQ_FLAG, R1_REQ_FEE_CALC, R1_SUPERVISOR_EDIT_ONLY_FLAG
             FROM  r2chckbox
             WHERE   serv_prov_code = @spc
               and r1_checkbox_type = @checkbox_type
               and  r1_checkbox_desc = 'Utility Billing Account Number'
               and r1_checkbox_group = 'APPLICATION'

           end

        SELECT @serviceNum_loaded  = isnull(b1_checklist_comment , ' ')
        FROM  bchckbox
        WHERE  serv_prov_code = @spc
          AND b1_per_id1 = @id1
            AND b1_per_id2 = @id2
          AND b1_per_id3 = @id3
          AND b1_checkbox_type = @Checkbox_Type
          AND b1_checkbox_desc = 'Utility Billing Service Location Number'

        if @@rowcount =1
           BEGIN  
            if @servicenum != @servicenum_loaded and @servicenum_loaded != ' '
             BEGIN
              INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)
              VALUES (@permitnum, 'Different Data', @permitnum + ' - service number is different from existing data Incoming Value ' + @accountnum+ ' Existing Value ' + @accountnum_loaded )
             END

             UPDATE bchckbox
               SET b1_checklist_comment = @servicenum
                 , REC_FUL_NAM = 'UB ADMIN'
                 , REC_DATE = GETDATE()
              WHERE  serv_prov_code = @spc
               AND b1_per_id1 = @id1
               AND b1_per_id2 = @id2
               AND b1_per_id3 = @id3
               AND b1_checkbox_type = @Checkbox_Type
               AND b1_checkbox_desc = 'Utility Billing Service Location Number'
           END
        ELSE   -- record does not exist
           BEGIN
             INSERT INTO BCHCKBOX (
               SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3,
               B1_PER_TYPE, B1_PER_SUB_TYPE, B1_CHECKBOX_TYPE, B1_CHECKBOX_DESC,
               B1_CHECKBOX_IND, B1_ACT_STATUS, B1_START_DATE, B1_END_DATE,
               B1_CHECKLIST_COMMENT, REC_DATE, REC_FUL_NAM, REC_STATUS, B1_DISPLAY_ORDER,
               B1_FEE_INDICATOR, B1_ATTRIBUTE_VALUE,  B1_ATTRIBUTE_UNIT_TYPE, B1_ATTRIBUTE_VALUE_REQ_FLAG, B1_VALIDATION_SCRIPT_NAME, RELATION_SEQ_ID,
               SD_STP_NUM, B1_CHECKBOX_GROUP, MAX_LENGTH, DISPLAY_LENGTH, B1_DEFAULT_SELECTED,
               B1_GROUP_DISPLAY_ORDER , VCH_DISP_FLAG , R1_TASK_STATUS_REQ_FLAG, B1_REQ_FEE_CALC, B1_SUPERVISOR_EDIT_ONLY_FLAG )
             SELECT   @spc, @id1, @id2, @id3,
               @PER_TYPE, @PER_SUB_TYPE, R1_CHECKBOX_TYPE, R1_CHECKBOX_DESC,
               R1_CHECKBOX_IND, R1_CHECKBOX_CODE, NULL, NULL,
               @servicenum, GETDATE(), 'UB ADMIN' , 'A', R1_DISPLAY_ORDER,
               R1_FEE_INDICATOR, R1_ATTRIBUTE_VALUE,  R1_ATTRIBUTE_UNIT_TYPE, R1_ATTRIBUTE_VALUE_REQ_FLAG, R1_VALIDATION_SCRIPT_NAME, 0,
               0, R1_CHECKBOX_GROUP, MAX_LENGTH, DISPLAY_LENGTH, R1_DEFAULT_SELECTED,
               R1_GROUP_DISPLAY_ORDER , VCH_DISP_FLAG , R1_TASK_STATUS_REQ_FLAG, R1_REQ_FEE_CALC, R1_SUPERVISOR_EDIT_ONLY_FLAG
             FROM  r2chckbox
             WHERE   serv_prov_code = @spc
               and r1_checkbox_type = @checkbox_type
               and  r1_checkbox_desc = 'Utility Billing Service Location Number'
               and r1_checkbox_group = 'APPLICATION'

              -- Insert_asi (@permitnum, @checkbox_type, 'Utility Billing Service Location Number' )
           end

       FETCH NEXT FROM  @c  INTO @spc, @Id1, @Id2, @Id3, @PermitNum, @Per_Type, @per_sub_type,  @accountnum, @serviceNum
     END

    CLOSE @c
    DEALLOCATE @c
  END
         -- load exception table for data in stg_ubil_in but not in b1permit
     INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)  
     SELECT ltrim(rtrim(a.permitnum)) , 'Invalid Data', 'This app number was in stg_ubill_in but not in b1permit'
     FROM  stg_ubill_in a left  join b1permit b on
       upper(rtrim(ltrim(a.permitnum))) = upper(rtrim(ltrim(b.b1_alt_id)))
     WHERE b.b1_alt_id is null
 
 
END
Avatar of Taitor
Taitor
Flag of United States of America image

ASKER

Is this question not realistic?  I think I need a refund from this site.
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

I have have an issue with how you have declared the cursor.

http://msdn.microsoft.com/en-us/library/ms180169.aspx

I think that there is a little too much in this one procedure to get a handle on it easily - you are using command shell call to bcp for instance.

Since you are using SQL 2005, I'd wrap the whole contents of the procedure in a begin try end tray begin catch end catch - it really aids debugging as it reports where the error occurred - even if there are nexted procedure calls and so forth.

There is a GO in an unusual place - just after the truncate statements - unusual to call a procedure with so many parameters and all that is done is to truncate two tables.

While developing/debugging I'd comment out the nocount on.

HTH
  David

PS Something of this complexity is quite difficult to debug/develop on-line without the actual tables etc.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

With the GO at line 24 it is likely that what you think of the procedure is not even being properly created.

It is likely that when you are executing most of the body of the procedure itself.

Regards
  David
Avatar of Taitor

ASKER

Great.  I appreciate the feedback.  The two lines that show up in the activity monitor that are causing the lock on the two tables are below; if I kill the process id = 94 then process 85 goes away but it never sucessfully ran the stored proecure.    

Process ID = 94 ; status = suspended ; command = SET OPTION ON; Wait Type = LCK_M_SCH_S
DETAILS ARE
SET FMTONLY ON select * from Spo.dbo.stg_ubill_in where 1=2 SET FMTONLY OFF

Process ID = 85;; STATUS = Runnable; Command = EXECUTE; Application = Cstar_Permits;
DETAILS ARE
execute SPO.dbo.UTILITYBILL_IN


I did see an index on stg_ubill_in which it is not letting me look at I guess until I restart services because it is saying there is a lock on it even though I killed the processes that were causing the lock before.    I am wondering now if that index is causing the problem.  I will look at the original table specifications and see why it is there or what it is suppose to be.  I also ran a trace right before I had the user start the stored procedure.  so I can add that to if need be but I will be doing more of a review on that as well.  
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
...also, you can debug stored procedure, step by step, checking if it's already locked or not.
>>you can debug stored procedure, step by step, checking if it's already locked or not. <<
The author appears to be still using SQL Server 2005.
Avatar of Taitor

ASKER

I changed the truncate table commands to delete table and it seemed to execute ok from the application calling it but it did not commit the updates.  I can run this stored procedure in sql server and it does the updates but the developer calls the stored procedure it appears to execute but does not commit the updates.  any ideas?  
I'm pretty sure that tracing each sql statement within the s.p. with Profiler is the only way.
You call the SP inside a transaction?
If so look if BEGIN TRANSACTION have correspondent COMMIT (or ROLLBACK).
Hi,

Just an opinion, but that procedure shouldn't be in a transaction. Maybe the transaction is needed inside the procedure. But otherwise the procedure is too big and means the transaction and its locks are held too long.

Regards
  David
That's why I asked :)