September 24, 2007 at 5:17 pm
Guys,
I below is my stored procedure code, I want to handle null values and '' (empty strings) for @last_name variable.
But when I run the below stored procedure number of rows returned for null and '' (empty strings) is not the same even when I try to handle both scenarios from the below code.
EXEC SP_RPT_DISPJUDGEMENT CR, NULL, NULL
--- RETURNS 28 ROWS
EXEC SP_RPT_DISPJUDGEMENT CR, NULL, ''
--- STORED PROC SHOULD RETURN 28 ROWS (SINCE CODE SHOULD TREAT NULL AND '' AS SAME)
ALTER PROCEDURE SP_RPT_DISPJUDGEMENT
@CASE_NUMBER VARCHAR(20)=NULL,
@PARTY_ID BIGINT=NULL,
@LAST_NAME VARCHAR(250)=NULL,
@DATE_TIME_MOD DATETIME=NULL
AS
SELECT [CASE_ID],[CASE_NUMBER],[CASE_TYP_CD],[CASE_TYP_DESC],[CASE_STATUS_CD]
,[CASE_STS_DESC],[COURT_CD],[COURT_NAME],[PARTY_ID],[LAST_NAME],[MIDDLE_NAME]
,[FIRST_NAME],[DISPLAYNAME],[CI_ANOTD_OFNS_REF_NUM],[CI_ANOTD_OFNS_EFF_DT]
,[ANOTD_OFNS_EFF_DT],[ANOTD_OFNS_REF_NUM],[CNT_DISPOSITION_DT]
,[DI_ANOTD_OFNS_REF_NUM],[DI_ANOTD_OFNS_EFF_DT],[CI_OFFENSE_CLASS_CD]
,[CI_OFFENSE_CLASS_DESC],[CI_OFFENSE_TYP_CD],[CI_OFFENSE_TYP_DESC],[OFFENSE_CLASS_CD]
,[OFFENSE_CLASS_DESC],[OFFENSE_TYP_CD],[OFFENSE_TYP_DESC],[DI_OFFENSE_CLASS_CD]
,[DI_OFFENSE_CLASS_DESC],[DI_OFFENSE_TYP_CD],[DI_OFFENSE_TYP_DESC],[VCC_CD]
,[VCC_CHARGEDESCRIPTION],[DISPOSITION_DT],[DISPOSITION_CD],[DISPOSITION_DESC]
,[DISPOSITION_BY_CD],[DISPOSITION_BY_DESC]
,[CASE_STATUS_DT], [CASE_SHORT_TITLE], [CASE_FILING_DT]
,[DATE_TIME_MOD] AS CASE_DATE_TIM_MOD, [OFFENSE_DT_FRM], [OFFENSE_DT_TO]
,[ID]
FROM DBO.[RPT_V_DISPJUDGEMENT]
WHERE
NOT(COALESCE(CHARINDEX(@CASE_NUMBER, [CASE_NUMBER]), 0) = CAST(0 AS INT))
AND
(ISNULL(PARTY_ID, 0) = ISNULL(@PARTY_ID, ISNULL(PARTY_ID, 0)))
AND
(ISNULL(LAST_NAME, 0) = ISNULL(@LAST_NAME, ISNULL(LAST_NAME, 0)))
AND
(ISNULL(DATE_TIME_MOD, 0) = ISNULL(@DATE_TIME_MOD, ISNULL(DATE_TIME_MOD, 0)))
-- CASE TO CHECK IF NON REQUIRED FIELDS WHERE PASSED
AND
1=CASE
WHEN (@PARTY_ID IS NULL) THEN 1
WHEN (@PARTY_ID IS NOT NULL) THEN 1
ELSE 0
END
AND
1=CASE
WHEN (@LAST_NAME IS NULL OR @LAST_NAME = '') THEN 1
WHEN (@LAST_NAME IS NOT NULL OR @LAST_NAME <> '') THEN 1
ELSE 0
END
AND
1=CASE
WHEN (@DATE_TIME_MOD IS NULL) THEN 1
WHEN (@DATE_TIME_MOD IS NOT NULL) THEN 1
ELSE 0
END
ORDER BY CASE_NUMBER
GO
Any suggestions and inputs would help
Thanks
September 25, 2007 at 1:07 am
This looks strange:
1=CASE
WHEN (@LAST_NAME IS NULL OR @LAST_NAME = '') THEN 1
WHEN (@LAST_NAME IS NOT NULL OR @LAST_NAME '') THEN 1
ELSE 0
END
That basically means "never mind, whatever @last_name is, set result of CASE to 1". Why bother with it then, if it can't have any other result than 1?
The same applies to CASE with @PARTY_ID and @DATE_TIME_MOD.
Further, what is the CR that seems to be passed as a parameter number 1? As a string, it should be enclosed in single quotes... or am I missing something? Similarly, in the ISNULL expressions, you set parameters that are of VARCHAR datatype to value 0. You could set them to '0', if you need it, but in most situations this treatment works better if you set them to empty string.
Also, look up COALESCE - it works as ISNULL, but allows several parameters, which looks a lot better than nested ISNULL statements.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply