Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fnsmedia
Starting Member
14 Posts |
 Posted - 2002-12-05 : 11:30:40
|
| Please help - I am trying to convert an sql query on an asp page into a stored procedure. The query includes a LIKE statement and a variable with wildcards, the relevant line is:AND Cmp_Name LIKE '%varCompName%' How do stored procedures handle variables like this that contain wildcards? |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-12-05 : 11:52:40
|
| Stored Procedures support T-SQL what ever you query you can write and call from asp using say ado, you can do in a stored procedure. You can process multiple statements in one stored procedure which is good for encapsulation transactions for example.Here is something to get you started you can search the main page for more articles on stored procedures. There are numerous benefits to having your sql in stored procedures.http://www.sqlteam.com/item.asp?ItemID=563 |
 |
|
|
fnsmedia
Starting Member
14 Posts |
Posted - 2002-12-06 : 04:10:59
|
| Thanks, I have read around this quite a lot but haven't come across anything that tells me how to deal with wildcards within stored procedure variables. |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-12-06 : 04:30:25
|
| Hi!You can send the string in as a parameter to the procedure, like this:exec mysp @cmp_name='thacompany'In the procedure, you do something like this:AND Cmp_Name LIKE '%' + @cmp_name + '%'Quite straight forward. |
|
|
|
fnsmedia
Starting Member
14 Posts |
Posted - 2002-12-06 : 06:17:51
|
| Cheers, just reached that conclusion myselfsimple when you know how :-) |
|
|
|
Rangi
Starting Member
18 Posts |
Posted - 2002-12-06 : 06:39:55
|
| There is some useful info on TSQL wildcards at:http://manuals.sybase.com/onlinebooks/group-as/srg1100e/sqlref/@Generic__BookView;pt=57582?DwebQuery=wildcardAlso, if you are trying to search for a single word/phrase in a column that may contain that word at the start, middle or end of the field, you can use:SELECT * FROM mytable WHERE (PATINDEX('%[^a-z]' + @my_search_term + '[^a-z]%', ' ' + field_i_am_searching + ' ') != 0)This will find 'a' in 'I went to a park' but importantly it will not return rows where the 'a' appears in a work like in 'I went to the park' (the 'a' in 'park' doesn't count as an occurence of the character 'a').RangiEdited by - Rangi on 12/06/2002 06:40:41 |
|
|
|
fnsmedia
Starting Member
14 Posts |
Posted - 2002-12-09 : 12:00:12
|
| cheers for the replies |
|
|
|
|
|
|
wildcard with stored procedure variables