views:

103

answers:

4

I have a select statement that is used in a gridview via a stored procedure:

SELECT * FROM T_Computer WHERE (ISTag LIKE '%' + @ISTag + '%') AND Status <> 'Trashed' AND Status <> 'Sold' AND Status <> 'Stored'

The @ISTag, an nchar is determined by a textbox value, if the textbox is empty I have the default value set to %, which in my mind should essentially negate that part of the where clause and return all of the rows except the ones negated by the Status column. Instead it isn't returning anything. If I remove the "+ @ISTag +" it does what I want.

Basically three %'s return nothing, but 2 %'s return what I want. How should I fix this?

+4  A: 

Here is an alternative, leaving the default value of the @ISTag parameter to NULL

SELECT * 
FROM T_Computer 
WHERE (@ISTag IS NULL OR (ISTag LIKE '%' + @ISTag + '%')) 
AND Status <> 'Trashed' 
AND Status <> 'Sold' 
AND Status <> 'Stored'
Dustin Laine
You'll want "IS NULL" instead of "ISNULL"
LittleBobbyTables
Thanks, typo. I fixed it.
Dustin Laine
A: 

is your subsitute % mark being escaped? you wouldn't get what you epect if the search is matching a literal % character.

also, there's no need to use the % for an empty field. %% should return the same results as %%%.

lincolnk
A: 

The problem is that two consecutive percent symbols (%%) cause it to match the literal '%' character. So 3 consecutive mean "match a percent symbol and then any characters thereafter".

As LittleBobby indicates, do not use '%' as your default for nulls.

mjmarsh
this is not true, as far as i can tell.
lincolnk
Escaping wildcards in SQL is done using the ESCAPE clause (ha ha). E.g. SELECT * FROM TT WHERE C LIKE 'e%' ESCAPE 'e'. This escapes the % so it can searched for in the column. Which means that if you might need to do WHERE C LIKE '%e%%' if you were looking for rows where a percent sign occurred.
Cyberherbalist
+2  A: 

I'm really intrigued by this. For a start, I ran these two queries against a table with 13221 rows, and got every single row in the first instance, and about half of them in the second:

declare @rad char(30)
select @rad = '%'

select count(*) from xofdoc where docdesc like '%%%'
select count(*) from xofdoc where docdesc like '%' + @rad + '%'

ON THE OTHER HAND, if I change the declaration of @rad to varchar(30), both queries pull all the rows.

The reason I bring this up is because there may be something going on with the data declaration of your @IsTag. You didn't say how it was declared. This is important because a char is a fixed-length string, meaning that a char(5) for example, set to the value of '%' will actually have the value '% '. So, the statement '%' + '% ' + '%' evaluates to: '%% %'. So it would only find rows which had at least four consecutive spaces in the whered column. On the other hand, a varchar is a variable length item, and trailing spaces are ignored, which results in '%%%' in the above case, and thus wildcards the entire column.

Edited to add: nchar or char, it makes no difference in this case. If you're using nchar now, change it to nvarchar.

Cyberherbalist
Changed it to varchar and it's all good. This is probably better in the long run anyways.
Shawn
Hey @Shawn, shameless plus here, if my answer was the one that did it for you, do you suppose you could mark it "Accepted"? :-) Much appreciated!
Cyberherbalist
Sorry, had tried but had to wait and hadn't been back till now. Thanks for the help.
Shawn