January 30, 2013 at 8:41 am
What's wrong with my procedure? The error msg:
Msg 156, Level 15, State 1, Procedure sp_GetInapprContrFollowUpNoFollowup, Line 36
Incorrect syntax near the keyword 'if'.
Thanks
Create proc [dbo].[sp_GetInapprContrFollowUpNoFollowup]
@IncludeCasesWithFollowup tinyint
as
;with _InappContr as
(
SELECT distinct
f.HospitalID,
f.ClinicalReviewID,
f.PhysicianEntID,
v.FullNamePosition,
c.FollowupAnctionCategoryName,
f.FollowupActionCategoryID,
f.MRN,
ClosedDate,
DeptChairFollowup,
DeptChairDisagrees,
v.Department,
Case cr.overallcarefinal
when 3 then 'Inappropriate'
when 2 then 'Controversial'
end OverallCareFinal
FROM tblFPPEInappControCase f
Inner Join
vw_UniquePhysicianPosition v
On v.UniqueIDSystem=f.PhysicianEntID
Left join
lstFollowupActionCategory c
on f.FollowupActionCategoryID=c.FollowupActionCategoryID
inner join
tblClinicalReview cr
on
cr.ClinicalReviewID =f.ClinicalReviewID
)
if @IncludeCasesWithFollowup=1
select * from _InappContr
Else
select * from _InappContr where DeptChairFollowup is null
January 30, 2013 at 9:05 am
When you define a CTE, the select statement that uses the CTE has to come immidate after the CET's definition. In your code you use an if statement between the CTE definition and the select statement that used the CTE. The code bellow shows how you can do it without the if statement. Take into account that if you have a big table, this code could cause performance problems.
Create proc [dbo].[sp_GetInapprContrFollowUpNoFollowup]
@IncludeCasesWithFollowup tinyint
as
;with _InappContr as
(
SELECT distinct
f.HospitalID,
f.ClinicalReviewID,
f.PhysicianEntID,
v.FullNamePosition,
c.FollowupAnctionCategoryName,
f.FollowupActionCategoryID,
f.MRN,
ClosedDate,
DeptChairFollowup,
DeptChairDisagrees,
v.Department,
Case cr.overallcarefinal
when 3 then 'Inappropriate'
when 2 then 'Controversial'
end OverallCareFinal
FROM tblFPPEInappControCase f
Inner Join
vw_UniquePhysicianPosition v
On v.UniqueIDSystem=f.PhysicianEntID
Left join
lstFollowupActionCategory c
on f.FollowupActionCategoryID=c.FollowupActionCategoryID
inner join
tblClinicalReview cr
on
cr.ClinicalReviewID =f.ClinicalReviewID
)
select * from _InappContr
where @IncludeCasesWithFollowup=1 or DeptChairFollowup is null
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 30, 2013 at 9:20 am
Thank you, but this does not solve the problem - we need to include or not to include records with null values using the variable that is sent to the procedure ...
January 30, 2013 at 9:24 am
Sorry Adi, it works ... Thanks a lot!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply