Stored Procedure and Joins

  • I am drawing a blank on this one, maybe someone out there can help.

    Below is my stored procedure...


    CREATE    PROCEDURE [dbo].[report__nonadmit_get]

     @agency_list varchar(2000) = null,

     @region_list varchar(2000) = null,

     @branch_list varchar(2000) = null,

     @lob_list varchar(2000) = null,

            @from_date datetime =null,

            @to_date datetime=null


    --Create Temp Agency table

                create table #Agency (agency_id int, agency_code varchar(6), agency_name varchar(35))

            if(@Agency_List is null)


                insert into #Agency

                select agency_id, agency_code, [name]as agency_name from agency




             insert into #Agency

            select agency_id, agency_code, [name]as agency_name from agency

            inner join dbo.udf_ParseIDs(@Agency_List) as s on s.entity_id=agency.agency_id


    --Create Temp Region Table

               create table #region (region_id int, region_code varchar(6), region_name varchar(35),agency_id int)

            if(@Region_List is  null)


                    insert into #region

                    select region_id, region_code, [name] as region_name, agency_id from region




            insert into #region

            select region_id, region_code, [name] as region_name , agency_id from region

            inner join dbo.udf_ParseIDs(@Region_List) as s on s.entity_id=region.region_id



    --Create Temp Branch Table

                create table #Branch (branch_id int, branch_code varchar(6),

                region_id int,branch_name varchar(35))

            if (@Branch_List is null)


                insert into #Branch

                select branch_id, branch_code, region_id, [name]as branch_name from branch




             insert into #Branch

             select branch_id, branch_code, region_id, [name]as branch_name from branch

            inner join dbo.udf_ParseIDs(@Branch_List) as s on s.entity_id=branch.branch_id


    -- Create Temp Line of Business (Lob) table

            create table #LOB (Lob_code char(6), lob_description varchar(30))

     if(@lob_list is null)


                insert into #LOB

                select lob_code, [description] as lob_description from lob




      select lob_code, description as lob_description from lob

     inner join  dbo.udf_parsecodes(@lob_list) as s on s.entity_id=lob.lob_code


    SELECT     patient.patient_code, patient.last_name, patient.first_name, patient.middle_initial, service_period_status.status_date, GETDATE() AS run_dt,

                 AS cname,

    rtrim(LOB.LOB_code) + ' - ' +  LOB.description as lname,

     RTRIM(agency.agency_code) + ' - ' + AS aname,

    case when patient.middle_initial is null then rtrim(patient.last_name) + ', ' + patient.first_name else

    rtrim(patient.last_name) + ', ' + rtrim(patient.first_name) + ' ' + patient.middle_initial end as pname,

    rtrim(branch.branch_code) + ' - ' + 

                 AS bname, rtrim(region.region_code) + ' - ' + AS rname,

    rtrim(service_period_status.not_accepted_reason) + ' - ' + not_accepted_reason_dict.description as reason,

    @from_date as dtFrom,

    @to_date as dtTo

    FROM         patient RIGHT OUTER JOIN

                          LOB RIGHT OUTER JOIN

                          service_period ON LOB.LOB_code = service_period.lob RIGHT OUTER JOIN

                          service_period_status ON service_period.service_period_id = service_period_status.service_period_id ON

                          patient.patient_id = service_period.patient_id LEFT OUTER JOIN

                          branch LEFT OUTER JOIN

                          company RIGHT OUTER JOIN

                          agency ON company.company_id = agency.company_id RIGHT OUTER JOIN

                          region ON agency.agency_id = region.agency_id ON branch.region_id = region.region_id ON service_period.branch_id = branch.branch_id

    left outer join not_accepted_reason_dict on service_period_status.not_accepted_reason  =


    inner join [#branch] on service_period.branch_id=#branch.branch_id or service_period.branch_id = NULL

    inner join [#region] ON [#branch].region_id = [#region].region_id

    inner join [#agency] ON [#region].agency_id = [#agency].agency_id

    inner join [#lob] ON service_period.lob = [#lob].lob_code or service_period.lob = NULL

    WHERE     (service_period_status.service_status = 'N')

    and ( service_period_status.status_date >=@from_date or @from_date is null)

    and ( service_period_status.status_date <=@to_date or @to_date is null)

    ORDER BY aname,rname,bname,lname,reason,pname



    You can see where it does inner joins on the temp tables agency, region, branch etc.....

    My problem is that if the user selects ALL agencies, regions or branches... I really want to do a left outer join. That way even if there are NULL values in the main table for these ID's, the rows will be returned.


    Any ideas?



  • How about using an IF...ELSE with 2 different select statements?

    Shamless self promotion - read my blog

  • I was thinking about that but was wondering if there was anything I could do within 1 sql statement.




  • What about building the SQL statement at run-time and using EXEC (@Sql).

  • It's a bit early in the morning for me to be thinking this hard but here goes...


    Use a left outer join

    then use a case to decide what data you want from the join based on null values in whatever columns - sorry the query was just to damn long to read the whole thing

    somehting like this

    left outer join table1

    on table1.column1 = table2.column1

    and case when (check for inner join flag) and table2.column1 is null then 1

    when (check for NOT inner join flag) and table2.column1 is not null then 1

    else 0

    end = 1

    which as I said may not make any sense whatsoever cause for me this is EARLY.  So let me go back to sleep and look at this tomorrow and maybe I can give a better example.

    Michael R. Schmidt

  • That is what I was shooting for, but I wasn't sure if I was able to use CASE statements in the JOINS.....

    I will give it a try though.


  • Hi Carol,

    I wonder if you solved the problem?


  • Actually, I have not had a chance to try it as of yet. I will do so this Monday and let you know.



  • Thanks to MikeyMikey's suggestion I got it to work..This is a simplied version.


    I have an employee table and an employee_region table. Not every employee will have an employee_region row.

    My problem was that I needed the ability to do a left outer join or an inner join depending on certain criteria.


    This is my solution

    declare @setit int ;

    set @setit = 0;

    --0 means just the records that have regions (21)

    --1 means all employees (27)

    select distinct employee.emp_id from employee

    left outer  join employee_region on

     employee.emp_id = employee_region.emp_id

    where  case when (@setit = 0) and employee_region.emp_id is not null then 1

    when (@setit = 1) and (employee_region.emp_id is null or employee_region.emp_id is not null)

     then 1

    else 0

    end = 1



  • Glad to be of assistance!


    I just love SQL, now Microsoft, where is that natural join syntax I've been waiting for, I'd like to save some typing!


    And wouldn't everyone just love it if SQL Server would optimize a simple IN clause into a more effecient join?  Hello SQL team, are you listening?

    Michael R. Schmidt

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply