UPDATE SQL Help

I am having issues with my update SQL, can someone please assist me?

Msg 4104, Level 16, State 1, Line 283
The multi-part identifier "g.GuarantorId" could not be bound.
Msg 4104, Level 16, State 1, Line 284
The multi-part identifier "g.StatementNotes" could not be bound.
Msg 4104, Level 16, State 1, Line 285
The multi-part identifier "sc.CriteriaName" could not be bound.

SET NOCOUNT ON

DECLARE
    @startdate DATETIME ,
    @enddate DATETIME ,
    @StartAlpha VARCHAR(20) ,
    @EndAlpha VARCHAR(20)

SET @startdate = ISNULL('01/01/2012' , '1/1/1900') 
SET @enddate = ISNULL('03/29/2012' , '1/1/3000')   
SET @StartAlpha = CAST('a' AS VARCHAR(20))
SET @EndAlpha = CAST('z' AS VARCHAR(19)) + 'z'

DECLARE @PatientProfileId INT
DECLARE @LoginUser AS VARCHAR(30)

SET @LoginUser = dbo.GetLogonID()

CREATE TABLE #tmpData
    (
      Type SMALLINT ,
      GuarantorId INT ,
      PatientProfileId INT ,
      PatientVisitId INT ,
      DoctorId INT ,
      FacilityId INT ,
      CompanyId INT ,
      GuarantorName_Sort VARCHAR(255) ,
      GuarantorZip_Sort VARCHAR(10) ,
      SortOrder VARCHAR(20) ,
      LastStatementDate DATETIME ,
      Unknown11 VARCHAR(100) ,
      GuarantorName VARCHAR(255) ,
      GuarantorAddress1 VARCHAR(100) ,
      GuarantorAddress2 VARCHAR(100) ,
      GuarantorCity VARCHAR(100) ,
      GuarantorState VARCHAR(100) ,
      GuarantorZip VARCHAR(10) ,
      GuarantorCountry VARCHAR(30) ,
      IncludeAging SMALLINT ,
      IncludeInsuranceBalanceOnly SMALLINT ,
      Unknown21 VARCHAR(100) ,
      IncludeByDoctor SMALLINT ,
      IncludeCash SMALLINT ,
      IncludePatientWithTransactions SMALLINT ,
      IncludePayToAddress SMALLINT ,
      IncludeTransactionsAfterClosing_Individual SMALLINT ,
      IncludeTransactionsAfterClosing_Batch SMALLINT ,
      IncludeReferringDoctor SMALLINT ,
      CreditCard1 VARCHAR(200) ,
      CreditCard2 VARCHAR(200) ,
      CreditCard3 VARCHAR(200) ,
      CreditCard4 VARCHAR(200) ,
      CreditCard5 VARCHAR(200) ,
      CreditCard6 VARCHAR(200) ,
      Unknown35 VARCHAR(100) ,
      Unknown36 VARCHAR(100) ,
      Unknown37 VARCHAR(100) ,
      TransactionThrough DATETIME ,
      PatientId VARCHAR(20) ,
      PatientName VARCHAR(200) ,
      PatientAddress1 VARCHAR(100) ,
      PatientAddress2 VARCHAR(100) ,
      PatientCity VARCHAR(100) ,
      PatientState VARCHAR(100) ,
      PatientZip VARCHAR(10) ,
      PatientCountry VARCHAR(30) ,
      Unknown47 VARCHAR(100) ,
      Unknown48 VARCHAR(100) ,
      Budget MONEY ,
      Visit DATETIME ,
      Entered DATETIME ,
      TicketNumber VARCHAR(30) ,
      DoctorName VARCHAR(100) ,
      DoctorAddress1 VARCHAR(100) ,
      DoctorAddress2 VARCHAR(100) ,
      DoctorCity VARCHAR(100) ,
      DoctorState VARCHAR(30) ,
      DoctorZip VARCHAR(30) ,
      DoctorCountry VARCHAR(60) ,
      ReferringDoctor VARCHAR(100) ,
      PayToAddressIsEmpty SMALLINT ,
      Company VARCHAR(100) ,
      CompanyAddress1 VARCHAR(100) ,
      CompanyAddress2 VARCHAR(100) ,
      CompanyCity VARCHAR(100) ,
      CompanyState VARCHAR(30) ,
      CompanyZip VARCHAR(10) ,
      CompanyCountry VARCHAR(30) ,
      CompanyPhone1 VARCHAR(20) ,
      PaytoCompany VARCHAR(100) ,
      PayToAddress1 VARCHAR(100) ,
      PayToAddress2 VARCHAR(100) ,
      PayToCity VARCHAR(100) ,
      PayToState VARCHAR(30) ,
      PayToZip VARCHAR(10) ,
      PayToCountry VARCHAR(100) ,
      Facility VARCHAR(100) ,
      FacilityAddress1 VARCHAR(100) ,
      FacilityAddress2 VARCHAR(100) ,
      FacilityCity VARCHAR(100) ,
      FacilityState VARCHAR(10) ,
      FacilityZip VARCHAR(10) ,
      FacilityCountry VARCHAR(30) ,
      FacilityPhone1 VARCHAR(20) ,
      Unknown85 VARCHAR(100) ,
      Unknown86 VARCHAR(100) ,
      Unknown87 VARCHAR(100) ,
      Unknown88 VARCHAR(100) ,
      StatementMessage_0_30 TEXT ,
      StatementMessage_31_60 TEXT ,
      StatementMessage_61_90 TEXT ,
      StatementMessage_91_120 TEXT ,
      StatementMessage_121 TEXT ,
      StatementNotesPrintStatus SMALLINT ,
      StatementNotes TEXT ,
      InsDeposit MONEY ,
      PatDeposit MONEY ,
      InsBalance0 MONEY ,
      PatBalance0 MONEY ,
      InsBalance30 MONEY ,
      PatBalance30 MONEY ,
      InsBalance60 MONEY ,
      PatBalance60 MONEY ,
      InsBalance90 MONEY ,
      PatBalance90 MONEY ,
      InsBalance120 MONEY ,
      PatBalance120 MONEY ,
      Unknown108 VARCHAR(100) ,
      PatientVisitDiagsId INT ,
      PatientVisitProcsId INT ,
      VisitTransactionsId INT ,
      Code VARCHAR(10) ,
      ICD9Code VARCHAR(10) ,
      CPTCode VARCHAR(10) ,
      RevenueCode VARCHAR(10) ,
      Description VARCHAR(255) ,
      TransactionService DATETIME ,
      TransactionEntry DATETIME ,
      PatientAmount MONEY ,
      InsuranceAmount MONEY ,
      Units FLOAT ,
      Fee MONEY ,
      Modifier1 VARCHAR(100) ,
      Modifier2 VARCHAR(100) ,
      Modifier3 VARCHAR(100) ,
      Modifier4 VARCHAR(100) ,
      PlaceOfService VARCHAR(3) ,
      TypeOfService VARCHAR(3) ,
      Source INT ,
      [Action] CHAR(1) ,
      ActionType VARCHAR(100) ,
      Payer VARCHAR(100) ,
      TransactionNote TEXT ,
      TotalFee MONEY ,
      Unknown135 VARCHAR(100) ,
      Unknown136 VARCHAR(100) ,
      Unknown137 VARCHAR(100) ,
      Unknown138 VARCHAR(100) ,
      ShowNoteOnStatement SMALLINT ,
      CheckNumber VARCHAR(50) ,
      Unknown141 VARCHAR(100) ,
      Unknown142 VARCHAR(100) ,
      Unknown143 VARCHAR(100) ,
      Unknown144 VARCHAR(100) ,
      Unknown145 VARCHAR(100) ,
      Unknown146 VARCHAR(100) ,
      Unknown147 VARCHAR(100) ,
      Unknown148 VARCHAR(100) ,
      PaymentPlanMonthlypayment MONEY ,
      PaymentPlanNextDueDate VARCHAR(20)
    )
          
