Stored Procedure Syntax Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Syntax Help

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-06-03 : 16:43:20

Can anyone spot my Syntax error? Im stumped ...

Server: Msg 170, Level 15, State 1, Procedure cusMPMPatientProfileAdvanOrtho, Line 186
Line 186: Incorrect syntax near 'Middle'.

Line 186 is as follows:

LEFT JOIN MedLists ml2 ON pp.PatientRelationToGuarantorMId = ml2.MedListsID

Where is this 'Middle' syntax error coming from???

Stored Procedure below:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cusMPMPatientProfileAdvanOrtho]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cusMPMPatientProfileAdvanOrtho]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- 03/11/2003 BinuV: Revised to fix the Relationship to patient for primary insurance if SameAsPatient.

CREATE PROCEDURE cusMPMPatientProfileAdvanOrtho @pPatientProfileId int, @pAppointmentsId int = NULL AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --to eliminate connection blocks.
SET NOCOUNT ON

CREATE TABLE #Contacts_tmp
(
id_col Numeric(3,0) identity,
PatientProfileId int NULL,
First varchar (50)NULL,
Middle varchar (50)NULL,
Last varchar (50)NULL,
Address1 VARCHAR (50)NULL,
Address2 VARCHAR (50)NULL,
City VARCHAR (50)NULL,
State VARCHAR (15)NULL,
Zip VARCHAR (15)NULL,
Phone1 varchar (15)NULL ,
Phone1Type VARCHAR (25)NULL,
Phone2 VARCHAR (15)NULL,
Phone2Type VARCHAR (25)NULL,
ContactRelToPatient varchar (20) NULL,
Created datetime NULL
)

SELECT '1.PatientProfileId' = @pPatientProfileId,
'2.GuarantorId' = IsNull(g.GuarantorID,0),
'3.GuarantorFirst' = IsNull(g.First,''),
'4.GuarantorMiddle' = IsNull(g.Middle,''),
'5.GuarantorLast' = IsNull(g.Last,''),
'6.GuarantorAddr1' = IsNull(g.Address1,''),
'7.GuarantorAddr2' = IsNull(g.Address2,''),
'8.GuarantorCity' = IsNull(g.City,''),
'9.GuarantorState' = IsNull(g.State,''),
'10.GuarantorZip'= IsNull(g.Zip,''),
'11.GuarantorCountry' = ISNULL(g.Country,''),
'12.GuarantorSSN' = ISNULL(SUBSTRING(g.SSN,1,3) + '-' + SUBSTRING(g.SSN,4,2) + '-' + SUBSTRING(g.SSN,6,4),''),
'13.GuarantorBirthdate' = ISNULL(Convert(VarChar(20),g.birthdate,101),''),
'14.GuarantorPhone1' = ISNULL(dbo.formatphone(g.phone1,1),''),
'139.GuarantorPhone1Type' = IsNull(g.Phone1Type,' '),
'15.GuarantorPhone2' = ISNULL(dbo.formatphone(g.phone2,1),''),
'140.GuarantorPhone2Type' = IsNull(g.Phone2Type,' '),
'16.GuarantorEmpName' = IsNull(emp.Name,'')
FROM PatientProfile pp INNER JOIN Guarantor g ON pp.GuarantorID=g.GuarantorID
LEFT JOIN Employer emp ON g.EmployerID = emp.EmployerID
WHERE pp.PatientProfileID = @pPatientProfileId

