SQL Server Performance Forum – Threads Archive

using ‘like ‘%”

I am trying to make a stored proc where it has 1 input parameter and the select
query inside the sp used the parameter in its where clause. something like this… ———————————
create proc test
@field1 varchar(10)=’%’
as select * from table1
where field like @field1
——————————— the @field is supposed to be optional. so this select query
can actually return the whole table1 or depending on the paramter, return only sub-data.
I gave ‘%’ as the default value for @field1. I do not want to write the same select query
depending on the @field1 value. Does a query like "select * from table1 where field like ‘%’" is slower than "select * from table1"?

Both queries will perform a table scan. There is a difference though. if your field value is NULL, SELECT * from table will show it while SELECT * from table WHERE field LIKE ‘%’ will not. Bambola.

To answer your last question, yes, the like query is slower than the straight select without a where clause. Regarding your query, the @field1 value of ‘%’ won’t fully work as a wild card when passed as it won’t return rows with NULL values in column field. Try this procedure if you need NULL values returned: —————————————————— create proc test @field1 varchar(10)= NULL as select * from table1 where ISNULL( field , ” ) = ISNULL( @field1 , ISNULL( field , ” ) ) —————————————————— It’s performance will be slow in all cases[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />], but the data will be complete and correct. – Greg Haselmann

To find the NULL values it is enough to do WHERE field_name IS NULL.
As for which would be faster. From the test I ran I did not notice much of a difference
between the two. If you are passing only one variable to the stored procedure and column field1 is indexed, I’d suggest you write a simple IF statement.
CREATE PROCEDURE test
(
@field1 varchar(10) = ‘%’
)
AS
SET NOCOUNT ON IF ISNULL(@field1, ”) = ”
SELECT * — here I suggest you specify the columns you need
FROM table1
ELSE
SELECT *
FROM table1
WHERE field like @field1
If column is not indexed you could write it using ISNULL WHERE ISNULL(@field1, ”) = ” OR (field like @field1) OR COALESCE WHERE COALESCE(@field1, a_field ) = a_field Bambola.

]]>