DECLARE @batchid INT
CREATE TABLE #tmp ( id INT )
INSERT  INTO #tmp
        EXEC reportnewbatch 
            cusMPMStatement_AOSM
SET @batchid = ( SELECT TOP 1 id FROM #tmp )

INSERT  INTO cusReportBatchParam
        SELECT
            @BatchId ,
            'StartDate' ,
            ISNULL('01/01/2012' , '1/1/1900')  

INSERT  INTO cusReportBatchParam
        SELECT
            @BatchId ,
            'EndDate' ,
            ISNULL('03/29/2012' , '1/1/3000') 
 
DECLARE @curse_id INT
DECLARE curse CURSOR
FOR
    SELECT
        g.GuarantorId
    FROM
        Guarantor g
    WHERE
        -- Filter on Guarantor Alpha Ranges
        (
          (
            'a' IS NOT NULL
            AND g.last >= @StartAlpha
          )
          OR ( 'a' IS NULL )
        )
        AND (
              (
                'z' IS NOT NULL
                AND g.last <= @EndAlpha
              )
              OR ( 'z' IS NULL )
            )

OPEN curse
FETCH NEXT FROM curse 
INTO @curse_id
WHILE @@FETCH_STATUS = 0 
    BEGIN
        INSERT  INTO #tmp -- hides the result
                EXEC reportAddToBatch 
                    @batchid ,
                    @curse_id ,
                    17 ,
                    '' ,
                    @BatchId
        FETCH NEXT FROM curse 
INTO @curse_id
    END
CLOSE curse
DEALLOCATE curse
 
EXEC crystalproc 
    @batchid
DROP TABLE #tmp


INSERT  INTO #tmpData
        EXEC dbo.CrystalProc 
            @batchid 
            
DECLARE curPatient CURSOR FORWARD_ONLY READ_ONLY LOCAL
FOR
    SELECT
        g.GuarantorId
    FROM
        #tmpData
        INNER JOIN Guarantor g ON #tmpData.GuarantorId = g.GuarantorId
        INNER JOIN dbo.StatementsCriteria sc ON sc.StatementsCriteriaId = '17'
    WHERE 
		g.GuarantorId = #tmpData.GuarantorId    

OPEN curPatient
FETCH NEXT FROM curPatient INTO @PatientProfileId 

WHILE @@FETCH_STATUS = 0 
    BEGIN

        INSERT  ActivityLog
                (
                  Action ,
                  ModuleMLC ,
                  ActivityMLC ,
                  FunctionName ,
                  PatientProfileId ,
                  Tablename ,
                  RecordId ,
                  Value1 ,
                  Value2 ,
                  Created ,
                  CreatedBy ,
                  LastModified ,
                  LastModifiedBy
                )
        VALUES
                (
                  4 ,
                  2 ,
                  7 ,
                  'Print Statement via Statement Report' ,
                  @PatientProfileId ,
                  'PatientProfile' ,
                  g.GuarantorId ,
                  CONVERT(VARCHAR(255) , g.StatementNotes) ,
                  sc.CriteriaName ,
                  GETDATE() ,
                  @LoginUser ,
                  GETDATE() ,
                  @LoginUser 
                );

        FETCH NEXT FROM curPatient INTO @PatientProfileId 
    END
CLOSE curPatient
DEALLOCATE curPatient
        

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Brendt HessConnect With a Mentor Senior DBACommented:
First, lets look at your original query.  The errors are occurring because you are referencing those field values in your INSERT statement, but they are not in context.  This is in this code block:

    INSERT INTO ActivityLog (
		Action ,
		ModuleMLC ,
		ActivityMLC ,
		FunctionName ,
		PatientProfileId ,
		Tablename ,
		RecordId ,
		Value1 ,
		Value2 ,
		Created ,
		CreatedBy ,
		LastModified ,
		LastModifiedBy
		)
    VALUES (
		4 ,
		2 ,
		7 ,
		'Print Statement via Statement Report' ,
		@PatientProfileId ,
		'PatientProfile' ,
		g.GuarantorId ,		----<<<<----<<<< here
		CONVERT(VARCHAR(255) , g.StatementNotes) , ----<<<<----<<<< here
		sc.CriteriaName , ----<<<<----<<<< here
		GETDATE() ,
		@LoginUser ,
		GETDATE() ,
		@LoginUser 
		);

Open in new window

If you need those values, you have to have them in variables at that time, or be performing a SELECT statement for the insert, not a VALUES list.  Note that you can extract any number of variable values in your cursor - just specify one variable per item in your FETCH...INTO clause.  If you don't need those values, either leave them out of the INSERT statement entirely, or replace the field name declarations with default values (e.g. NULL, '', etc.).

Your code looks... odd. The WHERE range filter is constructed incorrectly - the block starting with:
    WHERE
        -- Filter on Guarantor Alpha Ranges
        (
          (
            'a' IS NOT NULL
            AND g.last >= @StartAlpha
          )
          OR ( 'a' IS NULL )
        )

Open in new window

Should be:

    WHERE
        -- Filter on Guarantor Alpha Ranges
        (
          (
            @StartAlpha IS NOT NULL
            AND g.last >= @StartAlpha
          )
          OR ( @StartAlpha IS NULL )
        )
        AND (
              (
                @EndAlpha IS NOT NULL
                AND g.last <= @EndAlpha
              )
              OR ( @EndAlpha IS NULL )
            )

Open in new window


There may be other issues I have not yet identified - it needs a thorough functional review to ensure that it is actually doing what you want it to do.
0
 
HainKurtSr. System AnalystCommented:
maybe you should convert your cursor into regular select and get the results into some variables and use them in your insert

--DECLARE curPatient CURSOR FORWARD_ONLY READ_ONLY LOCAL
--FOR
DECLARE @GuarantorId varchar(max)
DECLARE @StatementNote varchar(max)
DECLARE @CriteriaName varchar(max)

    SELECT
        @GuarantorId = g.GuarantorId,
        @StatementNote = g.StatementNote,
        @CriteriaName = sc.CriteriaName 
    FROM
        #tmpData
        INNER JOIN Guarantor g ON #tmpData.GuarantorId = g.GuarantorId
        INNER JOIN dbo.StatementsCriteria sc ON sc.StatementsCriteriaId = '17'
    WHERE 
		g.GuarantorId = #tmpData.GuarantorId    

OPEN curPatient
FETCH NEXT FROM curPatient INTO @PatientProfileId 

WHILE @@FETCH_STATUS = 0 
    BEGIN

        INSERT  ActivityLog
                (
                  Action ,
                  ModuleMLC ,
                  ActivityMLC ,
                  FunctionName ,
                  PatientProfileId ,
                  Tablename ,
                  RecordId ,
                  Value1 ,
                  Value2 ,
                  Created ,
                  CreatedBy ,
                  LastModified ,
                  LastModifiedBy
                )
        VALUES
                (
                  4 ,
                  2 ,
                  7 ,
                  'Print Statement via Statement Report' ,
                  @PatientProfileId ,
                  'PatientProfile' ,
                  @GuarantorId ,
                  CONVERT(VARCHAR(255) , @StatementNote) ,
                  @CriteriaName ,
                  GETDATE() ,
                  @LoginUser ,
                  GETDATE() ,
                  @LoginUser 
                );

        FETCH NEXT FROM curPatient INTO @PatientProfileId 
    END
CLOSE curPatient
DEALLOCATE curPatient

Open in new window

0
 
Jeff SAuthor Commented:
Msg 16916, Level 16, State 1, Line 250
A cursor with the name 'curPatient' does not exist.
Msg 16916, Level 16, State 1, Line 262
A cursor with the name 'curPatient' does not exist.
Msg 16916, Level 16, State 1, Line 302
A cursor with the name 'curPatient' does not exist.
Msg 16916, Level 16, State 1, Line 303
A cursor with the name 'curPatient' does not exist.

SET NOCOUNT ON

DECLARE
    @startdate DATETIME ,
    @enddate DATETIME ,
    @StartAlpha VARCHAR(20) ,
    @EndAlpha VARCHAR(20)

SET @startdate = ISNULL('01/01/2012' , '1/1/1900') 
SET @enddate = ISNULL('03/29/2012' , '1/1/3000')   
SET @StartAlpha = CAST('a' AS VARCHAR(20))
SET @EndAlpha = CAST('z' AS VARCHAR(19)) + 'z'

DECLARE @PatientProfileId INT
DECLARE @LoginUser AS VARCHAR(30)

SET @LoginUser = dbo.GetLogonID()

CREATE TABLE #tmpData
    (
      Type SMALLINT ,
      GuarantorId INT ,
      PatientProfileId INT ,
      PatientVisitId INT ,
      DoctorId INT ,
      FacilityId INT ,
      CompanyId INT ,
      GuarantorName_Sort VARCHAR(255) ,
      GuarantorZip_Sort VARCHAR(10) ,
      SortOrder VARCHAR(20) ,
      LastStatementDate DATETIME ,
      Unknown11 VARCHAR(100) ,
      GuarantorName VARCHAR(255) ,
      GuarantorAddress1 VARCHAR(100) ,
      GuarantorAddress2 VARCHAR(100) ,
      GuarantorCity VARCHAR(100) ,
      GuarantorState VARCHAR(100) ,
      GuarantorZip VARCHAR(10) ,
      GuarantorCountry VARCHAR(30) ,
      IncludeAging SMALLINT ,
      IncludeInsuranceBalanceOnly SMALLINT ,
      Unknown21 VARCHAR(100) ,
      IncludeByDoctor SMALLINT ,
      IncludeCash SMALLINT ,
      IncludePatientWithTransactions SMALLINT ,
      IncludePayToAddress SMALLINT ,
      IncludeTransactionsAfterClosing_Individual SMALLINT ,
      IncludeTransactionsAfterClosing_Batch SMALLINT ,
      IncludeReferringDoctor SMALLINT ,
      CreditCard1 VARCHAR(200) ,
      CreditCard2 VARCHAR(200) ,
      CreditCard3 VARCHAR(200) ,
      CreditCard4 VARCHAR(200) ,
      CreditCard5 VARCHAR(200) ,
      CreditCard6 VARCHAR(200) ,
      Unknown35 VARCHAR(100) ,
      Unknown36 VARCHAR(100) ,
      Unknown37 VARCHAR(100) ,
      TransactionThrough DATETIME ,
      PatientId VARCHAR(20) ,
      PatientName VARCHAR(200) ,
      PatientAddress1 VARCHAR(100) ,
      PatientAddress2 VARCHAR(100) ,
      PatientCity VARCHAR(100) ,
      PatientState VARCHAR(100) ,
      PatientZip VARCHAR(10) ,
      PatientCountry VARCHAR(30) ,
      Unknown47 VARCHAR(100) ,
      Unknown48 VARCHAR(100) ,
      Budget MONEY ,
      Visit DATETIME ,
      Entered DATETIME ,
      TicketNumber VARCHAR(30) ,
      DoctorName VARCHAR(100) ,
      DoctorAddress1 VARCHAR(100) ,
      DoctorAddress2 VARCHAR(100) ,
      DoctorCity VARCHAR(100) ,
      DoctorState VARCHAR(30) ,
      DoctorZip VARCHAR(30) ,
      DoctorCountry VARCHAR(60) ,
      ReferringDoctor VARCHAR(100) ,
      PayToAddressIsEmpty SMALLINT ,
      Company VARCHAR(100) ,
      CompanyAddress1 VARCHAR(100) ,
      CompanyAddress2 VARCHAR(100) ,
      CompanyCity VARCHAR(100) ,
      CompanyState VARCHAR(30) ,
      CompanyZip VARCHAR(10) ,
      CompanyCountry VARCHAR(30) ,
      CompanyPhone1 VARCHAR(20) ,
      PaytoCompany VARCHAR(100) ,
      PayToAddress1 VARCHAR(100) ,
      PayToAddress2 VARCHAR(100) ,
      PayToCity VARCHAR(100) ,
      PayToState VARCHAR(30) ,
      PayToZip VARCHAR(10) ,
      PayToCountry VARCHAR(100) ,
      Facility VARCHAR(100) ,
      FacilityAddress1 VARCHAR(100) ,
      FacilityAddress2 VARCHAR(100) ,
      FacilityCity VARCHAR(100) ,
      FacilityState VARCHAR(10) ,
      FacilityZip VARCHAR(10) ,
      FacilityCountry VARCHAR(30) ,
      FacilityPhone1 VARCHAR(20) ,
      Unknown85 VARCHAR(100) ,
      Unknown86 VARCHAR(100) ,
      Unknown87 VARCHAR(100) ,
      Unknown88 VARCHAR(100) ,
      StatementMessage_0_30 TEXT ,
      StatementMessage_31_60 TEXT ,
      StatementMessage_61_90 TEXT ,
      StatementMessage_91_120 TEXT ,
      StatementMessage_121 TEXT ,
      StatementNotesPrintStatus SMALLINT ,
      StatementNotes TEXT ,
      InsDeposit MONEY ,
      PatDeposit MONEY ,
      InsBalance0 MONEY ,
      PatBalance0 MONEY ,
      InsBalance30 MONEY ,
      PatBalance30 MONEY ,
      InsBalance60 MONEY ,
      PatBalance60 MONEY ,
      InsBalance90 MONEY ,
      PatBalance90 MONEY ,
      InsBalance120 MONEY ,
      PatBalance120 MONEY ,
      Unknown108 VARCHAR(100) ,
      PatientVisitDiagsId INT ,
      PatientVisitProcsId INT ,
      VisitTransactionsId INT ,
      Code VARCHAR(10) ,
      ICD9Code VARCHAR(10) ,
      CPTCode VARCHAR(10) ,
      RevenueCode VARCHAR(10) ,
      Description VARCHAR(255) ,
      TransactionService DATETIME ,
      TransactionEntry DATETIME ,
      PatientAmount MONEY ,
      InsuranceAmount MONEY ,
      Units FLOAT ,
      Fee MONEY ,
      Modifier1 VARCHAR(100) ,
      Modifier2 VARCHAR(100) ,
      Modifier3 VARCHAR(100) ,
      Modifier4 VARCHAR(100) ,
      PlaceOfService VARCHAR(3) ,
      TypeOfService VARCHAR(3) ,
      Source INT ,
      [Action] CHAR(1) ,
      ActionType VARCHAR(100) ,
      Payer VARCHAR(100) ,
      TransactionNote TEXT ,
      TotalFee MONEY ,
      Unknown135 VARCHAR(100) ,
      Unknown136 VARCHAR(100) ,
      Unknown137 VARCHAR(100) ,
      Unknown138 VARCHAR(100) ,
      ShowNoteOnStatement SMALLINT ,
      CheckNumber VARCHAR(50) ,
      Unknown141 VARCHAR(100) ,
      Unknown142 VARCHAR(100) ,
      Unknown143 VARCHAR(100) ,
      Unknown144 VARCHAR(100) ,
      Unknown145 VARCHAR(100) ,
      Unknown146 VARCHAR(100) ,
      Unknown147 VARCHAR(100) ,
      Unknown148 VARCHAR(100) ,
      PaymentPlanMonthlypayment MONEY ,
      PaymentPlanNextDueDate VARCHAR(20)
    )
          
DECLARE @batchid INT
CREATE TABLE #tmp ( id INT )
INSERT  INTO #tmp
        EXEC reportnewbatch 
            cusMPMStatement_AOSM
SET @batchid = ( SELECT TOP 1 id FROM #tmp )

INSERT  INTO cusReportBatchParam
        SELECT
            @BatchId ,
            'StartDate' ,
            ISNULL('01/01/2012' , '1/1/1900')  

INSERT  INTO cusReportBatchParam
        SELECT
            @BatchId ,
            'EndDate' ,
            ISNULL('03/29/2012' , '1/1/3000') 
 
DECLARE @curse_id INT
DECLARE curse CURSOR
FOR
    SELECT
        g.GuarantorId
    FROM
        Guarantor g
    WHERE
        -- Filter on Guarantor Alpha Ranges
        (
          (
            'a' IS NOT NULL
            AND g.last >= @StartAlpha
          )
          OR ( 'a' IS NULL )
        )
        AND (
              (
                'z' IS NOT NULL
                AND g.last <= @EndAlpha
              )
              OR ( 'z' IS NULL )
            )

OPEN curse
FETCH NEXT FROM curse 
INTO @curse_id
WHILE @@FETCH_STATUS = 0 
    BEGIN
        INSERT  INTO #tmp -- hides the result
                EXEC reportAddToBatch 
                    @batchid ,
                    @curse_id ,
                    17 ,
                    '' ,
                    @BatchId
        FETCH NEXT FROM curse 
INTO @curse_id
    END
CLOSE curse
DEALLOCATE curse
 
EXEC crystalproc 
    @batchid
DROP TABLE #tmp


INSERT  INTO #tmpData
        EXEC dbo.CrystalProc 
            @batchid 

--DECLARE curPatient CURSOR FORWARD_ONLY READ_ONLY LOCAL
--FOR
DECLARE @GuarantorId VARCHAR(MAX)
DECLARE @StatementNote VARCHAR(MAX)
DECLARE @CriteriaName VARCHAR(MAX)

SELECT
    @GuarantorId = g.GuarantorId ,
    @StatementNote = g.StatementNotes ,
    @CriteriaName = sc.CriteriaName
FROM
    #tmpData
    INNER JOIN Guarantor g ON #tmpData.GuarantorId = g.GuarantorId
    INNER JOIN dbo.StatementsCriteria sc ON sc.StatementsCriteriaId = '17'
WHERE
    g.GuarantorId = #tmpData.GuarantorId    

OPEN curPatient
FETCH NEXT FROM curPatient INTO @PatientProfileId 

WHILE @@FETCH_STATUS = 0 
    BEGIN

        INSERT  ActivityLog
                (
                  Action ,
                  ModuleMLC ,
                  ActivityMLC ,
                  FunctionName ,
                  PatientProfileId ,
                  Tablename ,
                  RecordId ,
                  Value1 ,
                  Value2 ,
                  Created ,
                  CreatedBy ,
                  LastModified ,
                  LastModifiedBy
                )
        VALUES
                (
                  4 ,
                  2 ,
                  7 ,
                  'Print Statement via Statement Report' ,
                  @PatientProfileId ,
                  'PatientProfile' ,
                  @GuarantorId ,
                  CONVERT(VARCHAR(255) , @StatementNote) ,
                  @CriteriaName ,
                  GETDATE() ,
                  @LoginUser ,
                  GETDATE() ,
                  @LoginUser 
                );

        FETCH NEXT FROM curPatient INTO @PatientProfileId 
    END
CLOSE curPatient
DEALLOCATE curPatient

--DROP TABLE #tmpData

Open in new window

0
 
Jeff SAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.