September 2, 2010 at 6:05 pm
I wish to allow the user to load in from a control the search input for the wildcard search in a SELECT query in a TSQL stored procedure.
MyStoredProc(
@SearchContains VARCHAR(20)=NULL,
@SearchStartsWith VARCHAR(20)=NULL)
NOTE: Field 'OrderName' is a VARCHAR field
SELECT OrderID, OrderName, OrderValue FROM <table>
WHERE OrderName = @SearchContains
or
SELECT OrderID, OrderName FROM <table>
WHERE OrderName = @SearchStartsWith
I wish for the @SearchStartsWith (or @SearchContains) to be the input to go in between the wild card parameters of WHERE clause search, but I dont know the parameters to get the job done in the two search examples above, please advise, thanks:-)
September 2, 2010 at 6:18 pm
MyStoredProc(
@SearchContains VARCHAR(20)=NULL,
@SearchStartsWith VARCHAR(20)=NULL)
NOTE: Field 'OrderName' is a VARCHAR field
SELECT OrderID, OrderName, OrderValue FROM <table>
WHERE OrderName like '%' + @SearchContains + '%'
or
SELECT OrderID, OrderName FROM <table>
WHERE OrderName like @SearchStartsWith + '%'
or
SELECT OrderID, OrderName FROM <table>
WHERE LEFT( OrderName, LEN(@SearchStartsWith)) = @SearchStartsWith
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 6, 2010 at 5:11 am
Craig Farrell (9/2/2010)
SELECT OrderID, OrderName FROM <table>WHERE LEFT( OrderName, LEN(@SearchStartsWith)) = @SearchStartsWith
this is bad from performance perspective , never use function with where caluse column , it will force sql optimizer to NOT TO use index
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 6, 2010 at 2:11 pm
Roger that !, Thanks !:-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply