Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Cursor Help Needed 1

Status
Not open for further replies.
JBourne77

JBourne77

IS-IT--Management
Jan 21, 2008
153
US
First and foremost, I try to avoid cursors at all costs, so I am not the best SQL coder when it comes to coding them. In my code snippet below is my Stored Procedure (cusVRCSuperBillEXT). In my section below, I am taking the fields and passing AppointmentId into my report. What I need to do is pass every AppointmentId against a set date range. Can anyone help me construct a cursor that would do this?

What I started
==================================
Code: 
 SET NOCOUNT ON
 
 CREATE TABLE #TEMP1
              (
                TempID INT IDENTITY ,
                [1.PatientName] VARCHAR(92) ,
                [2.PatLast] VARCHAR(30) ,
                [3.PatFirst] VARCHAR(30) ,
                [4.PatMiddle] VARCHAR(30) ,
                [5.PatientAddr1] VARCHAR(50) ,
                [6.PatientAddr2] VARCHAR(50) ,
                [7.PatientCity] VARCHAR(30) ,
                [8.PatientState] VARCHAR(3) ,
                [9.PatientZip] VARCHAR(10) ,
                [10.PatientCountry] VARCHAR(30) ,
                [11.PatientBirthdate] DATETIME ,
                [12.PatientSex] VARCHAR(1) ,
                [13.PatientPhone1] VARCHAR(15) ,
                [14.PatientSSN] VARCHAR(9) ,
                [15.PatOccupation] VARCHAR(50) ,
                [16.PatSchool] VARCHAR(30) ,
                [17.PatBudget] MONEY NULL,
                [18.PatientSameAsGuarantor] SMALLINT ,
                [19.ReferringPhysician] VARCHAR(82) ,
                [20.PatientID] VARCHAR(15) ,
                [50.Department] VARCHAR(200) ,
                [51.Date Of Service] DATETIME ,
                [52.CPT Code] VARCHAR(10) ,
                [53.Description] VARCHAR(255) ,
                [54.Expiration Date] DATETIME ,
                [55.Ordered By] VARCHAR(10) ,
                [56.Modifier1MId] VARCHAR(255) ,
                [57.Modifier2MId] VARCHAR(255) ,
                [58.Modifier3MId] VARCHAR(255) ,
                [59.Modifier4MId] VARCHAR(255) ,
                [60.Doctor] VARCHAR(255)
              )
                           
--INSERT #TEMP1
--EXECUTE('dbo.cusVRCSuperBillEXT 307') <307 is my AppointmentID>

--INSERT #TEMP1
--EXECUTE('dbo.cusVRCSuperBillEXT 306') <306 is my AppointmentID>

<How do I pass my Id's into my cursor?>

DECLARE @curse_id int
DECLARE curse CURSOR LOCAL STATIC FORWARD_ONLY
FOR 
.......... <Need Help Here>

OPEN curse
FETCH NEXT FROM curse 
INTO @curse_id
WHILE @@FETCH_STATUS = 0
BEGIN

   FETCH NEXT FROM curse 
   INTO @curse_id
END
CLOSE curse
DEALLOCATE curse

DROP TABLE #TEMP1


My Stored Procedure - (cusVRCSuperBillEXT)
===========================================
Code: 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE    PROC [dbo].[cusVRCSuperBillEXT] @AppointmentsId INT
AS
SET nocount ON

       DECLARE @PatientProfileId INT
       DECLARE @count INT

       SELECT
        @PatientProfileId = Appointments.OwnerID
       FROM
        Appointments
       WHERE
        Appointments.AppointmentsID = @AppointmentsId

       CREATE TABLE #TEMP1
              (
                TempID INT IDENTITY ,
                [1.PatientName] VARCHAR(92) ,
                [2.PatLast] VARCHAR(30) ,
                [3.PatFirst] VARCHAR(30) ,
                [4.PatMiddle] VARCHAR(30) ,
                [5.PatientAddr1] VARCHAR(50) ,
                [6.PatientAddr2] VARCHAR(50) ,
                [7.PatientCity] VARCHAR(30) ,
                [8.PatientState] VARCHAR(3) ,
                [9.PatientZip] VARCHAR(10) ,
                [10.PatientCountry] VARCHAR(30) ,
                [11.PatientBirthdate] DATETIME ,
                [12.PatientSex] VARCHAR(1) ,
                [13.PatientPhone1] VARCHAR(15) ,
                [14.PatientSSN] VARCHAR(9) ,
                [15.PatOccupation] VARCHAR(50) ,
                [16.PatSchool] VARCHAR(30) ,
                [17.PatBudget] MONEY ,
                [18.PatientSameAsGuarantor] SMALLINT ,
                [19.ReferringPhysician] VARCHAR(82) ,
                [20.PatientID] VARCHAR(15) ,
                [50.Department] VARCHAR(200) ,
                [51.Date Of Service] DATETIME ,
                [52.CPT Code] VARCHAR(10) ,
                [53.Description] VARCHAR(255) ,
                [54.Expiration Date] DATETIME ,
                [55.Ordered By] VARCHAR(10) ,
                [56.Modifier1MId] VARCHAR(255) ,
                [57.Modifier2MId] VARCHAR(255) ,
                [58.Modifier3MId] VARCHAR(255) ,
                [59.Modifier4MId] VARCHAR(255) ,
                [60.Doctor] VARCHAR(255)
              )

       INSERT INTO
        #TEMP1
        SELECT
            ISNULL(pp.First , ' ') + ' ' + ISNULL(pp.Middle , ' ') + ' ' + ISNULL(pp.Last , '') ,
            ISNULL(pp.Last , '') ,
            ISNULL(pp.First , '') ,
            ISNULL(pp.Middle , '') ,
            ISNULL(pp.Address1 , '') ,
            ISNULL(pp.Address2 , '') ,
            ISNULL(pp.City , '') ,
            ISNULL(pp.State , '') ,
            ISNULL(pp.Zip , '') ,
            ISNULL(pp.Country , '') ,
            ISNULL(pp.Birthdate , '') ,
            ISNULL(pp.Sex , '') ,
            ISNULL(pp.Phone1 , '') ,
            ISNULL(pp.SSN , '') ,
            ISNULL(pp.EmpOccup , '') ,
            ISNULL(pp.SchoolName , '') ,
            g.Budget ,
            ISNULL(pp.PatientSameAsGuarantor , '') ,
            ISNULL(UPPER(df.First) , ' ') + ' ' + ISNULL(UPPER(df.Last) , ' ') + ' ' + ISNULL(UPPER(df.Suffix) , ' ') ,
            ISNULL(pp.PatientID , '') ,
            ISNULL(m.description , '') ,
            pv.Visit ,
            ISNULL(pvp.CPTCode , '') ,
            ISNULL(pvp.Description , '') ,
            ISNULL(( pv.Visit + 92 ) , '') ,
            m.ledger ,
            ISNULL(pvp.Modifier1MId , '') ,
            ISNULL(pvp.Modifier2MId , '') ,
            ISNULL(pvp.Modifier3MId , '') ,
            ISNULL(pvp.Modifier4MId , '') ,
            ISNULL(df2.Last , '')
        FROM
            PatientProfile pp ,
            Guarantor G ,
            DoctorFacility df ,
            PatientVisit PV ,
            patientvisitprocs PVP ,
            procedures P ,
            medlists M ,
            DoctorFacility df2
        WHERE
            PV.PatientProfileID = @PatientProfileId
            AND PV.PatientProfileID = PP.PatientProfileID
            AND pp.GuarantorId = g.GuarantorId
            AND pp.RefDoctorID = df.DoctorFacilityID
            AND pv.patientvisitid = pvp.PatientVisitId
            AND pvp.ProceduresId = p.ProceduresId
            AND p.departmentMId = m.medlistsid
            AND PV.DoctorID = df2.DoctorFacilityID
        ORDER BY
            m.ledger ,
            pv.Visit DESC
	

       SELECT
        @count = MAX(tempid)
       FROM
        #temp1 

       WHILE @count IS NOT NULL
             BEGIN
                   IF (
                        SELECT [56.Modifier1MId] FROM #temp1 WHERE @count = tempid
                      ) IS NOT NULL
                      AND (
                            SELECT [56.Modifier1MId] FROM #temp1 WHERE @count = tempid
                          ) IN ( '226' , '227' ) 
                      BEGIN
                            UPDATE
                                #temp1
                            SET 
                                [56.Modifier1MId] = m.description
                            FROM
                                #temp1 t ,
                                medlists m
                            WHERE
                                t.[56.Modifier1MId] = m.medlistsid
                                AND @count = t.tempid
                      END
                   ELSE 
                      BEGIN
                            UPDATE
                                #temp1
                            SET 
                                [56.Modifier1MId] = '0'
                            FROM
                                #temp1 t
                            WHERE
                                @count = t.tempid
                      END
                   IF (
                        SELECT [57.Modifier2MId] FROM #temp1 WHERE @count = tempid
                      ) IS NOT NULL
                      AND (
                            SELECT [56.Modifier1MId] FROM #temp1 WHERE @count = tempid
                          ) IN ( '226' , '227' ) 
                      BEGIN
                            UPDATE
                                #temp1
                            SET 
                                [57.Modifier2MId] = m.description
                            FROM
                                #temp1 t ,
                                medlists m
                            WHERE
                                t.[57.Modifier2MId] = m.medlistsid
                                AND @count = t.tempid
                      END
                   ELSE 
                      BEGIN
                            UPDATE
                                #temp1
                            SET 
                                [57.Modifier2MId] = '0'
                            FROM
                                #temp1 t
                            WHERE
                                @count = t.tempid
                      END
                   IF (
                        SELECT [58.Modifier3MId] FROM #temp1 WHERE @count = tempid
                      ) IS NOT NULL
                      AND (
                            SELECT [56.Modifier1MId] FROM #temp1 WHERE @count = tempid
                          ) IN ( '226' , '227' ) 
                      BEGIN
                            UPDATE
                                #temp1
                            SET 
                                [58.Modifier3MId] = m.description
                            FROM
                                #temp1 t ,
                                medlists m
                            WHERE
                                t.[58.Modifier3MId] = m.medlistsid
                                AND @count = t.tempid
                      END
                   ELSE 
                      BEGIN
                            UPDATE
                                #temp1
                            SET 
                                [58.Modifier3MId] = '0'
                            FROM
                                #temp1 t
                            WHERE
                                @count = t.tempid
                      END
                   IF (
                        SELECT [59.Modifier4MId] FROM #temp1 WHERE @count = tempid
                      ) IS NOT NULL
                      AND (
                            SELECT [56.Modifier1MId] FROM #temp1 WHERE @count = tempid
                          ) IN ( '226' , '227' ) 
                      BEGIN
                            UPDATE
                                #temp1
                            SET 
                                [59.Modifier4MId] = m.description
                            FROM
                                #temp1 t ,
                                medlists m
                            WHERE
                                t.[59.Modifier4MId] = m.medlistsid
                                AND @count = t.tempid
                      END
                   ELSE 
                      BEGIN
                            UPDATE
                                #temp1
                            SET 
                                [59.Modifier4MId] = '0'
                            FROM
                                #temp1 t
                            WHERE
                                @count = t.tempid
                      END

                   SELECT
                    @count = MAX(tempid)
                   FROM
                    #temp1
                   WHERE
                    @count > tempid

             END

       UPDATE
        #TEMP1
       SET
        [54.Expiration Date] = ' '
       WHERE
        1 <> SUBSTRING([55.Ordered By] , 1 , 1)
       UPDATE
        #TEMP1
       SET
        [54.Expiration Date] = ' '
       WHERE
        [52.CPT Code] IN ( '67028' , '65800' , '67515' , '67500' )
       IF 1 < (
                SELECT COUNT (*) FROM #temp1
              ) 
          SELECT
            [1.PatientName] ,
            [2.PatLast] ,
            [3.PatFirst] ,
            [4.PatMiddle] ,
            [5.PatientAddr1] ,
            [6.PatientAddr2] ,
            [7.PatientCity] ,
            [8.PatientState] ,
            [9.PatientZip] ,
            [10.PatientCountry] ,
            [11.PatientBirthdate] ,
            [12.PatientSex] ,
            [13.PatientPhone1] ,
            [14.PatientSSN] ,
            [15.PatOccupation] ,
            [16.PatSchool] ,
            [17.PatBudget] ,
            [18.PatientSameAsGuarantor] ,
            [19.ReferringPhysician] ,
            [20.PatientID] ,
            [50.Department] ,
            [51.Date Of Service] ,
            [52.CPT Code] ,
            [53.Description] ,
            CONVERT(VARCHAR(10) , [54.Expiration Date] , 101) AS [54.Expiration Date] ,
            [55.Ordered By] ,
            [56.Modifier1MId] ,
            [57.Modifier2MId] ,
            [58.Modifier3MId] ,
            [59.Modifier4MId] ,
            [60.Doctor]
          FROM
            #temp1
       ELSE 
          SELECT
            ISNULL(pp.First , ' ') + ' ' + ISNULL(pp.Middle , ' ') + ' ' + ISNULL(pp.Last , '') AS [1.PatientName] ,
            ISNULL(pp.Last , '') AS [2.PatLast] ,
            ISNULL(pp.First , '') AS [3.PatFirst] ,
            ISNULL(pp.Middle , '') AS [4.PatMiddle] ,
            ISNULL(pp.Address1 , '') AS [5.PatientAddr1] ,
            ISNULL(pp.Address2 , '') AS [6.PatientAddr2] ,
            ISNULL(pp.City , '') AS [7.PatientCity] ,
            ISNULL(pp.State , '') AS [8.PatientState] ,
            ISNULL(pp.Zip , '') AS [9.PatientZip] ,
            ISNULL(pp.Country , '') AS [10.PatientCountry] ,
            ISNULL(pp.Birthdate , '') AS [11.PatientBirthdate] ,
            ISNULL(pp.Sex , '') AS [12.PatientSex] ,
            ISNULL(pp.Phone1 , '') AS [13.PatientPhone1] ,
            ISNULL(pp.SSN , '') AS [14.PatientSSN] ,
            ISNULL(pp.EmpOccup , '') AS [15.PatOccupation] ,
            ISNULL(pp.SchoolName , '') AS [16.PatSchool] ,
            NULL AS [17.PatBudget] ,
            '' AS [18.PatientSameAsGuarantor] ,
            '' AS [19.ReferringPhysician] ,
            ISNULL(pp.PatientID , '') AS [20.PatientID] ,
            '' AS [50.Department] ,
            '1/1/2000' AS [51.Date Of Service] ,
            '' AS [52.CPT Code] ,
            '' AS [53.Description] ,
            '1/1/2000' AS [54.Expiration Date] ,
            '' AS [55.Ordered By] ,
            '' AS [56.Modifier1MId] ,
            '' AS [57.Modifier2MId] ,
            '' AS [58.Modifier3MId] ,
            '' AS [59.Modifier4MId] ,
            '' AS [60.Doctor]
          FROM
            PatientProfile pp
          WHERE
            pp.PatientProfileID = @PatientProfileId
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
 
