worked fine in SQL 2005 and in SQL 2008, I get error

This report worked fine in SQL 2005 and in SQL 2008, I get this error.

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' '.
Msg 137, Level 15, State 1, Line 7
Must declare the scalar variable "@groupby1".
Msg 137, Level 15, State 1, Line 8
Must declare the scalar variable "@groupby2".
Msg 102, Level 15, State 1, Line 138
Incorrect syntax near ' '.
SET NOCOUNT ON

DECLARE
    @groupby1 varchar(60),    
    @groupby2 varchar(60)

SELECT @groupby1 = convert(varchar(60),'None')
SELECT @groupby2 = convert(varchar(60),'None')

SELECT  
                pp.PatientProfileId,
                ISNULL(pp.[First], '') AS [First],
                ISNULL(pp.Middle, '') AS Middle,
                pp.[Last],
                dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName,
                ISNULL(race.Description, 'Unknown') AS Race,
                ISNULL(pp.Sex, '') AS Gender,
                pp.Birthdate,
                ImmunCodes.Code AS ImmunCode,
                ImmunCodes.Description AS ImmunDescription,
                ISNULL(ImmunCats.Category,CAST(ImmunCodes.CatCode AS VARCHAR(2)) + ' (undefined in cusImmunCategories)') AS Category,
                pvp.DateOfServiceFrom AS ImmunDte,
                pvp.Code AS ProcCode,
                pvp.CPTCode AS ProcCPTCode,
                pvp.Description AS ProcDescription,
                dbo.cusCETfCalcAge(pp.Birthdate, pvp.DateOfServiceFrom) AS AgeYears,
                dbo.cusCalcAgeMonths(pp.Birthdate, pvp.DateOfServiceFrom) AS AgeMonths,
--             dbo.cusCalcAgeYearsMonths(AgeYears, AgeMonths) AS AgeAtImmun,
                ImmunCats.ListOrder,
                pv.PatientVisitId,
                pv.TicketNumber,
                pvp.PatientVisitProcsId,
                cipv.cusImmunPatVisitID AS ImmunID,
                ImmunCodes.cusImmunCodesID,
                f.ListName AS FacilityName,
                pv.FacilityID,
                d.ListName AS Doctor,
                RTRIM(pvp.Code) + ' - ' + ImmunCodes.Description AS Description,
                ISNULL(pv.PrimaryInsuranceCarriersId,0)AS PrimaryInsuranceCarriersId,
                ISNULL(ic.ListName,'Self Pay') AS PrimaryInsuranceCarrier,
                ISNULL(pv.FinancialClassMId,0) AS FinancialClassMId, 
                ISNULL(fc.Description,  'Unknown') AS FinancialClass, 
                ISNULL(pt.Description, 'No Policy Type') AS PolicyType,
                case @groupby1
                                when 'Facility' then f.ListName
                                when 'Provider' then d.ListName
                                when 'Insurance' then ISNULL(ic.ListName,'Self Pay') 
                                when 'Policy Type' then ISNULL(pt.Description, 'No Policy Type') 
                                when 'Financial Class' then ISNULL(fc.Description,  'Unknown')
                                when 'Immunization Category' then ImmunCats.Category
                                when 'Patient Race' then ISNULL(race.Description, 'Unknown') 
                                when 'Patient Name' then dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix)
                                else 'None'
                end as Group1,
                case @groupby2
                                when 'Facility' then f.ListName
                                when 'Provider' then d.ListName
                                when 'Insurance' then ISNULL(ic.ListName,'Self Pay') 
                                when 'Policy Type' then ISNULL(pt.Description, 'No Policy Type') 
                                when 'Financial Class' then ISNULL(fc.Description,  'Unknown')
                                when 'Immunization Category' then ImmunCats.Category
                                when 'Patient Race' then ISNULL(race.Description, 'Unknown') 
                                when 'Patient Name' then dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix)
                                else 'None'
                end as Group2
INTO #Tmp

FROM    
                PatientVisitProcs pvp
                INNER JOIN cusImmunCodes ImmunCodes ON pvp.CPTCode = ImmunCodes.Code
                LEFT OUTER JOIN cusImmunCategories ImmunCats ON ImmunCodes.CatCode = ImmunCats.CatCode
                INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
                INNER JOIN DoctorFacility f ON pv.FacilityID = f.DoctorFacilityID
                INNER JOIN DoctorFacility d ON pv.DoctorID = d.DoctorFacilityID
                INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
                LEFT JOIN Medlists race ON pp.RaceMId = race.MedListsId
                LEFT OUTER JOIN cusImmunPatVisit cipv ON pvp.PatientVisitProcsId = cipv.PatientVisitProcsID
                LEFT OUTER JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID 
                LEFT OUTER JOIN MedLists fc ON pv.FinancialClassMID = fc.MedListsID 
                LEFT OUTER JOIN Medlists pt ON ic.PolicyTypeMID = pt.MedlistsID

WHERE 
                pvp.DateofServiceFrom   >= ISNULL('01/01/2010','1/1/1900') AND pvp.DateofServiceFrom  < dateadd(day,1,ISNULL('12/31/2010','1/1/3000')) 
                

SELECT * FROM #Tmp

WHERE                 
    --Filter on Age
    (
    ([AgeYears] >= ('-1') AND [AgeYears] <= ('1'))     
    )

ORDER BY 
                TicketNumber

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
 
Ephraim WangoyaCommented:
try it like this
SET NOCOUNT ON

DECLARE @groupby1 varchar(60)
DECLARE @groupby2 varchar(60)

SET @groupby1 = 'None'
SET @groupby2 = 'None'

SELECT  
                pp.PatientProfileId,
                ISNULL(pp.[First], '') AS [First],
                ISNULL(pp.Middle, '') AS Middle,
                pp.[Last],
                dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName,
                ISNULL(race.Description, 'Unknown') AS Race,
                ISNULL(pp.Sex, '') AS Gender,
                pp.Birthdate,
                ImmunCodes.Code AS ImmunCode,
                ImmunCodes.Description AS ImmunDescription,
                ISNULL(ImmunCats.Category,CAST(ImmunCodes.CatCode AS VARCHAR(2)) + ' (undefined in cusImmunCategories)') AS Category,
                pvp.DateOfServiceFrom AS ImmunDte,
                pvp.Code AS ProcCode,
                pvp.CPTCode AS ProcCPTCode,
                pvp.Description AS ProcDescription,
                dbo.cusCETfCalcAge(pp.Birthdate, pvp.DateOfServiceFrom) AS AgeYears,
                dbo.cusCalcAgeMonths(pp.Birthdate, pvp.DateOfServiceFrom) AS AgeMonths,
--             dbo.cusCalcAgeYearsMonths(AgeYears, AgeMonths) AS AgeAtImmun,
                ImmunCats.ListOrder,
                pv.PatientVisitId,
                pv.TicketNumber,
                pvp.PatientVisitProcsId,
                cipv.cusImmunPatVisitID AS ImmunID,
                ImmunCodes.cusImmunCodesID,
                f.ListName AS FacilityName,
                pv.FacilityID,
                d.ListName AS Doctor,
                RTRIM(pvp.Code) + ' - ' + ImmunCodes.Description AS Description,
                ISNULL(pv.PrimaryInsuranceCarriersId,0)AS PrimaryInsuranceCarriersId,
                ISNULL(ic.ListName,'Self Pay') AS PrimaryInsuranceCarrier,
                ISNULL(pv.FinancialClassMId,0) AS FinancialClassMId, 
                ISNULL(fc.Description,  'Unknown') AS FinancialClass, 
                ISNULL(pt.Description, 'No Policy Type') AS PolicyType,
                case @groupby1
                                when 'Facility' then f.ListName
                                when 'Provider' then d.ListName
                                when 'Insurance' then ISNULL(ic.ListName,'Self Pay') 
                                when 'Policy Type' then ISNULL(pt.Description, 'No Policy Type') 
                                when 'Financial Class' then ISNULL(fc.Description,  'Unknown')
                                when 'Immunization Category' then ImmunCats.Category
                                when 'Patient Race' then ISNULL(race.Description, 'Unknown') 
                                when 'Patient Name' then dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix)
                                else 'None'
                end as Group1,
                case @groupby2
                                when 'Facility' then f.ListName
                                when 'Provider' then d.ListName
                                when 'Insurance' then ISNULL(ic.ListName,'Self Pay') 
                                when 'Policy Type' then ISNULL(pt.Description, 'No Policy Type') 
                                when 'Financial Class' then ISNULL(fc.Description,  'Unknown')
                                when 'Immunization Category' then ImmunCats.Category
                                when 'Patient Race' then ISNULL(race.Description, 'Unknown') 
                                when 'Patient Name' then dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix)
                                else 'None'
                end as Group2
INTO #Tmp

FROM    
                PatientVisitProcs pvp
                INNER JOIN cusImmunCodes ImmunCodes ON pvp.CPTCode = ImmunCodes.Code
                LEFT OUTER JOIN cusImmunCategories ImmunCats ON ImmunCodes.CatCode = ImmunCats.CatCode
                INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
                INNER JOIN DoctorFacility f ON pv.FacilityID = f.DoctorFacilityID
                INNER JOIN DoctorFacility d ON pv.DoctorID = d.DoctorFacilityID
                INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
                LEFT JOIN Medlists race ON pp.RaceMId = race.MedListsId
                LEFT OUTER JOIN cusImmunPatVisit cipv ON pvp.PatientVisitProcsId = cipv.PatientVisitProcsID
                LEFT OUTER JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersID = ic.InsuranceCarriersID 
                LEFT OUTER JOIN MedLists fc ON pv.FinancialClassMID = fc.MedListsID 
                LEFT OUTER JOIN Medlists pt ON ic.PolicyTypeMID = pt.MedlistsID

WHERE 
                pvp.DateofServiceFrom   >= ISNULL('01/01/2010','1/1/1900') AND pvp.DateofServiceFrom  < dateadd(day,1,ISNULL('12/31/2010','1/1/3000')) 
                

SELECT * FROM #Tmp

WHERE                 
    --Filter on Age
    (
    ([AgeYears] >= ('-1') AND [AgeYears] <= ('1'))     
    )

ORDER BY 
                TicketNumber

Open in new window

0
 
HainKurtSr. System AnalystCommented:
try replacing lines 3-4 with

DECLARE    @groupby1 varchar(60)    
DECLARE    @groupby2 varchar(60)
0
 
Jeff SAuthor Commented:
HainKurt -

Same Error.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
wdosanjosCommented:
I think you have some invalid chars in your script.  Try copying the version that is posted on your question and running it on your end.
0
 
HainKurtSr. System AnalystCommented:
copy ypur code into a text file save attach here in a new post...
close file, open file copy the code, paste into your editor and run again... maybe copy paste in/out to notepad removes some invalid characters :)
0
 
Jeff SAuthor Commented:
I have not forgotten this ... I will try the examples and report back.
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.