COALESCE - ISNull, isnutty

[KOVMoe]

I use the following query to run series of pages. The user might choose a city, state, user, customer or address for filtering. They can choose one or all of the above. The query runs in Query Analyzer, but returns nothing to the page. I know the first thing is to say the info is not getting passed, but I do a CFDUMP form to see the variables are correct.

Any ideas as I bash my head into the screen?


CREATE PROCEDURE dbo.spPackageApps_EcsPostcard_ReportPostcard_FilterAdr
@intModuleID int
,@intUserID int
,@intLocationLevelID int
,@intLocationID int
,@dtmSelectedDate datetime
,@dtmEndDate datetime
,@strShrAcNr char(10)
,@strOrgAdrStrNr char(6)
,@strOrgAdrStrNa char(35)
,@strOrgAdrPolDiv2Na char(35)
,@strUsrNr char(10)
,@strReturnMessage varchar(800) output
AS

if @intLocationLevelID <> 50 begin
select @strReturnMessage = 'This report is only available at the center level.'
return 1
end
SELECT DAT_VAL_DT as xcp_dcv_dt, NVT_LBL_NR, pkg_alt_ref_nr,
usr_nr, xcp_rpt_loc_sys_nr, xcp_rpt_dt, shr_ac_nr, xcp_dcv_tm,
org_cus_na, org_ad_str_nr, org_ad_str_na,
org_ad_str_typ_cd, org_ad_bdg_flr_nr, org_ad_rm_sut_nr,
org_ad_pbx_nr, org_ad_rrl_rte_nr, org_ad_rrl_rte_pbx_nr,
org_ad_pol_div_2_na, org_ad_pol_div_1_cd,
org_ad_atn_na, org_ad_psl_1_cd, org_ad_psl_2_cd,
new_ad_atn_na, 'Resolved' as STS, 1 as Sts_ID, '' as gmt_fcr
FROM TECSRLV
WHERE DAT_VAL_DT between @dtmSelectedDate and @dtmEndDate
and xcp_rpt_loc_sys_nr = @intLocationID
and isnull(shr_ac_nr,1) = coalesce(@strShrAcNr, isnull(shr_ac_nr,1))
and isnull(org_ad_str_nr,2) = coalesce(@strOrgAdrStrNr, isnull(org_ad_str_nr,2))
and isnull(org_ad_str_na,3) = coalesce(@strOrgAdrStrNa, isnull(org_ad_str_na,3))
and isnull(org_ad_pol_div_2_na,4) = coalesce(@strOrgAdrPolDiv2Na, isnull(org_ad_pol_div_2_na,4))
and isnull(usr_nr,5) = coalesce(@strUsrNr, isnull(usr_nr,5))
UNION
SELECT DAT_VAL_DT as xcp_dcv_dt, NVT_LBL_NR, pkg_alt_ref_nr,
usr_nr, xcp_rpt_loc_sys_nr, xcp_rpt_dt,shr_ac_nr, xcp_dcv_tm,
org_cus_na, org_ad_str_nr, org_ad_str_na,
org_ad_str_typ_cd, org_ad_bdg_flr_nr, org_ad_rm_sut_nr,
org_ad_pbx_nr, org_ad_rrl_rte_nr, org_ad_rrl_rte_pbx_nr,
org_ad_pol_div_2_na, org_ad_pol_div_1_cd,
org_ad_atn_na, org_ad_psl_1_cd, org_ad_psl_2_cd,
'' as new_ad_atn_na, 'Pending' as STS, 2 as Sts_ID, gmt_fcr
FROM TECSPND
WHERE DAT_VAL_DT between @dtmSelectedDate and @dtmEndDate
and xcp_rpt_loc_sys_nr = @intLocationID
and isnull(shr_ac_nr,1) = coalesce(@strShrAcNr, isnull(shr_ac_nr,1))
and isnull(org_ad_str_nr,2) = coalesce(@strOrgAdrStrNr, isnull(org_ad_str_nr,2))
and isnull(org_ad_str_na,3) = coalesce(@strOrgAdrStrNa, isnull(org_ad_str_na,3))
and isnull(org_ad_pol_div_2_na,4) = coalesce(@strOrgAdrPolDiv2Na, isnull(org_ad_pol_div_2_na,4))
and isnull(usr_nr,5) = coalesce(@strUsrNr, isnull(usr_nr,5))

Thank you,

Moe-King of the Village Idiots.

"When in trouble,
when in doubt;
Run in circles-
SCREAM & SHOUT!!"
Burma Shave

 

[vongrunt]

COALESCE() with two parameters is the same as ISNULL(). To avoid confusion, use only one function for that purpose.

Check input values. '' instead of NULL can completely "shut down" this query.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

... and besides, why is @strShrAcNr declared as char(10) when it is compared against integer in this expression:

and isnull(shr_ac_nr,1) = coalesce(@strShrAcNr, isnull(shr_ac_nr,1))

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)