Sort by date Sort by votes
Your basic cursor syntax will look like this.

Code: 
DECLARE @YourId INT, @AnotherValue INT

DECLARE cur CURSOR FOR SELECT YourId, AnotherValue FROM YourTable
OPEN cur
FETCH NEXT FROM cur into @YourId, @AnotherValue
WHILE @@FETCH_STATUS = 0
BEGIN
     EXEC usp_Yourprocedure @YourId
     FETCH NEXT FROM cur into @YourId, @AnotherValue
END
CLOSE cur
DEALLOCATE cur

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog
 
Upvote 0 Downvote
mrdenny -

I followed what you said and tried to format my SQL and I am getting the following error and not sure why.

Msg 137, Level 15, State 2, Line 1
Must declare the variable '@AppointmentsID'.

Code: 
 SET NOCOUNT ON
 
 CREATE TABLE #TEMP1
              (
                TempID INT IDENTITY ,
                [1.PatientName] VARCHAR(92) ,
                [2.PatLast] VARCHAR(30) ,
                [3.PatFirst] VARCHAR(30) ,
                [4.PatMiddle] VARCHAR(30) ,
                [5.PatientAddr1] VARCHAR(50) ,
                [6.PatientAddr2] VARCHAR(50) ,
                [7.PatientCity] VARCHAR(30) ,
                [8.PatientState] VARCHAR(3) ,
                [9.PatientZip] VARCHAR(10) ,
                [10.PatientCountry] VARCHAR(30) ,
                [11.PatientBirthdate] DATETIME ,
                [12.PatientSex] VARCHAR(1) ,
                [13.PatientPhone1] VARCHAR(15) ,
                [14.PatientSSN] VARCHAR(9) ,
                [15.PatOccupation] VARCHAR(50) ,
                [16.PatSchool] VARCHAR(30) ,
                [17.PatBudget] MONEY NULL,
                [18.PatientSameAsGuarantor] SMALLINT ,
                [19.ReferringPhysician] VARCHAR(82) ,
                [20.PatientID] VARCHAR(15) ,
                [50.Department] VARCHAR(200) ,
                [51.Date Of Service] DATETIME ,
                [52.CPT Code] VARCHAR(10) ,
                [53.Description] VARCHAR(255) ,
                [54.Expiration Date] DATETIME ,
                [55.Ordered By] VARCHAR(10) ,
                [56.Modifier1MId] VARCHAR(255) ,
                [57.Modifier2MId] VARCHAR(255) ,
                [58.Modifier3MId] VARCHAR(255) ,
                [59.Modifier4MId] VARCHAR(255) ,
                [60.Doctor] VARCHAR(255)
              )

