Why is it ignoring the dates - 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)

Why is it ignoring the dates

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-06-08 : 09:02:28

If I run this sp , when @strStatus is 2 or 3 it is not returning the correct records. Not all parameters will have a value.

CREATE PROCEDURE spSL_CallSearch

@strSurname nvarchar(100),
@strTelNo nvarchar(100),
@strAdd1 nvarchar(500),
@strCallDesc nvarchar(2000),
@strStatus integer

AS

If @strStatus = 1
BEGIN
Select CallNo,convert(varchar(20),CallNo) + ' ' + COALESCE(Surname,'Anon') + ' :: ' + COALESCE(Add1,'No Address') + '::'+ COALESCE(Call_desc,'No details') as Ticket from tblSL_Calls
WHERE
(Surname like '%'+ @strSurname+'%')and
(Tel_no like '%'+@strTelNo+'%')and
(Add1 like '%' + @strAdd1 + '%')and
(Call_desc like '%'+@strCallDesc+'%') and
(Completed_time is null) and
(Cancelled is null)
ORDER BY CallNo desc
END

If @strStatus= 2
BEGIN
Select CallNo,convert(varchar(20),CallNo) + ' ' + COALESCE(Surname,'Anon') + ' :: ' + COALESCE(Add1,'No Address') + '::'+ COALESCE(Call_desc,'No details') as Ticket from tblSL_Calls
WHERE
(Surname like '%'+ @strSurname+'%')and
(Tel_no like '%'+@strTelNo+'%)') and
(Add1 like '%' + @strAdd1 + '%') and
(Call_desc like '%'+@strCallDesc+'%') and
(NOT (Completed_time IS NULL))
ORDER BY CallNo desc
END

If @strStatus= 3
BEGIN
Select CallNo,convert(varchar(20),CallNo) + ' ' + COALESCE(Surname,'Anon') + ' :: ' + COALESCE(Add1,'No Address') + '::'+ COALESCE(Call_desc,'No details') as Ticket from tblSL_Calls
WHERE
(Surname like '%'+ @strSurname+'%)') and
(Tel_no like '%'+@strTelNo+'%') and
(Add1 like '%' + @strAdd1 + '%') and
(Call_desc like '%'+@strCallDesc+'%') and
(NOT (Cancelled IS NULL))
ORDER BY CallNo desc
END
GO

mr_mist
Grunnio

1870 Posts

Posted - 2005-06-08 : 09:05:53

More info please.

Your question is "why is it ignoring the dates" but there is no dreference to any date anywhere in your WHERE clause.

-------
Moo. :)

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-08 : 09:07:56

Please follow the link below so we can help

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-06-08 : 09:10:56

Sorry - completed_time and cancelled are dates. I'm rather stressed over this as I've been battling all day with it !

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-08 : 09:51:29

>>Not all parameters will have a value

Since your parameters don't have defaults defined, what is actually being passed to the procedure? My guess is NULLs.

That would make:

(Add1 like '%' + @strAdd1 + '%')

like this:

(Add1 like NULL)

which would never be satisfied. If that is not the problem, follow Brett's posting instructions:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-08 : 21:19:15

never begin your Stored Procedure with sp.

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-08 : 21:48:48

this will be fun....

quote:

never begin your Stored Procedure with sp

Why not raclede ?

Damian
Ita erat quando hic adveni.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources