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
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.<br /><br />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. <br /><br />Try this procedure if you need NULL values returned:<br /><br />——————————————————<br />create proc test<br />@field1 varchar(10)= NULL <br /><br />as<br /><br />select * from table1<br />where ISNULL( field , ” ) = ISNULL( @field1 , ISNULL( field , ” ) )<br />——————————————————<br /><br />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.<br /><br /><br /><br />– 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) = ‘%’
SET NOCOUNT ON IF ISNULL(@field1, ”) = ”
SELECT * — here I suggest you specify the columns you need
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.