Search with wildcards from Stored proc parameter

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » Search with wildcards from Stored proc...

Search with wildcards from Stored proc parameter

Rate Topic

Display Mode

Topic Options

Author

Message

Digs

Posted Thursday, September 02, 2010 6:05 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, April 12, 2013 12:27 PM
Points: 416, Visits: 542

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 Smile

Post #979990

Evil Kraig F

Posted Thursday, September 02, 2010 6:18 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 PM
Points: 5,722, Visits: 6,194

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

- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #979994

Bhuvnesh

Posted Monday, September 06, 2010 5:11 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #981033

Digs

Posted Monday, September 06, 2010 2:11 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, April 12, 2013 12:27 PM
Points: 416, Visits: 542

Roger that !, Thanks ! Smile

Post #981238

« Prev Topic | Next Topic »

Permissions