Use variable in procedure with CTE

  • 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

  • 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/

  • 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 ...

  • 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