Bug in stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Bug in stored procedure

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-09-24 : 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
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-09-24 : 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

22859 Posts

Posted - 2007-09-25 : 02:31:16

Blindman, sorry you've wasted your time, I already made all these suggestions on an earlier post by this OP

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89847

I think we are trying to push a rock up hill

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-09-25 : 09:04:45

True WTF material.

e4 d5 xd5 Nf6

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-09-25 : 10:41:28

Kristen, blindman Thank you for your suggestions, I am incorporating your suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources