April 13, 2004 at 1:44 pm
Can someone tell me what my problem is, this should return a list of all records in the table CodesOffense, but I'm getting nothing. If I change to '%' it works, but setting @Find to '%' should work also shouldn't it?
Alter
Procedure spCodesOffenseListActive
(
@Find
as char(20)='%'
)
As
set
nocount on
Select
CodesOffense.offenseID, CodesOffense.StatuteReference + ' ' + CodesOffense.Abbrevation as OffenseText, CodesOffense.Severity, CodesOffense.Description
From
CodesOffense
WHERE
(Description LIKE @Find)
Order
by CodesOffense.Abbrevation
return
When this does
Alter
Procedure spCodesOffenseListActive
As
set nocount on
Select CodesOffense.offenseID, CodesOffense.StatuteReference + ' ' + CodesOffense.Abbrevation as OffenseText, CodesOffense.Severity, CodesOffense.Description
From
CodesOffense
WHERE
(Description LIKE '%')
Order
by CodesOffense.Abbrevation
return
April 13, 2004 at 2:38 pm
@Find should be a VARCHAR(20).
When you use CHAR(20) it pads out to 20 characters with 19 trailing spaces. So intead of getting '%', you're getting '% '.
April 15, 2004 at 2:12 am
What is wrong with this!!
Alter Procedure spCodesOffenseListActive
(
@Find
as varchar(20)=NULL
)
As
set
nocount on
if @Find is null or @Find=''
Select CodesOffense.offenseID, CodesOffense.StatuteReference + ' ' + CodesOffense.Abbrevation as OffenseText, CodesOffense.Severity, CodesOffense.Description
From
CodesOffense
--WHERE
(Description LIKE @Find)
Order
by CodesOffense.Abbrevation
else
Select CodesOffense.offenseID, CodesOffense.StatuteReference + ' ' + CodesOffense.Abbrevation as OffenseText, CodesOffense.Severity, CodesOffense.Description
From
CodesOffense
WHERE
(Description LIKE '%'+@Find+'%')
Order
by CodesOffense.Abbrevation
April 15, 2004 at 9:23 am
Not sure what you expect it to do.
It should work OK if @Find is not NULL.
If you pass a null @Find (or don't pass any value, which will default to NULL)
then you have a where clause: WHERE Description LIKE NULL
That always returns false, even if the Description field is null.
To detect a null field, you have to use WHERE Description IS NULL
Hope that helps
April 15, 2004 at 9:28 am
Thanks to ezan, I was using the wrong variable type.
I changed char to varchar and it works great.
Isn't it amazing how hard it is to see you own mistakes.
April 15, 2004 at 6:11 pm
change the char(20) to varchar(20) so that you won't be sending empty spaces.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply