By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,440 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

IF Statements in Stored Procedures

P: 19
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_Code 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_OF_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.INS_CARRIER, OI_CHARGES.BILLED_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.CARRIER_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_NBR = OI_CHARGES.CUST_NBR AND PATIENT.CUST_NBR = OI_CHARGES.CUST_NBR AND
PATIENT.PAT_NBR = OI_CHARGES.PAT_NBR INNER JOIN
OI_CLAIM_CHARGES INNER JOIN
OI_INS_CLAIM ON OI_CLAIM_CHARGES.INSURANCE_ID = OI_INS_CLAIM.INSURANCE_ID ON
OI_CHARGES.CHARGE_ID = OI_CLAIM_CHARGES.CHARGE_ID AND OI_CHARGES.RESP_GUAR_NBR = OI_CLAIM_CHARGES.RESP_GUAR_NBR AND
OI_CHARGES.SUPP_PAT_NBR = OI_CLAIM_CHARGES.SUPP_PAT_NBR AND OI_CHARGES.CUST_NBR = OI_CLAIM_CHARGES.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.INS_CARRIER = INS_CARRIER.CARRIER_NBR
WHERE (OI_CHARGES.PERF_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND (OI_CHARGES.SERV_LOCATION BETWEEN @Loc_Start AND
@Loc_End) AND (OI_CHARGES.PROC_CD BETWEEN @Start_Proc_Code AND @End_Proc_Code) AND (OI_CHARGES.CURRENT_FC BETWEEN
@FC_Start AND @FC_End) AND (OI_CHARGES.PERF_DEPT_CD BETWEEN @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORIG_FC BETWEEN
@FC_Start AND @FC_End) AND (OI_CHARGES.PERF_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND
(OI_CHARGES.SERV_LOCATION BETWEEN @Loc_Start AND @Loc_End) AND (OI_CHARGES.PROC_CD BETWEEN @Start_Proc_Code AND
@End_Proc_Code) AND (OI_CHARGES.CURRENT_FC BETWEEN @FC_Start AND @FC_End) AND (OI_CHARGES.PERF_DEPT_CD BETWEEN
@Dept_Start AND @Dept_End) AND (OI_CHARGES.ORIG_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_OF_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.INS_CARRIER, OI_CHARGES.POST_DT, OI_CHARGES.BILLED_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.CARRIER_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_OF_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.INS_CARRIER, OI_CHARGES.BILLED_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.CARRIER_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_NBR = OI_CHARGES.CUST_NBR AND PATIENT.CUST_NBR = OI_CHARGES.CUST_NBR AND
PATIENT.PAT_NBR = OI_CHARGES.PAT_NBR INNER JOIN
OI_CLAIM_CHARGES INNER JOIN
OI_INS_CLAIM ON OI_CLAIM_CHARGES.INSURANCE_ID = OI_INS_CLAIM.INSURANCE_ID ON
OI_CHARGES.CHARGE_ID = OI_CLAIM_CHARGES.CHARGE_ID AND OI_CHARGES.RESP_GUAR_NBR = OI_CLAIM_CHARGES.RESP_GUAR_NBR AND
OI_CHARGES.SUPP_PAT_NBR = OI_CLAIM_CHARGES.SUPP_PAT_NBR AND OI_CHARGES.CUST_NBR = OI_CLAIM_CHARGES.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.INS_CARRIER = INS_CARRIER.CARRIER_NBR
WHERE (OI_CHARGES.PERF_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND (OI_CHARGES.SERV_LOCATION BETWEEN @Loc_Start AND
@Loc_End) AND (OI_CHARGES.PROC_CD BETWEEN @Start_Proc_Code AND @End_Proc_Code) AND (OI_CHARGES.CURRENT_FC BETWEEN
@FC_Start AND @FC_End) AND (OI_CHARGES.PERF_DEPT_CD BETWEEN @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORIG_FC BETWEEN
@FC_Start AND @FC_End) AND (OI_CHARGES.PERF_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND
(OI_CHARGES.SERV_LOCATION BETWEEN @Loc_Start AND @Loc_End) AND (OI_CHARGES.PROC_CD BETWEEN @Start_Proc_Code AND
@End_Proc_Code) AND (OI_CHARGES.PERF_DEPT_CD BETWEEN @Dept_Start AND @Dept_End)
AND (OI_CHARGES.ORIG_FC BETWEEN @FC_Start AND @FC_End) AND (OI_CHARGES.POST_DT BETWEEN @Post_Start AND @Post_End)
GROUP BY OI_CHARGES.PAT_NBR, MPI_PAT.DISPLAY_NM, OI_CHARGES.DT_OF_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.INS_CARRIER, OI_CHARGES.POST_DT, OI_CHARGES.BILLED_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.CARRIER_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_OF_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.INS_CARRIER, OI_CHARGES.BILLED_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.CARRIER_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_NBR = OI_CHARGES.CUST_NBR AND PATIENT.CUST_NBR = OI_CHARGES.CUST_NBR AND
PATIENT.PAT_NBR = OI_CHARGES.PAT_NBR INNER JOIN
OI_CLAIM_CHARGES INNER JOIN
OI_INS_CLAIM ON OI_CLAIM_CHARGES.INSURANCE_ID = OI_INS_CLAIM.INSURANCE_ID ON
OI_CHARGES.CHARGE_ID = OI_CLAIM_CHARGES.CHARGE_ID AND OI_CHARGES.RESP_GUAR_NBR = OI_CLAIM_CHARGES.RESP_GUAR_NBR AND
OI_CHARGES.SUPP_PAT_NBR = OI_CLAIM_CHARGES.SUPP_PAT_NBR AND OI_CHARGES.CUST_NBR = OI_CLAIM_CHARGES.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.INS_CARRIER = INS_CARRIER.CARRIER_NBR
WHERE (OI_CHARGES.PERF_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND (OI_CHARGES.SERV_LOCATION BETWEEN @Loc_Start AND
@Loc_End) AND (OI_CHARGES.PROC_CD BETWEEN @Start_Proc_Code AND @End_Proc_Code) AND (OI_CHARGES.CURRENT_FC BETWEEN
@FC_Start AND @FC_End) AND (OI_CHARGES.PERF_DEPT_CD BETWEEN @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORIG_FC BETWEEN
@FC_Start AND @FC_End) AND (OI_CHARGES.PERF_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND
(OI_CHARGES.SERV_LOCATION BETWEEN @Loc_Start AND @Loc_End) AND (OI_CHARGES.PROC_CD BETWEEN @Start_Proc_Code AND
@End_Proc_Code) AND (OI_CHARGES.PERF_DEPT_CD BETWEEN
@Dept_Start AND @Dept_End) AND (OI_CHARGES.ORIG_FC BETWEEN @FC_Start AND @FC_End)
GROUP BY OI_CHARGES.PAT_NBR, MPI_PAT.DISPLAY_NM, OI_CHARGES.DT_OF_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.INS_CARRIER, OI_CHARGES.POST_DT, OI_CHARGES.BILLED_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.CARRIER_NM, OI_CHARGES.ORIG_FC

END

ELSE

BEGIN

SELECT OI_CHARGES.PAT_NBR, MPI_PAT.DISPLAY_NM, OI_CHARGES.POST_DT, OI_CHARGES.DT_OF_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.INS_CARRIER, OI_CHARGES.BILLED_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.CARRIER_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_NBR = OI_CHARGES.CUST_NBR AND PATIENT.CUST_NBR = OI_CHARGES.CUST_NBR AND
PATIENT.PAT_NBR = OI_CHARGES.PAT_NBR INNER JOIN
OI_CLAIM_CHARGES INNER JOIN
OI_INS_CLAIM ON OI_CLAIM_CHARGES.INSURANCE_ID = OI_INS_CLAIM.INSURANCE_ID ON
OI_CHARGES.CHARGE_ID = OI_CLAIM_CHARGES.CHARGE_ID AND OI_CHARGES.RESP_GUAR_NBR = OI_CLAIM_CHARGES.RESP_GUAR_NBR AND
OI_CHARGES.SUPP_PAT_NBR = OI_CLAIM_CHARGES.SUPP_PAT_NBR AND OI_CHARGES.CUST_NBR = OI_CLAIM_CHARGES.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.INS_CARRIER = INS_CARRIER.CARRIER_NBR
WHERE (OI_CHARGES.PERF_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND (OI_CHARGES.SERV_LOCATION BETWEEN @Loc_Start AND
@Loc_End) AND (OI_CHARGES.PROC_CD BETWEEN @Start_Proc_Code AND @End_Proc_Code) AND (OI_CHARGES.CURRENT_FC BETWEEN
@FC_Start AND @FC_End) AND (OI_CHARGES.PERF_DEPT_CD BETWEEN @Dept_Start AND @Dept_End) AND (OI_CHARGES.ORIG_FC BETWEEN
@FC_Start AND @FC_End) AND (OI_CHARGES.PERF_PHYS_NBR BETWEEN @Phys_Start AND @Phys_End) AND
(OI_CHARGES.SERV_LOCATION BETWEEN @Loc_Start AND @Loc_End) AND (OI_CHARGES.PROC_CD BETWEEN @Start_Proc_Code AND
@End_Proc_Code) AND (OI_CHARGES.PERF_DEPT_CD BETWEEN
@Dept_Start AND @Dept_End) AND (OI_CHARGES.ORIG_FC BETWEEN @FC_Start AND @FC_End) AND (OI_CHARGES.DT_OF_SERVICE BETWEEN
@DOS_Start AND @DOS_End) AND (OI_CHARGES.POST_DT BETWEEN @Post_Start AND @Post_End)
GROUP BY OI_CHARGES.PAT_NBR, MPI_PAT.DISPLAY_NM, OI_CHARGES.DT_OF_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.INS_CARRIER, OI_CHARGES.POST_DT, OI_CHARGES.BILLED_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.CARRIER_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
May 20 '08 #1
Share this Question
Share on Google+
2 Replies


Delerna
Expert 100+
P: 1,134
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!!!
May 20 '08 #2

Delerna
Expert 100+
P: 1,134
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,isnull(@Var2,@DefaultValue))
or
isnull(isnull(@Var1,@Var2),@DefaultValue)



In the select
SELECT isnull(field1,field2) as FieldName
May 20 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.