DECLARE curAppointmentId CURSOR
        FOR SELECT
                AppointmentsID
            FROM
                Appointments
            WHERE
				Appointments.ApptStart >= ISNULL('07/31/2009','1/1/1900')
				AND Appointments.ApptStart < dateadd(d, 1, ISNULL('07/31/2009','1/1/3000'))
    
DECLARE @AppointmentsID INT
 
OPEN curAppointmentId
 
FETCH NEXT FROM curAppointmentId INTO @AppointmentsID
 
WHILE @@FETCH_STATUS = 0
      BEGIN
            INSERT
                #TEMP1
            EXECUTE ('dbo.cusVRCSuperBillEXT @AppointmentsID')
            FETCH NEXT FROM curAppointmentId INTO @AppointmentsID
      END
 
SELECT * FROM #TEMP1    
    
CLOSE curAppointmentId
 
DEALLOCATE curAppointmentId
 
DROP TABLE #TEMP1
 
Upvote 0 Downvote
Change:

EXECUTE ('dbo.cusVRCSuperBillEXT @AppointmentsID')

To

EXECUTE dbo.cusVRCSuperBillEXT @AppointmentsID

(remove the parenthesis and single-quotes)


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
George -

Thank you! You made my morning start off good!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Question
Parameterised sql server query
Replies
8
Views
328
Chriss Miller
C
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
920
pjw001
pjw001
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
503
Chris Miller
Chris Miller
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
560
f0rg3tfu1
f0rg3tfu1
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
748
Volvere
V

Part and Inventory Search

Sponsor

Back
Top