SELECT '20.PatientID' = IsNull(pp.PatientID,''),
'19.PatientIDNumeric' = CASE WHEN isnumeric(pp.PatientID) = 1 and len(pp.PatientId) <= 9 THEN convert(int, pp.PatientID) ELSE 0 END,
'21.PatientPrefix' = IsNull(pp.Prefix,''),
'22.PatientFirst' = IsNull(pp.First,''),
'23.PatientMiddle' = IsNull(pp.Middle,''),
'24.PatientLast' = IsNull(pp.Last,''),
'25.PatientSuffix' = IsNull(pp.Suffix,''),
'26.PatientAddr1' = IsNull(pp.Address1,''),
'27.PatientAddr2' = IsNull(pp.Address2,''),
'28.PatientCity' = IsNull(pp.City,''),
'29.PatientState' = IsNull(pp.State,''),
'30.PatientZip' = IsNull(pp.Zip,''),
'31.PatientCountry' = ISNULL(pp.Country, ''),
'32.PatientPhone1' = ISNULL(dbo.formatphone(pp.phone1,1),''),
'33.PatientPhone1Type' = IsNull(pp.Phone1Type,' '),
'34.PatientPhone2' = ISNULL(dbo.formatphone(pp.phone2,1),''),
'35.PatientPhone2Type' = IsNull(pp.Phone2Type,' '),
'36.PatientSSN' = ISNULL(SUBSTRING(pp.SSN,1,3) + '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),''),
'37.PatientBirthdate' = ISNULL(Convert(VarChar(20),pp.birthdate,101),''),
'38.PatientMaritalStatus' = IsNull(ml.Description,' '),
'40.PatientRefDoctor' = isnull(df.First,'') + ' ' + isnull(df.Middle,'') + ' ' + isnull(df.Last,'') + ' ' + isnull(df.Suffix,''),
'41.PatientPrimaryDoctor' = isnull(df2.First,'') + ' ' + isnull(df2.Middle,'') + ' ' + isnull(df2.Last,'') + ' ' + isnull(df2.Suffix,''),
'42.PatientDoctor' = isnull(df3.First,'') + ' ' + isnull(df3.Middle,'') + ' ' + isnull(df3.Last,'') + ' ' + isnull(df3.Suffix,''),
'43.PatientSex' = CASE
WHEN pp.Sex = 'M' THEN 'Male'
WHEN pp.Sex = 'F' THEN 'Female'
ELSE ISNULL(pp.Sex,'')END,
'135.EMailAddress' = IsNull(pp.EMailAddress,'N/A'),
'44.PatientEmpStatus' = IsNull(ml2.FunctionName,' '),
'45.PatientEmpCode' = IsNull(ml2.Code,' '),
'46.PatientEmployer' = IsNull(emp.Name,''),
'136.PatientEmployerAddress' = IsNull(emp.Address1,'') + IsNull(' '+emp.Address2,''),
'137.PatientEmployerCSZ' =IsNull(emp.City,'') + IsNull(', '+emp.State,'') + IsNull(' '+emp.Zip,''),
'47.PatientEmployerPhone' = ISNULL(dbo.formatphone(emp.phone1,1),''),
'48.PatientSameAsGuarantor' = IsNull(pp.PatientSameAsGuarantor,0),
'138.PatientAge' = CASE
WHEN datediff(day, pp.birthdate, getdate()) <= 6 THEN cast(datediff(day, pp.birthdate, getdate()) as varchar(10)) + ' dys'
WHEN datediff(day, pp.birthdate, getdate()) <= 112 THEN cast(floor(cast(datediff(day, pp.birthdate, getdate()) as decimal) / 7) as varchar(10)) + ' wks'
WHEN floor(cast(datediff(day, pp.birthdate, getdate()) as decimal) / 365.25) < 2 and day(pp.birthdate) <= day(getdate()) THEN cast(datediff(month,pp.birthdate, getdate()) as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, getdate()) as decimal) / 365.25) < 2 and day(pp.birthdate) > day(getdate()) THEN cast(datediff(month,pp.birthdate, getdate()) - 1 as varchar(10)) + ' mos'
WHEN floor(cast(datediff(day, pp.birthdate, getdate()) as decimal) / 365.25) >= 2 THEN cast(floor(cast(datediff(day, pp.birthdate, getdate()) as decimal) / 365.25) as varchar(10)) + ' yrs'
ELSE '' END
FROM PatientProfile pp
LEFT JOIN DoctorFacility df ON pp.RefDoctorID = df.DoctorFacilityID
LEFT JOIN DoctorFacility df2 ON pp.PrimaryCareDoctorID = df2.DoctorFacilityID
LEFT JOIN DoctorFacility df3 ON pp.DoctorID = df3.DoctorFacilityID
LEFT JOIN Medlists ml ON pp.MaritalStatusMID = ml.MedlistsID
LEFT JOIN Medlists ml2 ON pp.EmpStatusMID = ml2.MedListsID
LEFT JOIN Employer emp ON pp.EmployerID = emp.EmployerID
WHERE pp.PatientProfileID = @pPatientProfileId

