Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Search with wildcards from Stored proc parameter Expand / Collapse
Author
Message
Posted Thursday, September 2, 2010 6:05 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 4:00 PM
Points: 420, Visits: 560
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

Post #979990
Posted Thursday, September 2, 2010 6:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:53 PM
Points: 5,383, Visits: 7,456
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
Posted Monday, September 6, 2010 5:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:01 AM
Points: 2,840, Visits: 3,968
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
Post #981033
Posted Monday, September 6, 2010 2:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 4:00 PM
Points: 420, Visits: 560
Roger that !, Thanks !
Post #981238
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse