Link to home
Start Free TrialLog in
Avatar of mugsey
mugseyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

t-sql wildcard example

I need to write a stored procedure that searches a list of products in a table.

Now each product row in the table has a PRODUCT DESCRIPTION field that is a varchar(255)

I want to pass in one or more keywords and do a search on the product description field.

So for example if I passed in a word "computer" then the sproc would search the PRODUCT DESCRIPTION field for anything like computer.

Also ideally I would want to pass in more than one word OR just one word
Avatar of chapmandew
chapmandew
Flag of United States of America image

create procedure usp_search
(
@searchval varchar(255)
)

declare @x varchar(255)
set @x = 'smith'
select * from tablename where productdescription like  '%' + @x + '%'
create procedure myproc @keyword varchar(4000)
as
select *
from products_table
where [product description] like '%'+@keyword+'%'
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
if you want to search for more than one word it can be a bit more compicated
do you have a maximum number of keywords you need to support or do you want to be able to support any number of keywords ?
Avatar of mugsey

ASKER

Hi thanks for everyone's comments

Ideally I would want to pass in more than one word - however I will try your suggestions and get back to you all asap.

Avatar of mugsey

ASKER

HI

The value I want to pass in comes from a textbox in an asp.net app

So would I need to do something like?

@searchCriteria varchar(50)

I am not sure why I need to declare a variable as varchar(255) or 4000

varchar(50) is probably fine for what you are trying to do...
when you define a varchar you have to give it's maximum length
sql server 2000 max length is 8000 for varchar and 4000 for nvarchar (which is good for unicode strings)
it has no impact on performance if your variable is declared varchar(50) or varchar(4000)
so it is better to define it as varchar(4000) in my option because this way
if you choose to enlarge your text box someday, you won't have to go and update the procedure as well
Avatar of mugsey

ASKER

I am just passing in one or two words for example, "computer" or "dvd"

OR

"Computer" AND "DVD"

How can I cater for this?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial