• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

SQL call to log write a log to a table

I have multiple stored proc where it is required to grab the name of the incoming file and write that to a separate table.

A few of those proc do this three times and it gets accomplished by creating a tmp table.
Is there a better way to handle this?



CREATE TABLE #TmpReturnedFileID1 (FileID integer)

            INSERT #TmpReturnedFileID1
                  EXECUTE dbo.ap_CreateFileLog 'Online - RBHAREFM'

            SELECT @FileID = FileID FROM #TmpReturnedFileID1

            DROP TABLE #TmpReturnedFileID1

dbo.ap_CreateFileLog call 3 times within another proc.

Thanks
0
TimSweet220
Asked:
TimSweet220
  • 7
  • 6
1 Solution
 
HCommented:
Hi

Can you clarify what you are looking to accomplish and provide a little more details.  Is your goal to rewrite the stored procedure so it is only called once?

Thanks
0
 
TimSweet220Author Commented:
Sorry.  Yes, these proc are huge and they often time out. My goal is to reduce the call and make them a bit more efficient.  so if I can limit these types of calls it will help.
0
 
HCommented:
I will need a lot more detail to help...Can you go through the high level steps that are performed and examples of the procs.

E.G.
Step#1 Proc#1 starts does this and this
Step#2 Proc#1 calls proc#2   etc etc ...........................
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
TimSweet220Author Commented:
Here is a version of the proc.

basically it grabs data form the form, call a couple stored procs to additional variable. It does some data validation, which has to come out and if there is an error it make a call to the proc (see highlighted areas).
USE [something]
GO
/****** Object:  StoredProcedure [dbo].[ap_EnrollREFM]    Script Date: 10/12/2011 13:25:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[ap_EnrollREFM]
      @ProviderNo                        varchar(9),
      @UserID                              int,
      @ReferralCategory            varchar(30) = 'URGENT/ROUTINE', -- CPSA-303 - asaxton - 6/27/04
      @ProviderClientID            varchar(10) = null,
      @ReferralDate                  datetime = null,
      @PersonSubmitting            varchar(30) = null,
      -- Referral Type Section
      @TypeOfReferral                  varchar(10) = null,
      @PersonMaking                  varchar(50) = null,
      @PersonMakingPhone            varchar(10) = null,
      @PersonMakingFax            varchar(10) = null,
      @ReferredRelationship      varchar(15) = null,
      @ReferralSourceCLTID      varchar(10) = null,
      @SignedConsent                  bit = null,
      -- Member Demographics Section
      @ClientFirstName            varchar(25) = null,
      @ClientMiddleInitial      varchar(1) = null,
      @ClientLastName                  varchar(35) = null,
      @DateOfBirth                  datetime = null,
      @Sex                              char(1) = null,
      @SSN                              varchar(10) = null,
      @ClientAddress1                  varchar(55) = null,
      @ClientAddress2                  varchar(55) = null,
      @ClientCity                        varchar(30) = null,
      @ClientState                  varchar(2) = null,
      @ClientZip                        varchar(10) = null,
      @ClientCounty                  varchar(10) = null,
      @PhoneNumber                  varchar(10) = null,
      @MessagePhone                  varchar(10) = null,
      @CurrentLocation            varchar(50) = null,
      @ContactName                  varchar(30) = null,
      @ContactPhone                  varchar(10) = null,
      @RBHA                              varchar(10) = null,
      @Guardian                        bit = null,
      @GuardianName                  varchar(30) = null,
      @GuardianPhone                  varchar(10) = null,
      @AHCCCSClient                  bit = null,
      @AHCCCSRateCode                  varchar(10) = null,
      @TXIXClient                        bit = null,
      @TXXIClient                        bit = null,
      @AHCCCSID                        varchar(10) = null,
      @AHCCCSHealthPlan            varchar(6) = null,
      @PrivateInsPlan                  varchar(20) = null,
      @PrivateInsPlanText            varchar(20) = null,
      -- Special Pops
      @RequestHospEnroll            bit = null,
      @RequestCOTEnroll            bit = null,
      @RequestJailEnroll            bit = null,
      @RequestJailCrisis            bit = null,
      @PregnantSubstanceAbuse      bit = null,
      @MedRequest                        bit = null,
      @IVDrug                              bit = null,
      @RequestSMI                        bit = null,
      -- Special Needs
      @Interpreter                  bit = null,
      @Language                        varchar(3) = null,
      @MobilityAssist                  bit = null,
      @MobilityAssistNeed            varchar(30) = null,
      @VisualImpairment            bit = null,
      @VisualImpairmentNeed      varchar(30) = null,
      @HearingImpairment            bit = null,
      @HearingImpairmentNeed      varchar(30) = null,
      @Cultural                        bit = null,
      @CulturalNeed                  varchar(30) = null,
      @ChildWatch                        bit = null,
      @ChildWatchNeed                  varchar(30) = null,
      @SMIImpairment                  bit = null,
      @SMIImpairmentNeed            varchar(30) = null,
      -- Provider Assignment
      @Program                        varchar(1) = null,
      @ProviderName                  varchar(30) = null,
      @MembersChoice                  bit = null,
      -- Referral Status      
      @InitialReferralStatus      varchar(10) = null,
      -- Other
      @Comments                        varchar(2098) = null,
      @ClientID                        numeric = null OUTPUT,
      @IsMemberManagement            bit = null,
      -- CPSA-303 - asaxton - 6/27/04
      @ActionCode                        char(1),
      @IsSuperUser            bit = 0,  --CPSA-762 JCALL 1-6-05
      @FemaID                              varchar(20) = null , -- JCall 9/7/05
      @RedCrossID                        varchar(20) = null,
      -- pace 190 desal 7/2006
      @NetworkId                        varchar(2) = null,
   
      -- pace 675 desal 7/2008
      @MaritalStatus              varchar(1) = null,
      -- pace 674 desal 7/2008
      @PrimaryLanguage              varchar(3) = null,
      
      -- PACE 8.0      
      @StateClientID                  varchar(10) = null
 
AS


      Declare @ErrorComments      varchar(2098)
      Declare @Details      varchar(2098)
      Declare @FileID            integer
      Declare @ProcessEnd      datetime
      Declare @ProviderID      int
      Declare @RecordCount      integer
      Declare @Resolution      varchar(2098)
      Declare @SaveError      integer
      Declare @UpdateAllowed varchar(5)
      DECLARE @ErrComments      VARCHAR(2098)
      DECLARE @CursorErrorID      INTEGER
      DECLARE @CursorTableID      INTEGER
      DECLARE @ErrorCode            VARCHAR(6)
      DECLARE @ErrorsID            BIGINT
      DECLARE @ErrorStatus      VARCHAR(2)
      DECLARE @FailCount            INTEGER
      DECLARE @OrigProvNo            VARCHAR(9)
      DECLARE @Stage1                  VARCHAR(20)
      DECLARE @ErrorCount            INTEGER      
      DECLARE @Ret      INTEGER
      -- Pace 190 desal
      DECLARE @Age int

      Set @Age = dbo.FN_GetAge(@DateOfBirth,@ReferralDate)

      -- desal - fill in city, state, and county if missing using zip code
      DECLARE @TempCity varchar(50)
      DECLARE @TempState varchar(2)
      DECLARE @TempCounty varchar(50)
      DECLARE @TempRBHA int

--  desal Pace 190  if access is no, then blank out some stuff
      IF @AHCCCSClient = 0
      BEGIN
            SET @AHCCCSRateCode = Null
            SET @TXIXClient = 0
            SET @TXXIClient = 0
            SET @AHCCCSHealthPlan = Null
      END


      
      Set @SSN = REPLACE(@SSN, '`', '')
      Set @ClientFirstName = REPLACE(@ClientFirstName, '`', '')
      Set @ClientLastName = REPLACE(@ClientLastName, '`', '')
      Set @ClientAddress1 = REPLACE(@ClientAddress1, '`', '')
      Set @ClientAddress2 = REPLACE(@ClientAddress2, '`', '')
      Set @ClientCity = REPLACE(@ClientCity, '`', '')

      
if @IsMemberManagement = 1  AND (@ClientID is not null AND @ClientID > 0)
      BEGIN
            SET @ErrorStatus = '02'
            SET @Stage1 = 'Referral (Online)'
            SET @ProcessEnd = GetDate()
            SET @Resolution = 'Closed'

            CREATE TABLE #TmpErrors(
            ErrorsID                  INT                        NULL,
            tableID                        INT                        NULL,
            errorCodeID                  INT                        NULL,
            fileID                        INT                        NULL,
            ProviderID                  INT                        NULL,
            stage                        VARCHAR(20)            NULL,
            resolution                  VARCHAR(2098)      NULL,
            comments                  VARCHAR(2098)      NULL,
            details                        VARCHAR(2098)      NULL,
            ErrCode                        VARCHAR(4)            NULL,
            ErrDescr                  VARCHAR(2098)      NULL,
            ProviderClientID      VARCHAR(10)            NULL,
            ReferralDate            DATETIME            NULL,
            ProviderNo                  VARCHAR(9)            NULL,
            Fatality                  BIT                        NULL)

            -- ***** Referral Type Section *****
            If @PersonMakingPhone Is Not Null
                  Set @PersonMakingPhone = Replace(@PersonMakingPhone, '-', '')

            If @PersonMakingFax Is Not Null
                  Set @PersonMakingFax = Replace(@PersonMakingFax, '-', '')
                  
            -- ***** Member Demographics Section *****
            If @SSN Is Not Null
                  Set @SSN = Replace(@SSN, '-', '')

            If @PhoneNumber Is Not Null
                  Set @PhoneNumber = Replace(@PhoneNumber, '-', '')
                  
            If @MessagePhone Is Not Null
                  Set @MessagePhone = Replace(@MessagePhone, '-', '')

            --Gather information to use for logging errors.
            ---------------------------------------------------------------------
            Exec dbo.ap_GetProviderID @ProviderNo, @ProviderID = @ProviderID OUTPUT

            Set @Details =      ISNULL('Provider No = ' + @ProviderNO, 'Provider No is NULL') + '; ' +
                                    ISNULL('ProviderClientID = ' + @ProviderClientID, 'ProviderClientID is NULL') +
                                    '; ' + ISNULL('ReferralDate = ' + LTRIM(convert(varchar(12), @ReferralDate)), 'Referral Date is NULL')


            --Create file in database file log.
            -------------------------------------------------
           CREATE TABLE #TmpReturnedFileID1 (FileID integer)

            INSERT #TmpReturnedFileID1
                  EXECUTE dbo.ap_CreateFileLog 'Online - RBHAREFM'

            SELECT @FileID = FileID FROM #TmpReturnedFileID1

            DROP TABLE #TmpReturnedFileID1


            --Validate Referral record.
            -- PACE desal 7/2006  NetworkId
            -------------------------------------
            INSERT INTO #TmpErrors(ErrorsID, tableID, errorCodeID, fileID, ProviderID, stage, resolution,
            comments, details, ErrCode, ErrDescr, ProviderClientID, ReferralDate, ProviderNo, Fatality)
            EXEC dbo.ap_Referral_Validation_Master @FileID, @ReferralCategory, @ProviderNo, @ProviderClientID, @ReferralDate, @PersonSubmitting,
            @TypeOfReferral, @PersonMaking, @PersonMakingPhone, @PersonMakingFax, @ReferredRelationship, @ReferralSourceCLTID,
            @SignedConsent, @ClientFirstName, @ClientMiddleInitial, @ClientLastName, @DateOfBirth, @Sex, @SSN, @ClientAddress1,
            @ClientAddress2, @ClientCity, @ClientState, @ClientZip, @ClientCounty, @PhoneNumber, @MessagePhone, @CurrentLocation,
            @ContactName, @ContactPhone, @RBHA, @Guardian, @GuardianName, @GuardianPhone, @AHCCCSClient, @AHCCCSRateCode,
            @TXIXClient, @TXXIClient, @AHCCCSID, @AHCCCSHealthPlan, @PrivateInsPlan, @PrivateInsPlanText, @RequestHospEnroll,
            @RequestCOTEnroll, @RequestJailEnroll, @RequestJailCrisis, @PregnantSubstanceAbuse, @MedRequest, @IVDrug, @RequestSMI, @Interpreter,
            @Language, @MobilityAssist, @MobilityAssistNeed, @VisualImpairment, @VisualImpairmentNeed, @HearingImpairment,
            @HearingImpairmentNeed, @Cultural, @CulturalNeed, @ChildWatch, @ChildWatchNeed, @SMIImpairment, @SMIImpairmentNeed,
            @Program, @ProviderName, @MembersChoice,
            @InitialReferralStatus, @Comments, @IsMemberManagement, @IsSuperUser,@NetworkId,
        @MaritalStatus, @PrimaryLanguage,  -- pace 674 and 675 desal 7/2008
        @StateClientID -- PACE 8.0


            SELECT @ErrorCount = COUNT(*) FROM #TmpErrors --count of all errors
            SELECT @FailCount = COUNT(*) FROM #TmpErrors WHERE Fatality = 1 --count of fatal errors


            IF @FailCount < 1
                  BEGIN
                        --There are no fatal errors
                        
                        --Pace 190 desal 7/2006
                        -- we need to change network provider to CPSA Direct if the conditions match
                        If (@RBHA = 26 AND @TXIXClient = 0 AND @TXXIClient = 0 AND @Age >= 18 AND @Program in ('M','G') AND  @NetworkId Not In ('20') and @NetworkId Not In ('50'))
                        BEGIN
                               SET @NetworkId = '20'
                        END

                        --C-411
                        If (@RBHA = 27 AND @TXIXClient = 0 AND @TXXIClient = 0 AND @Age >= 18 AND @Program in ('M','G'))
                        BEGIN
                               SET @NetworkId = 16
                        END

                        DECLARE curErrors CURSOR LOCAL SCROLL DYNAMIC READ_ONLY FOR
                        SELECT ErrorsID, tableID FROM #TmpErrors
                        
            -- pace 190 desal 7/2006 - network id
                        EXEC @Ret = dbo.ap_UpdateReferralRec
                        @ClientID, @ReferralCategory, @ProviderNo,      @ProviderClientID, @ReferralDate, @PersonSubmitting, @TypeOfReferral, @PersonMaking,
                        @PersonMakingPhone, @PersonMakingFax, @ReferredRelationship, @ReferralSourceCLTID, @SignedConsent,
                        @ClientFirstName, @ClientMiddleInitial, @ClientLastName, @DateOfBirth, @Sex, @SSN, @ClientAddress1,
                        @ClientAddress2, @ClientCity, @ClientState, @ClientZip, @ClientCounty, @PhoneNumber, @MessagePhone,
                        @CurrentLocation, @ContactName, @ContactPhone, @RBHA, @Guardian, @GuardianName, @GuardianPhone,
                        @AHCCCSClient, @AHCCCSRateCode, @TXIXClient, @TXXIClient, @AHCCCSID, @AHCCCSHealthPlan, @PrivateInsPlan,
                        @PrivateInsPlanText, @RequestHospEnroll, @RequestCOTEnroll, @RequestJailEnroll, @RequestJailCrisis,
                        @PregnantSubstanceAbuse, @MedRequest, @IVDrug, @RequestSMI, @Interpreter, @Language, @MobilityAssist, @MobilityAssistNeed,
                        @VisualImpairment, @VisualImpairmentNeed, @HearingImpairment, @HearingImpairmentNeed, @Cultural,
                        @CulturalNeed, @ChildWatch, @ChildWatchNeed, @SMIImpairment, @SMIImpairmentneed,
                        @Program, @ProviderName, @MembersChoice, @InitialReferralStatus,
                        @Comments, @UserID, @FemaID, @RedCrossID, @NetworkId, @MaritalStatus, @PrimaryLanguage,  -- pace 674 and 675 desal 7/2008
                @StateClientID -- PACE 8.0
            
                        SET @SaveError = @@Error
      
                        IF @SaveError <> 0
                              BEGIN
                                    --Problem with data. Could not execute the stored procedure.
                                    --Log the record that had the error during the import.
                                    ---------------------------------------------------------------------------------------------------------------------------------------
                                    SET @ErrComments = 'Error number ' + LTRIM(STR(@SaveError)) + ' occurred while importing the Referral file.'
                                    EXEC dbo.ap_LogError @ClientID, @SaveError, @FileID, @Stage1, null, @ErrComments, @Details, @ClientID, @ProviderID, NULL, @ErrorsID OUTPUT
                                    EXEC dbo.ap_CloseError @ErrorsID, @ErrorStatus, @Resolution
                                    EXEC dbo.ap_UpdateFileLog @FileID, @ProcessEND, 0, 1
                                    RETURN -10
                              END
                        ELSE
                              BEGIN
                                    if @NetworkId in ('50')
                                    begin
                                          update referrals
                                          set referralstatusid = 4
                                          where clientid = @ClientID
                                    end
                                    
                              
                                    --Stored Procedure executed successfully, iterate through the cursor
                                    --and reopen all of the errors
                                    OPEN curErrors
                                    FETCH NEXT FROM curErrors INTO @CursorErrorID, @CursorTableID
                                    WHILE @@FETCH_STATUS = 0
                                          BEGIN
                                                EXEC dbo.ap_UpdateError @CursorErrorID, @CursorTableID, 3
                                                FETCH NEXT FROM curErrors INTO @CursorErrorID, @CursorTableID
                                          END
                                    CLOSE curErrors
                                    DEALLOCATE curErrors

                                    --Create Referral File XREF.
                                    ---------------------------------------------
                                    --INSERT #TmpReturnedErr
                                    EXEC dbo.ap_CreateReferralFileXREF @FileID, @ClientID
                                    SET @SaveError = @@Error    
                                    IF @SaveError <> 0
                                    BEGIN
                                          SET @ErrComments = 'Error number ' + LTRIM(STR(@SaveError)) + ' occurred trying to create Referral File XRef.'
                                          SET @ErrorCode = '901'
                                          EXEC dbo.ap_LogError @ClientID, @ErrorCode, @FileID, @Stage1, null, @ErrComments, @Details, @ClientID, @ProviderID, NULL, @ErrorsID OUTPUT
                                          EXEC dbo.ap_CloseError @ErrorsID, @ErrorStatus, @Resolution
                                    END
      
                                    IF @ErrorCount > 0
                                          BEGIN
                                                --At least one non-fatal error (but no fatal errors)
                                                SELECT * FROM #TmpErrors
                                                RETURN 2
                                          END
                                    ELSE
                                          BEGIN
                                                --No fatal or non-fatal errors
                                                RETURN 1
                                          END
                                    EXEC dbo.ap_UpdateFileLog @FileID, @ProcessEND, 1, 0
                                    

                              END
                  END
            ELSE
                  BEGIN
                       --There was at least one fatal error, just return the recordset of errors
                        SELECT * FROM #TmpErrors
                        EXEC dbo.ap_UpdateFileLog @FileID, @ProcessEND, 0, 1
                        RETURN 10
                  END
      END      

      
If (@IsSuperUser = 0 AND @IsMemberManagement = 0) or (@ClientID = 0)
      BEGIN
            


            SET @ErrorStatus = '02'
            SET @ClientID = 0
            SET @Stage1 = 'Referral (Online)'
            SET @ProcessEnd = GetDate()
            SET @Resolution = 'Closed'

            CREATE TABLE #TmpErrors1(
            ErrorsID                  INT                        NULL,
            tableID                        INT                        NULL,
            errorCodeID                  INT                        NULL,
            fileID                        INT                        NULL,
            ProviderID                  INT                        NULL,
            stage                        VARCHAR(20)            NULL,
            resolution                  VARCHAR(2098)      NULL,
            comments                  VARCHAR(2098)      NULL,
            details                        VARCHAR(2098)      NULL,
            ErrCode                        VARCHAR(4)            NULL,
            ErrDescr                  VARCHAR(2098)      NULL,
            ProviderClientID      VARCHAR(10)            NULL,
            ReferralDate            DATETIME            NULL,
            ProviderNo                  VARCHAR(9)            NULL,
            Fatality                  BIT                        NULL)

            CREATE TABLE #TmpReturnedErr (RetCode integer)
                        
            -- ***** Referral Type Section *****
            If @PersonMakingPhone Is Not Null
                  Set @PersonMakingPhone = Replace(@PersonMakingPhone, '-', '')

            If @PersonMakingFax Is Not Null
                  Set @PersonMakingFax = Replace(@PersonMakingFax, '-', '')
                  
            -- ***** Member Demographics Section *****
            If @SSN Is Not Null
                  Set @SSN = Replace(@SSN, '-', '')

            If @PhoneNumber Is Not Null
                  Set @PhoneNumber = Replace(@PhoneNumber, '-', '')
                  
            If @MessagePhone Is Not Null
                  Set @MessagePhone = Replace(@MessagePhone, '-', '')

            --Gather information to use for logging errors.
            ---------------------------------------------------------------------
            Exec dbo.ap_GetProviderID @ProviderNo, @ProviderID = @ProviderID OUTPUT

            Set @Details =      ISNULL('Provider No = ' + @ProviderNO, 'Provider No is NULL') + '; ' +
                                    ISNULL('ProviderClientID = ' + @ProviderClientID, 'ProviderClientID is NULL') +
                                    '; ' + ISNULL('ReferralDate = ' + LTRIM(convert(varchar(12), @ReferralDate)), 'Referral Date is NULL')


            --Create file in database file log.
            -------------------------------------------------
           CREATE TABLE #TmpReturnedFileID2 (FileID integer)

            INSERT #TmpReturnedFileID2
                  EXECUTE dbo.ap_CreateFileLog 'Online - RBHAREFM'


            SELECT @FileID = FileID FROM #TmpReturnedFileID2 OPTION (KEEPFIXED PLAN)  

            DROP TABLE #TmpReturnedFileID2

            --Validate Referral record.
            -- PACE desal 7/2006  NetworkId
            -------------------------------------
            INSERT INTO #TmpErrors1(ErrorsID, tableID, errorCodeID, fileID, ProviderID, stage, resolution,
            comments, details, ErrCode, ErrDescr, ProviderClientID, ReferralDate, ProviderNo, Fatality)
            EXEC dbo.ap_Referral_Validation_Master @FileID, @ReferralCategory, @ProviderNo, @ProviderClientID, @ReferralDate, @PersonSubmitting,
            @TypeOfReferral, @PersonMaking, @PersonMakingPhone, @PersonMakingFax, @ReferredRelationship, @ReferralSourceCLTID,
            @SignedConsent, @ClientFirstName, @ClientMiddleInitial, @ClientLastName, @DateOfBirth, @Sex, @SSN, @ClientAddress1,

            @ClientAddress2, @ClientCity, @ClientState, @ClientZip, @ClientCounty, @PhoneNumber, @MessagePhone, @CurrentLocation,
            @ContactName, @ContactPhone, @RBHA, @Guardian, @GuardianName, @GuardianPhone, @AHCCCSClient, @AHCCCSRateCode,
            @TXIXClient, @TXXIClient, @AHCCCSID, @AHCCCSHealthPlan, @PrivateInsPlan, @PrivateInsPlanText, @RequestHospEnroll,
            @RequestCOTEnroll, @RequestJailEnroll, @RequestJailCrisis, @PregnantSubstanceAbuse, @MedRequest, @IVDrug, @RequestSMI, @Interpreter,
            @Language, @MobilityAssist, @MobilityAssistNeed, @VisualImpairment, @VisualImpairmentNeed, @HearingImpairment,
            @HearingImpairmentNeed, @Cultural, @CulturalNeed, @ChildWatch, @ChildWatchNeed, @SMIImpairment, @SMIImpairmentNeed,
            @Program, @ProviderName, @MembersChoice,
            @InitialReferralStatus, @Comments, @IsMemberManagement, null,@NetworkId,
        @MaritalStatus, @PrimaryLanguage,  -- pace 674 and 675 desal 7/2008
            @StateClientID -- PACE 8.0

            SELECT @ErrorCount = COUNT(*) FROM #TmpErrors1 --count of all errors
            SELECT @FailCount = COUNT(*) FROM #TmpErrors1 WHERE Fatality = 1 --count of fatal errors


            IF @FailCount < 1
                  BEGIN
                        --There are no fatal errors

                        --Pace 190 desal 7/2006
                        -- we need to change network provider to CPSA Direct if the conditions match
                        -- we need to change network provider to CPSA Direct if the conditions match
                        If (@RBHA = 26 AND @TXIXClient = 0 AND @TXXIClient = 0 AND @Age >= 18 AND @Program in ('M','G') AND  @NetworkId Not In (20) and @NetworkId Not In ('50'))
                        BEGIN
                               SET @NetworkId = 20
                        END

                        --C-411
                        If (@RBHA = 27 AND @TXIXClient = 0 AND @TXXIClient = 0 AND @Age >= 18 AND @Program in ('M','G'))
                        BEGIN
                               SET @NetworkId = 16
                        END

                        DECLARE curErrors CURSOR LOCAL SCROLL DYNAMIC READ_ONLY FOR
                        SELECT ErrorsID, tableID FROM #TmpErrors1

                        --Execute the stored procedure.
                        --------------------------------------------------
                        -- pace 190 desal 7/2006 - network id
                        CREATE TABLE #TmpReturnedID (ClientID INTEGER , UpdateAllowed VARCHAR(5), StageID INTEGER )
                        INSERT #TmpReturnedID
                        Exec dbo.ap_ProviderImport_RBHAREFM @FileID, @ReferralCategory, @ProviderNo, @ProviderClientID, @ReferralDate, @PersonSubmitting,
                        @TypeOfReferral, @PersonMaking, @PersonMakingPhone, @PersonMakingFax, @ReferredRelationship, @ReferralSourceCLTID,
                        @SignedConsent, @ClientFirstName, @ClientMiddleInitial, @ClientLastName, @DateOfBirth, @Sex, @SSN, @ClientAddress1,
                        @ClientAddress2, @ClientCity, @ClientState, @ClientZip, @ClientCounty, @PhoneNumber, @MessagePhone, @CurrentLocation,
                        @ContactName, @ContactPhone, @RBHA, @Guardian, @GuardianName, @GuardianPhone, @AHCCCSClient, @AHCCCSRateCode,
                        @TXIXClient, @TXXIClient, @AHCCCSID, @AHCCCSHealthPlan, @PrivateInsPlan, @PrivateInsPlanText, @RequestHospEnroll,
                        @RequestCOTEnroll, @RequestJailEnroll, @RequestJailCrisis, @PregnantSubstanceAbuse, @MedRequest, @IVDrug, @RequestSMI, @Interpreter,
                        @Language, @MobilityAssist, @MobilityAssistNeed, @VisualImpairment, @VisualImpairmentNeed, @HearingImpairment,
                        @HearingImpairmentNeed, @Cultural, @CulturalNeed, @ChildWatch, @ChildWatchNeed, @SMIImpairment, @SMIImpairmentNeed,
                        @Program, @ProviderName, @MembersChoice,
                        @InitialReferralStatus, @Comments, @UserID,
                        -- CPSA-351 - asaxton - 6/21/04
                        2, -- ComponentID
                        @ActionCode, @FemaID, @RedCrossID, @NetworkId, @MaritalStatus, @PrimaryLanguage,  -- pace 674 and 675 desal 7/2008
                        @StateClientID -- PACE 8.0

                        SET @SaveError = @@Error

                        SELECT @ClientID = ClientID, @UpdateAllowed = UpdateAllowed FROM #TmpReturnedID

                        IF @ClientID IS NULL
                              SET @ClientID = 0

                        DROP TABLE #TmpReturnedID

                        IF @SaveError <> 0
                              BEGIN
                                    --Problem with data. Could not execute the stored procedure.
                                    --Log the record that had the error during the import.
                                    ---------------------------------------------------------------------------------------------------------------------------------------
                                    SET @ErrComments = 'Error number ' + LTRIM(STR(@SaveError)) + ' occurred while importing the Referral file.'
                                    EXEC dbo.ap_LogError @ClientID, @SaveError, @FileID, @Stage1, null, @ErrComments, @Details, @ClientID, @ProviderID, NULL, @ErrorsID OUTPUT
                                    EXEC dbo.ap_CloseError @ErrorsID, @ErrorStatus, @Resolution
                                    EXEC dbo.ap_UpdateFileLog @FileID, @ProcessEND, 0, 1
                                    RETURN -10
                              END
                        ELSE
                              BEGIN
                                    --Stored Procedure executed successfully, iterate through the cursor
                                    --and reopen all of the errors
                                    
                                                                        
                                    if @NetworkId in ('50')
                                    begin
                                          update referrals
                                          set referralstatusid = 4
                                          where clientid = @ClientID
                                    end

                                    OPEN curErrors
                                    FETCH NEXT FROM curErrors INTO @CursorErrorID, @CursorTableID
                                    WHILE @@FETCH_STATUS = 0
                                          BEGIN
                                                EXEC dbo.ap_UpdateError @CursorErrorID, @CursorTableID, 3
                                                FETCH NEXT FROM curErrors INTO @CursorErrorID, @CursorTableID
                                          END
                                    CLOSE curErrors
                                    DEALLOCATE curErrors

                                    --Create Referral File XREF.
                                    ---------------------------------------------
                                    --INSERT #TmpReturnedErr
                                    EXEC dbo.ap_CreateReferralFileXREF @FileID, @ClientID
                                    SET @SaveError = @@Error    
                                    IF @SaveError <> 0
                                    BEGIN
                                          SET @ErrComments = 'Error number ' + LTRIM(STR(@SaveError)) + ' occurred trying to create Referral File XRef.'
                                          SET @ErrorCode = '901'
                                          EXEC dbo.ap_LogError @ClientID, @ErrorCode, @FileID, @Stage1, null, @ErrComments, @Details, @ClientID, @ProviderID, NULL, @ErrorsID OUTPUT
                                          EXEC dbo.ap_CloseError @ErrorsID, @ErrorStatus, @Resolution
                                    END
                                          --Create transmit record if no errors.
                                    ---------------------------------------------------------
      
                                    IF @ErrorCount > 0
                                          BEGIN
                                                --At least one non-fatal error (but no fatal errors)
                                                SELECT * FROM #TmpErrors1
                                                RETURN 2
                                          END
                                    ELSE
                                          BEGIN
                                                --No fatal or non-fatal errors
                                                RETURN 1
                                          END
                                    EXEC dbo.ap_UpdateFileLog @FileID, @ProcessEND, 1, 0
                              END
                  END
            ELSE
                  BEGIN
                        --There was at least one fatal error, just return the recordset of errors
                       SELECT * FROM #TmpErrors1
                        EXEC dbo.ap_UpdateFileLog @FileID, @ProcessEND, 0, 1
                        RETURN 10
                  END


            DROP TABLE #TmpReturnedErr

      END
0
 
TimSweet220Author Commented:
Over all there are 27 calls to other procs inside this one.

0
 
HCommented:
I left myself a reminder to review this 1st thing tomorrow morning if nobody has before then...
0
 
TimSweet220Author Commented:
Thanks
0
 
HCommented:
     Hi here are a few ideas to start with. Without access to your databases and systems this is the best I can do for now. When you create the stored procedure use the "WITH RECOMPILE" option. This way it will insure that it chooses a different execution plan when the stored procedure is executed. Go through your procs and gather a list of all tables that get touched during the run of this and update the statistics on those tables. Sql server will set a flag on any affected stored procedures and then recompile the next time they are called. Another way I use sometimes is to delete/drop the proc and recreate it or use the alter proc statement even though there is no changes to it.  This will also force the recompile. Yet another way is to use the Sp_recompile system proc as well, this will also refresh the query plans. Also recompile all procs anytime you rebuild or reorg indexes.
0
 
TimSweet220Author Commented:
Great info!!!   I didn't understand the last option with the sp_recomplie system.  When should be use...it does all the stored procs in the database?
0
 
HCommented:

You can use just for the procs in question

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

sp_recomplie procname;

Also more info on recompiling procs..

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

0
 
HCommented:
Oh I see what you meant...I was just saying sp_recomplie is a sql server system stored proc...
0
 
TimSweet220Author Commented:
If I could give ya 1000 points I would.

Thanks
0
 
TimSweet220Author Commented:
Great info.  On target.
Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now