Solved

UPDATE SQL Help

Posted on 2012-03-30
4
413 Views
Last Modified: 2012-04-03
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

0
Comment
Question by:Jeff S
  • 2
4 Comments
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
 
LVL 7

Author Comment

by:Jeff S
Comment Utility
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
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
Comment Utility
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
 
LVL 7

Author Closing Comment

by:Jeff S
Comment Utility
Thank you
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now