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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
22858 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  
 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