SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Bug in stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 09/24/2007 :  19:17:51  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/25/2007 :  02:31:16  Show Profile  Reply with Quote
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
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 09/25/2007 :  09:04:45  Show Profile  Reply with Quote
True WTF material.

e4 d5 xd5 Nf6
Go to Top of Page

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 09/25/2007 :  10:41:28  Show Profile  Reply with Quote
Kristen, blindman Thank you for your suggestions, I am incorporating your suggestions.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000