| Author |
 Topic  |
|
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 09/24/2007 : 19:17:51
|
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, ''
SELECT * FROM RPT_V_DISPJUDGEMENT WHERE CASE_NUMBER LIKE ''
--- 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 |
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 09/24/2007 : 20:24:39
|
So many problems. Where to start....
First, why are you using ISNULL(StringValue, 0)? You should use coalesce, and your substitution value should be a zero-length string, not an integer.
Now, replace this:
AND (ISNULL(LAST_NAME, 0) = ISNULL(@LAST_NAME, ISNULL(LAST_NAME, 0)))
...with this:
AND (LAST_NAME = @LAST_NAME OR @LAST_NAME IS NULL)
...and repeat for your other optional parameters.
Then, brush up on conditional logic, because this:
AND 1 = CASE
WHEN (@PARTY_ID IS NULL) THEN 1
WHEN (@PARTY_ID IS NOT NULL) THEN 1
ELSE 0
END
..is (obviously...) going to evaluate to 1 every time.
And you make the same mistake again here:
AND 1 = CASE
WHEN (@DATE_TIME_MOD IS NULL) THEN 1
WHEN (@DATE_TIME_MOD IS NOT NULL) THEN 1
ELSE 0
END
...and this mess:
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
...is logically equivalent to this:
AND (@LAST_NAME IS NULL
OR @LAST_NAME = ''
OR @LAST_NAME IS NOT NULL
OR @LAST_NAME <> '')
...which again is going to return TRUE every time, simply because the lines 1 and 2 complement eachother, and lines 2 and 3 complement eachother as well.
I think you need to scrap your code and go back to the drawing board, starting with a simple query and adding requirements from there.
e4 d5 xd5 Nf6 |
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 09/25/2007 : 09:04:45
|
True WTF material.
e4 d5 xd5 Nf6 |
|
|
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 09/25/2007 : 10:41:28
|
| Kristen, blindman Thank you for your suggestions, I am incorporating your suggestions. |
|
|
| |
Topic |
|
|
|
Bug in stored procedure