SELECT '50.Ins1_First' = IsNull(pi.First,''),
'51.Ins1_Middle' = IsNull(pi.Middle,''),
'52.Ins1_Last' = IsNull(pi.Last,''),
'53.Ins1_Addr1' = IsNull(pi.Address1,''),
'54.Ins1_Addr2' = IsNull(pi.Address2,''),
'55.Ins1_City' = IsNull(pi.City,''),
'56.Ins1_State' = IsNull(pi.State,''),
'57.Ins1_Zip' = IsNull(pi.Zip,''),
'58.Ins1_Country' = ISNULL(pi.Country,''),
'59.Ins1_Phone1' = IsNull(pi.Phone1,''),
'60.Ins1_SSN' = --IsNull(pi.SSN,''),
CASE
WHEN pi.InsuredSameAsPatient = 1 THEN pp.SSN
ELSE IsNull(pi.SSN,'')
END,
'61.Ins1_Birthdate'= ISNULL(Convert(VarChar(20),pi.birthdate,101),''),
'62.Ins1_InsuredId' = IsNull(pi.InsuredId,''),
'63.Ins1_GroupId' = IsNull(pi.GroupId,''),
'64.Ins1_InsuredSameAsPatient' = IsNull(pi.InsuredSameAsPatient,0),
'65.Ins1_InsuredSameAsGuarantor' = IsNull(pi.InsuredSameAsGuarantor,0),
'66.Ins1_RelationToInsured' =
CASE
WHEN pi.InsuredSameAsGuarantor = 1 THEN IsNull(ml2.Description,'')
WHEN pi.InsuredSameAsPatient = 1 THEN convert(varchar(200), 'SELF')
ELSE IsNull(ml.Description,'')
END,
'67.Ins1_OrderForClaims' = IsNull(pi.OrderForClaims,0),
'68.Ins1_CompanyName' = IsNull(ic.Name,''),
'147.Ins1_Address' = IsNull(ic.Address1,'') + IsNull(' '+ ic.Address2,''),
'148.Ins1_CSZ' = IsNull(ic.City,'') + IsNull(', ' + ic.State,'') + IsNull(' ' + ic.Zip,''),
'149.Ins1_Phone'= ISNULL(dbo.formatphone(ic.phone1,1),'')

FROM PatientProfile pp
INNER JOIN PatientInsurance pi ON pp.PatientProfileID = pi.PatientProfileID AND (pi.Inactive IS NULL OR pi.Inactive = 0) AND pi.OrderForClaims = 1
LEFT JOIN MedLists ml ON pi.PatRelToInsuredMId = ml.MedListsID
LEFT JOIN MedLists ml2 ON pp.PatientRelationToGuarantorMId = ml2.MedListsID
LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersID = ic.InsuranceCarriersID AND (ic.ReferenceCarrier = 0 OR ic.ReferenceCarrier is null)
WHERE pp.PatientProfileID = @pPatientProfileId

SELECT '70.Ins2_First' = IsNull(pi.First,''),
'71.Ins2_Middle' = IsNull(pi.Middle,''),
'72.Ins2_Last' = IsNull(pi.Last,''),
'73.Ins2_Addr1' = IsNull(pi.Address1,''),
'74.Ins2_Addr2' = IsNull(pi.Address2,''),
'75.Ins2_City' = IsNull(pi.City,''),
'76.Ins2_State' = IsNull(pi.State,''),
'77.Ins2_Zip' = IsNull(pi.Zip,''),
'78.Ins2_Country' = ISNULL(pi.Country,''),
'79.Ins2_Phone1' = IsNull(pi.Phone1,''),
'80.Ins2_SSN' =
CASE
WHEN pi.InsuredSameAsPatient = 1 THEN pp.SSN
ELSE IsNull(pi.SSN,'')
END,
'81.Ins2_Birthdate'= ISNULL(Convert(VarChar(20),pi.birthdate,101),''),
'82.Ins2_InsuredId' = IsNull(pi.InsuredId,''),
'83.Ins2_GroupId' = IsNull(pi.GroupId,''),
'84.Ins2_InsuredSameAsPatient' = IsNull(pi.InsuredSameAsPatient,0),
'85.Ins2_InsuredSameAsGuarantor' = IsNull(pi.InsuredSameAsGuarantor,0),
'86.Ins2_RelationToInsured' =
CASE
WHEN pi.InsuredSameAsGuarantor = 1 THEN IsNull(ml2.Description,'')
WHEN pi.InsuredSameAsPatient = 1 THEN convert(varchar(200), 'SELF')
ELSE IsNull(ml.Description,'')
END,
'87.Ins2_OrderForClaims' = IsNull(pi.OrderForClaims,0),
'88.Ins2_CompanyName' = IsNull(ic.Name,''),
'150.Ins2_Address' = IsNull(ic.Address1,'') + IsNull(' '+ ic.Address2,''),
'151.Ins2_CSZ' = IsNull(ic.City,'') + IsNull(', ' + ic.State,'') + IsNull(' ' + ic.Zip,''),
'152.Ins2_Phone'= ISNULL(dbo.formatphone(ic.phone1,1),'')

FROM PatientProfile pp
INNER JOIN PatientInsurance pi ON pp.PatientProfileID = pi.PatientProfileID AND (pi.Inactive IS NULL OR pi.Inactive = 0) AND pi.OrderForClaims = 2
LEFT JOIN MedLists ml ON pi.PatRelToInsuredMId = ml.MedListsID
LEFT JOIN MedLists ml2 ON pp.PatientRelationToGuarantorMId = ml2.MedListsID
LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersID = ic.InsuranceCarriersID AND (ic.ReferenceCarrier = 0 OR ic.ReferenceCarrier is null)
WHERE pp.PatientProfileID = @pPatientProfileId

INSERT #Contacts_tmp

SELECT PatientProfileId
First
Middle
Last
Address1
Address2
City
State
Zip
Phone1
Phone1Type
Phone2
Phone2Type
ContactRelToPatient
Created

FROM PatientContacts pc
WHERE pc.PatientProfileId = @pPatientProfileId
ORDER BY Created

SELECT '90.Contact1First' = IsNull(First,''),
'91.Contact1Middle' = IsNull(Middle,''),
'92.Contact1Last' = IsNull(Last,''),
'93.Contact1Phone1' = IsNull(Phone1,''),
'111.Contact1Phone1Type'= ISNULL(phone1type,''),
'112.Contact1Phone2' = IsNull(Phone2,''),
'113.Contact1Phone2Type'= ISNULL(phone2type,''),
'114.Contact1Address1'= ISNULL(address1,''),
'115.Contact1Address2'= ISNULL(address2,''),
'116.Contact1City'= ISNULL(city,''),
'117.Contact1State'= ISNULL(state,''),
'118.Contact1Zip'= ISNULL(zip,''),
'94.Contact1RelToPatient' = IsNull(ContactRelToPatient,'')

FROM #Contacts_tmp
WHERE id_col = 1

SELECT '95.Contact2First' = IsNull(First,''),
'96.Contact2Middle' = IsNull(Middle,''),
'97.Contact2Last' = IsNull(Last,''),
'98.Contact2Phone' = IsNull(Phone1,''),
'119.Contact2Phone1Type'= ISNULL(phone1type,''),
'120.Contact2Phone2' = IsNull(Phone2,''),
'121.Contact2Phone2Type'= ISNULL(phone2type,''),
'122.Contact2Address1'= ISNULL(address1,''),
'123.Contact2Address2'= ISNULL(address2,''),
'124.Contact2City'= ISNULL(city,''),
'125.Contact2State'= ISNULL(state,''),
'126.Contact2Zip'= ISNULL(zip,''),
'99.Contact2RelToPatient' = IsNull(ContactRelToPatient,'')

FROM #Contacts_tmp
WHERE id_col = 2

SELECT '100.Contact3First' = IsNull(First,''),
'101.Contact3Middle' = IsNull(Middle,''),
'102.Contact3Last' = IsNull(Last,''),
'103.Contact3Phone' = IsNull(Phone1,''),
'127.Contact3Phone1Type'= ISNULL(phone1type,''),
'128.Contact3Phone2' = IsNull(Phone2,''),
'129.Contact3Phone2Type'= ISNULL(phone2type,''),
'130.Contact3Address1'= ISNULL(address1,''),
'131.Contact3Address2'= ISNULL(address2,''),
'132.Contact3City'= ISNULL(city,''),
'133.Contact3State'= ISNULL(state,''),
'134.Contact3Zip'= ISNULL(zip,''),
'104.Contact3RelToPatient' = IsNull(ContactRelToPatient,'')

FROM #Contacts_tmp
WHERE id_col = 3

SELECT '105.AppointmentDoctor' = d.ListName,
'106.AppointmentStartTime' = a.ApptStart,
'141.AppointmentType'=IsNull(at.Name,''),
'142.ApptNotes'=IsNull(CONVERT(varchar(255),a.Notes),''),
'143.StartHour'=IsNull(convert(varchar(2),datepart(hour,a.ApptStart)),''),
'144.StartMinute'=IsNull(convert(varchar(2),datepart(minute,a.ApptStart)),''),
'145.StopHour'=IsNull(convert(varchar(2),datepart(hour,a.ApptStop)),''),
'146.StopMinute'=IsNull(convert(varchar(2),datepart(minute,a.ApptStop)),'')

FROM Appointments a
LEFT JOIN DoctorFacility d ON a.DoctorId = d.DoctorFacilityId
LEFT JOIN ApptType at ON a.ApptTypeId = at.ApptTypeId

WHERE a.AppointmentsId = @pAppointmentsId

SELECT '110.ReportFooter' = ''

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-06-03 : 17:08:53

Oh my bad i found - commas help! Please disregard

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources