IF Statements in Stored Procedures

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • mmurphmsu
    New Member
    • Nov 2006
    • 19
    #1

    IF Statements in Stored Procedures

    I am trying to use IF statements in a Stored Procedure that would allow me to test for Null values. See Code below:

    CREATE PROCEDURE SRS_ARREPORTING
    @Phys_Start VarChar(4), @Phys_End VarChar(4), @Loc_Start VarChar(2), @Loc_End VarChar(2),
    @Start_Proc_Cod e VarChar(5), @End_Proc_Code VarChar(5), @FC_Start VarChar(2), @FC_End VarChar(2),
    @Dept_Start VarChar(2), @Dept_End VarChar(2), @DOS_Start Char(10), @DOS_End Char(10), @Post_Start Char(10), @Post_End Char(10)

    AS



    IF ((@Post_Start is Null ) AND (@Post_End is Null))

    BEGIN

    SELECT OI_CHARGES.PAT_ NBR, MPI_PAT.DISPLAY _NM, OI_CHARGES.POST _DT, OI_CHARGES.DT_O F_SERVICE, OI_CHARGES.PROC _CD,
    OI_CHARGES.PROC _CD_MOD, OI_CHARGES.PERF _PHYS_NBR, OI_CHARGES.PERF _DEPT_CD, OI_CHARGES.SERV _LOCATION,
    OI_CHARGES.DIAG _CD, OI_INS_CLAIM.IN S_CARRIER, OI_CHARGES.BILL ED_AMT, OI_CHARGES.OPEN _AMT, OI_CHARGES.INS_ RESP_AMT,
    OI_CHARGES.INS_ PAID_AMT, OI_CHARGES.INS_ ADJ_AMT, OI_CHARGES.PAT_ RESP_AMT, OI_CHARGES.PAT_ PAID_AMT, OI_CHARGES.PAT_ ADJ_AMT,
    OI_CHARGES.NBR_ UNITS, PC_DESC.PROC_CD _DESC_1, INS_CARRIER.CAR RIER_NM, OI_CHARGES.ORIG _FC
    FROM MPI AS MPI_PAT INNER JOIN
    PATIENT ON MPI_PAT.MPI_SET = PATIENT.MPI_SET AND MPI_PAT.MPI_NBR = PATIENT.MPI_NBR INNER JOIN
    OI_CHARGES ON PATIENT.CUST_NB R = OI_CHARGES.CUST _NBR AND PATIENT.CUST_NB R = OI_CHARGES.CUST _NBR AND
    PATIENT.PAT_NBR = OI_CHARGES.PAT_ NBR INNER JOIN
    OI_CLAIM_CHARGE S INNER JOIN
    OI_INS_CLAIM ON OI_CLAIM_CHARGE S.INSURANCE_ID = OI_INS_CLAIM.IN SURANCE_ID ON
    OI_CHARGES.CHAR GE_ID = OI_CLAIM_CHARGE S.CHARGE_ID AND OI_CHARGES.RESP _GUAR_NBR = OI_CLAIM_CHARGE S.RESP_GUAR_NBR AND
    OI_CHARGES.SUPP _PAT_NBR = OI_CLAIM_CHARGE S.SUPP_PAT_NBR AND OI_CHARGES.CUST _NBR = OI_CLAIM_CHARGE S.CUST_NBR INNER JOIN
    PC_DESC ON OI_CHARGES.PROC _CD = PC_DESC.PC_PROC _CD AND OI_CHARGES.PROC _CD_DEPT = PC_DESC.PC_DEPT _CD INNER JOIN
    INS_CARRIER ON OI_INS_CLAIM.IN S_CARRIER = INS_CARRIER.CAR RIER_NBR
    WHERE (OI_CHARGES.PER F_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND (OI_CHARGES.SER V_LOCATION BETWEEN @Loc_Start AND
    @Loc_End) AND (OI_CHARGES.PRO C_CD BETWEEN @Start_Proc_Cod e AND @End_Proc_Code) AND (OI_CHARGES.CUR RENT_FC BETWEEN
    @FC_Start AND @FC_End) AND (OI_CHARGES.PER F_DEPT_CD BETWEEN @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORI G_FC BETWEEN
    @FC_Start AND @FC_End) AND (OI_CHARGES.PER F_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND
    (OI_CHARGES.SER V_LOCATION BETWEEN @Loc_Start AND @Loc_End) AND (OI_CHARGES.PRO C_CD BETWEEN @Start_Proc_Cod e AND
    @End_Proc_Code) AND (OI_CHARGES.CUR RENT_FC BETWEEN @FC_Start AND @FC_End) AND (OI_CHARGES.PER F_DEPT_CD BETWEEN
    @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORI G_FC BETWEEN @FC_Start AND @FC_End) AND (OI_CHARGES.DT_ OF_SERVICE BETWEEN
    @DOS_Start AND @DOS_End)
    GROUP BY OI_CHARGES.PAT_ NBR, MPI_PAT.DISPLAY _NM, OI_CHARGES.DT_O F_SERVICE, OI_CHARGES.PROC _CD, OI_CHARGES.PROC _CD_MOD,
    OI_CHARGES.PERF _PHYS_NBR, OI_CHARGES.PERF _DEPT_CD, OI_CHARGES.SERV _LOCATION, OI_CHARGES.DIAG _CD,
    OI_INS_CLAIM.IN S_CARRIER, OI_CHARGES.POST _DT, OI_CHARGES.BILL ED_AMT, OI_CHARGES.OPEN _AMT, OI_CHARGES.INS_ RESP_AMT,
    OI_CHARGES.INS_ PAID_AMT, OI_CHARGES.INS_ ADJ_AMT, OI_CHARGES.PAT_ RESP_AMT, OI_CHARGES.PAT_ PAID_AMT, OI_CHARGES.PAT_ ADJ_AMT,
    OI_CHARGES.NBR_ UNITS, PC_DESC.PROC_CD _DESC_1, INS_CARRIER.CAR RIER_NM, OI_CHARGES.ORIG _FC
    END

    IF ((@DOS_Start is Null) AND (@DOS_End is Null))

    BEGIN

    SELECT OI_CHARGES.PAT_ NBR, MPI_PAT.DISPLAY _NM, OI_CHARGES.POST _DT, OI_CHARGES.DT_O F_SERVICE, OI_CHARGES.PROC _CD,
    OI_CHARGES.PROC _CD_MOD, OI_CHARGES.PERF _PHYS_NBR, OI_CHARGES.PERF _DEPT_CD, OI_CHARGES.SERV _LOCATION,
    OI_CHARGES.DIAG _CD, OI_INS_CLAIM.IN S_CARRIER, OI_CHARGES.BILL ED_AMT, OI_CHARGES.OPEN _AMT, OI_CHARGES.INS_ RESP_AMT,
    OI_CHARGES.INS_ PAID_AMT, OI_CHARGES.INS_ ADJ_AMT, OI_CHARGES.PAT_ RESP_AMT, OI_CHARGES.PAT_ PAID_AMT, OI_CHARGES.PAT_ ADJ_AMT,
    OI_CHARGES.NBR_ UNITS, PC_DESC.PROC_CD _DESC_1, INS_CARRIER.CAR RIER_NM, OI_CHARGES.ORIG _FC
    FROM MPI AS MPI_PAT INNER JOIN
    PATIENT ON MPI_PAT.MPI_SET = PATIENT.MPI_SET AND MPI_PAT.MPI_NBR = PATIENT.MPI_NBR INNER JOIN
    OI_CHARGES ON PATIENT.CUST_NB R = OI_CHARGES.CUST _NBR AND PATIENT.CUST_NB R = OI_CHARGES.CUST _NBR AND
    PATIENT.PAT_NBR = OI_CHARGES.PAT_ NBR INNER JOIN
    OI_CLAIM_CHARGE S INNER JOIN
    OI_INS_CLAIM ON OI_CLAIM_CHARGE S.INSURANCE_ID = OI_INS_CLAIM.IN SURANCE_ID ON
    OI_CHARGES.CHAR GE_ID = OI_CLAIM_CHARGE S.CHARGE_ID AND OI_CHARGES.RESP _GUAR_NBR = OI_CLAIM_CHARGE S.RESP_GUAR_NBR AND
    OI_CHARGES.SUPP _PAT_NBR = OI_CLAIM_CHARGE S.SUPP_PAT_NBR AND OI_CHARGES.CUST _NBR = OI_CLAIM_CHARGE S.CUST_NBR INNER JOIN
    PC_DESC ON OI_CHARGES.PROC _CD = PC_DESC.PC_PROC _CD AND OI_CHARGES.PROC _CD_DEPT = PC_DESC.PC_DEPT _CD INNER JOIN
    INS_CARRIER ON OI_INS_CLAIM.IN S_CARRIER = INS_CARRIER.CAR RIER_NBR
    WHERE (OI_CHARGES.PER F_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND (OI_CHARGES.SER V_LOCATION BETWEEN @Loc_Start AND
    @Loc_End) AND (OI_CHARGES.PRO C_CD BETWEEN @Start_Proc_Cod e AND @End_Proc_Code) AND (OI_CHARGES.CUR RENT_FC BETWEEN
    @FC_Start AND @FC_End) AND (OI_CHARGES.PER F_DEPT_CD BETWEEN @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORI G_FC BETWEEN
    @FC_Start AND @FC_End) AND (OI_CHARGES.PER F_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND
    (OI_CHARGES.SER V_LOCATION BETWEEN @Loc_Start AND @Loc_End) AND (OI_CHARGES.PRO C_CD BETWEEN @Start_Proc_Cod e AND
    @End_Proc_Code) AND (OI_CHARGES.PER F_DEPT_CD BETWEEN @Dept_Start AND @Dept_End)
    AND (OI_CHARGES.ORI G_FC BETWEEN @FC_Start AND @FC_End) AND (OI_CHARGES.POS T_DT BETWEEN @Post_Start AND @Post_End)
    GROUP BY OI_CHARGES.PAT_ NBR, MPI_PAT.DISPLAY _NM, OI_CHARGES.DT_O F_SERVICE, OI_CHARGES.PROC _CD, OI_CHARGES.PROC _CD_MOD,
    OI_CHARGES.PERF _PHYS_NBR, OI_CHARGES.PERF _DEPT_CD, OI_CHARGES.SERV _LOCATION, OI_CHARGES.DIAG _CD,
    OI_INS_CLAIM.IN S_CARRIER, OI_CHARGES.POST _DT, OI_CHARGES.BILL ED_AMT, OI_CHARGES.OPEN _AMT, OI_CHARGES.INS_ RESP_AMT,
    OI_CHARGES.INS_ PAID_AMT, OI_CHARGES.INS_ ADJ_AMT, OI_CHARGES.PAT_ RESP_AMT, OI_CHARGES.PAT_ PAID_AMT, OI_CHARGES.PAT_ ADJ_AMT,
    OI_CHARGES.NBR_ UNITS, PC_DESC.PROC_CD _DESC_1, INS_CARRIER.CAR RIER_NM, OI_CHARGES.ORIG _FC

    END

    IF ((@DOS_Start is Null) AND (@DOS_End is Null) AND (@Post_Start is Null ) AND (@Post_End is Null))

    BEGIN

    SELECT OI_CHARGES.PAT_ NBR, MPI_PAT.DISPLAY _NM, OI_CHARGES.POST _DT, OI_CHARGES.DT_O F_SERVICE, OI_CHARGES.PROC _CD,
    OI_CHARGES.PROC _CD_MOD, OI_CHARGES.PERF _PHYS_NBR, OI_CHARGES.PERF _DEPT_CD, OI_CHARGES.SERV _LOCATION,
    OI_CHARGES.DIAG _CD, OI_INS_CLAIM.IN S_CARRIER, OI_CHARGES.BILL ED_AMT, OI_CHARGES.OPEN _AMT, OI_CHARGES.INS_ RESP_AMT,
    OI_CHARGES.INS_ PAID_AMT, OI_CHARGES.INS_ ADJ_AMT, OI_CHARGES.PAT_ RESP_AMT, OI_CHARGES.PAT_ PAID_AMT, OI_CHARGES.PAT_ ADJ_AMT,
    OI_CHARGES.NBR_ UNITS, PC_DESC.PROC_CD _DESC_1, INS_CARRIER.CAR RIER_NM, OI_CHARGES.ORIG _FC
    FROM MPI AS MPI_PAT INNER JOIN
    PATIENT ON MPI_PAT.MPI_SET = PATIENT.MPI_SET AND MPI_PAT.MPI_NBR = PATIENT.MPI_NBR INNER JOIN
    OI_CHARGES ON PATIENT.CUST_NB R = OI_CHARGES.CUST _NBR AND PATIENT.CUST_NB R = OI_CHARGES.CUST _NBR AND
    PATIENT.PAT_NBR = OI_CHARGES.PAT_ NBR INNER JOIN
    OI_CLAIM_CHARGE S INNER JOIN
    OI_INS_CLAIM ON OI_CLAIM_CHARGE S.INSURANCE_ID = OI_INS_CLAIM.IN SURANCE_ID ON
    OI_CHARGES.CHAR GE_ID = OI_CLAIM_CHARGE S.CHARGE_ID AND OI_CHARGES.RESP _GUAR_NBR = OI_CLAIM_CHARGE S.RESP_GUAR_NBR AND
    OI_CHARGES.SUPP _PAT_NBR = OI_CLAIM_CHARGE S.SUPP_PAT_NBR AND OI_CHARGES.CUST _NBR = OI_CLAIM_CHARGE S.CUST_NBR INNER JOIN
    PC_DESC ON OI_CHARGES.PROC _CD = PC_DESC.PC_PROC _CD AND OI_CHARGES.PROC _CD_DEPT = PC_DESC.PC_DEPT _CD INNER JOIN
    INS_CARRIER ON OI_INS_CLAIM.IN S_CARRIER = INS_CARRIER.CAR RIER_NBR
    WHERE (OI_CHARGES.PER F_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND (OI_CHARGES.SER V_LOCATION BETWEEN @Loc_Start AND
    @Loc_End) AND (OI_CHARGES.PRO C_CD BETWEEN @Start_Proc_Cod e AND @End_Proc_Code) AND (OI_CHARGES.CUR RENT_FC BETWEEN
    @FC_Start AND @FC_End) AND (OI_CHARGES.PER F_DEPT_CD BETWEEN @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORI G_FC BETWEEN
    @FC_Start AND @FC_End) AND (OI_CHARGES.PER F_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND
    (OI_CHARGES.SER V_LOCATION BETWEEN @Loc_Start AND @Loc_End) AND (OI_CHARGES.PRO C_CD BETWEEN @Start_Proc_Cod e AND
    @End_Proc_Code) AND (OI_CHARGES.PER F_DEPT_CD BETWEEN
    @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORI G_FC BETWEEN @FC_Start AND @FC_End)
    GROUP BY OI_CHARGES.PAT_ NBR, MPI_PAT.DISPLAY _NM, OI_CHARGES.DT_O F_SERVICE, OI_CHARGES.PROC _CD, OI_CHARGES.PROC _CD_MOD,
    OI_CHARGES.PERF _PHYS_NBR, OI_CHARGES.PERF _DEPT_CD, OI_CHARGES.SERV _LOCATION, OI_CHARGES.DIAG _CD,
    OI_INS_CLAIM.IN S_CARRIER, OI_CHARGES.POST _DT, OI_CHARGES.BILL ED_AMT, OI_CHARGES.OPEN _AMT, OI_CHARGES.INS_ RESP_AMT,
    OI_CHARGES.INS_ PAID_AMT, OI_CHARGES.INS_ ADJ_AMT, OI_CHARGES.PAT_ RESP_AMT, OI_CHARGES.PAT_ PAID_AMT, OI_CHARGES.PAT_ ADJ_AMT,
    OI_CHARGES.NBR_ UNITS, PC_DESC.PROC_CD _DESC_1, INS_CARRIER.CAR RIER_NM, OI_CHARGES.ORIG _FC

    END

    ELSE

    BEGIN

    SELECT OI_CHARGES.PAT_ NBR, MPI_PAT.DISPLAY _NM, OI_CHARGES.POST _DT, OI_CHARGES.DT_O F_SERVICE, OI_CHARGES.PROC _CD,
    OI_CHARGES.PROC _CD_MOD, OI_CHARGES.PERF _PHYS_NBR, OI_CHARGES.PERF _DEPT_CD, OI_CHARGES.SERV _LOCATION,
    OI_CHARGES.DIAG _CD, OI_INS_CLAIM.IN S_CARRIER, OI_CHARGES.BILL ED_AMT, OI_CHARGES.OPEN _AMT, OI_CHARGES.INS_ RESP_AMT,
    OI_CHARGES.INS_ PAID_AMT, OI_CHARGES.INS_ ADJ_AMT, OI_CHARGES.PAT_ RESP_AMT, OI_CHARGES.PAT_ PAID_AMT, OI_CHARGES.PAT_ ADJ_AMT,
    OI_CHARGES.NBR_ UNITS, PC_DESC.PROC_CD _DESC_1, INS_CARRIER.CAR RIER_NM, OI_CHARGES.ORIG _FC
    FROM MPI AS MPI_PAT INNER JOIN
    PATIENT ON MPI_PAT.MPI_SET = PATIENT.MPI_SET AND MPI_PAT.MPI_NBR = PATIENT.MPI_NBR INNER JOIN
    OI_CHARGES ON PATIENT.CUST_NB R = OI_CHARGES.CUST _NBR AND PATIENT.CUST_NB R = OI_CHARGES.CUST _NBR AND
    PATIENT.PAT_NBR = OI_CHARGES.PAT_ NBR INNER JOIN
    OI_CLAIM_CHARGE S INNER JOIN
    OI_INS_CLAIM ON OI_CLAIM_CHARGE S.INSURANCE_ID = OI_INS_CLAIM.IN SURANCE_ID ON
    OI_CHARGES.CHAR GE_ID = OI_CLAIM_CHARGE S.CHARGE_ID AND OI_CHARGES.RESP _GUAR_NBR = OI_CLAIM_CHARGE S.RESP_GUAR_NBR AND
    OI_CHARGES.SUPP _PAT_NBR = OI_CLAIM_CHARGE S.SUPP_PAT_NBR AND OI_CHARGES.CUST _NBR = OI_CLAIM_CHARGE S.CUST_NBR INNER JOIN
    PC_DESC ON OI_CHARGES.PROC _CD = PC_DESC.PC_PROC _CD AND OI_CHARGES.PROC _CD_DEPT = PC_DESC.PC_DEPT _CD INNER JOIN
    INS_CARRIER ON OI_INS_CLAIM.IN S_CARRIER = INS_CARRIER.CAR RIER_NBR
    WHERE (OI_CHARGES.PER F_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND (OI_CHARGES.SER V_LOCATION BETWEEN @Loc_Start AND
    @Loc_End) AND (OI_CHARGES.PRO C_CD BETWEEN @Start_Proc_Cod e AND @End_Proc_Code) AND (OI_CHARGES.CUR RENT_FC BETWEEN
    @FC_Start AND @FC_End) AND (OI_CHARGES.PER F_DEPT_CD BETWEEN @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORI G_FC BETWEEN
    @FC_Start AND @FC_End) AND (OI_CHARGES.PER F_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND
    (OI_CHARGES.SER V_LOCATION BETWEEN @Loc_Start AND @Loc_End) AND (OI_CHARGES.PRO C_CD BETWEEN @Start_Proc_Cod e AND
    @End_Proc_Code) AND (OI_CHARGES.PER F_DEPT_CD BETWEEN
    @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORI G_FC BETWEEN @FC_Start AND @FC_End) AND (OI_CHARGES.DT_ OF_SERVICE BETWEEN
    @DOS_Start AND @DOS_End) AND (OI_CHARGES.POS T_DT BETWEEN @Post_Start AND @Post_End)
    GROUP BY OI_CHARGES.PAT_ NBR, MPI_PAT.DISPLAY _NM, OI_CHARGES.DT_O F_SERVICE, OI_CHARGES.PROC _CD, OI_CHARGES.PROC _CD_MOD,
    OI_CHARGES.PERF _PHYS_NBR, OI_CHARGES.PERF _DEPT_CD, OI_CHARGES.SERV _LOCATION, OI_CHARGES.DIAG _CD,
    OI_INS_CLAIM.IN S_CARRIER, OI_CHARGES.POST _DT, OI_CHARGES.BILL ED_AMT, OI_CHARGES.OPEN _AMT, OI_CHARGES.INS_ RESP_AMT,
    OI_CHARGES.INS_ PAID_AMT, OI_CHARGES.INS_ ADJ_AMT, OI_CHARGES.PAT_ RESP_AMT, OI_CHARGES.PAT_ PAID_AMT, OI_CHARGES.PAT_ ADJ_AMT,
    OI_CHARGES.NBR_ UNITS, PC_DESC.PROC_CD _DESC_1, INS_CARRIER.CAR RIER_NM, OI_CHARGES.ORIG _FC

    END

    GO






    When I run this with DOS START & END being NULL no results are given, same goes with POST START & END. Do I not have the code set right?

    Matt
    Tags: None
    • Delerna
      Recognized Expert Top Contributor
      • Jan 2008
      • 1134
      #2
      This is not the problem you are mentioning but....

      You are checking the null state of 4 variables
      From what I can see, your proc will return
      3 seperate recordsets if all 4 variables are null.
      Did you mean that to happen?
      I am thinking that the 3 if conditions should be checking the state
      of all 4 variables, like this
      IF @Post_Start is Null AND @Post_End is Null and @DOS_Start is not Null AND @DOS_End is not Null

      IF @Post_Start is not Null AND @Post_End is not Null and @DOS_Start is Null AND @DOS_End is Null

      IF @Post_Start is Null AND @Post_End is Null and @DOS_Start is Null AND @DOS_End is Null


      As to the problem you are asking about.
      I don't think it is in the way you have the If statements constructed.
      It is too difficult to decipher what is going wrong in what you have posted on our end.
      Try and narrow down the problem from your end.

      Some suggestions
      Using query analyser
      Copy the query from the first if statement in your proc.
      Declare the variables and set them up to the values required for the first if statement in your proc to be entered
      Run the query and see if it behaves as expected.
      If not, then there is something wrong with the query itself.
      Strip the query down to its simplest form (selecting from just 1 table)
      Test it and see if it works as expected. If it does add the next table, etc etc until it fails.

      Now you have narrowed the problem down.
      Why did it Fail?
      Check the tables contents
      Check your join fields are correct
      etc

      Do you get what I mean?
      Always analyse queries that don't work by starting as simple as possible and gradually building it back up to the desired final query.
      Thats how I do it, and I haven't been beaten by a query.......yet !!!

        Comment

        • Delerna
          Recognized Expert Top Contributor
          • Jan 2008
          • 1134
          #3
          By the way, those queries look very much identical to me.
          I am too lazy to decipher it for you but I suspect the only difference between them is in the where clause and maybe a field or 2 in the select clause, based on which of the 4 variables is null.

          If that is the case I feel you maybe able to do it with a single query and handle the condition of the variable being null within the query itself.

          For example
          WHERE field1=isnull(@ Var1,@Var2) and field2=isnull(@ Var3,@Var4)

          to handle both bein null
          isnull(@Var1,is null(@Var2,@Def aultValue))
          or
          isnull(isnull(@ Var1,@Var2),@De faultValue)



          In the select
          SELECT isnull(field1,f ield2) as FieldName

            Comment

            Previous template Next
            Working...