| Author |
Topic |
|
financemanager
Starting Member
9 Posts |
 Posted - 2008-03-30 : 10:22:24
|
| Hi All,Firstly thank you for taking time out and looking at this topic.I have a problem implementing a stored procedure......What i need to do is using a Stored Procedure pass a user defined search expression and return results from a table.basically user searches for a company name and based on other variables the procedure returns the relevant results.the search expression can be a combo of things.....Common company names....COMPANY A and ATMScompany nameCOMPANY A and COMPANY BCOMPANY A or COMPANY BThankyou for all your help. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 11:04:03
|
| i would suggest that you don't do these kinds of searches with a stored procedure.complex searches are one of the few places i reccomend using dynamicaly built parametrized sql in the app.you can of course create dynamic sql in your sproc but you have to be carefull not to allow sql injection._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
financemanager
Starting Member
9 Posts |
Posted - 2008-03-30 : 11:23:26
|
| Hi,I would agree however i have been given this task to do and this is how they want it done. i just dont know where to start.....i declare the variable and am lost i dont kow the select script for it....Select * from companyTBL where company = @search_expressionBut it needs to be dynamic....I want to search on any given word in the search... with AND OR's...Please help |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 11:28:43
|
| well you can't really do this dynamicaly inthe way you wish to.what exactly would be the user input?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
financemanager
Starting Member
9 Posts |
Posted - 2008-03-30 : 11:55:24
|
| Hi thanks for getting back to me....The user would input the name of the company or a combo of words for example......INPUT 1: BPINPUT 2: BP AND SHELLINPUT 3: SHELL OR BPI just need the correct way compile the SQL Script and Stored procedure..... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-30 : 12:22:10
|
| may be this:-Select * from companyTBL where (PATINDEX('%AND%',company)>0 AND company like '%' + REPLACE(REPLACE(@search_expression,' ',''),'AND','%') + '%' )OR (PATINDEX('%OR%',company)>0 AND company like '%[' + REPLACE(REPLACE(@search_expression,' ',''),'OR',']*%[') + ']*%' )OR (PATINDEX('%AND%',company)=0 AND PATINDEX('%OR%',company)=0 AND company = @search_expression) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 12:22:34
|
| this would requrie that you parse the input in the sproc and turn it into something like this:(you might use paramteres)INPUT 1: BPset @where = 'where companyName = ''BP'''INPUT 2: BP AND SHELLset @where = 'where companyName = ''BP'' and companyName = ''shell''' <- this is also a bit ridicolous since a column can't have 2 values at the same time.INPUT 3: SHELL OR BPset @where = 'where companyName = ''BP'' or companyName = ''shell'''after you parse your string you doexec('select * from yourTable ' + @where)you also have to find a way to handle mistypes and malicious tries (sql injection)._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
financemanager
Starting Member
9 Posts |
Posted - 2008-03-30 : 13:40:57
|
| thank you for all that has posted to my question, but what I really need is how to do a simple proximity search in sqlKind RegardsPete |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 13:58:35
|
| and in your case that would be what?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
financemanager
Starting Member
9 Posts |
Posted - 2008-03-30 : 14:03:40
|
| i have the code for user defined company search and implemented it successfully, now i want to be able to narrow the search on a geographical location..Kind RegardsPete |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 14:12:40
|
| wait wait... this is then a completly different issue than the one you started this thread with?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
financemanager
Starting Member
9 Posts |
Posted - 2008-03-30 : 14:14:58
|
| Sorry am new to this.....When i started the post i had a problem with a dynamic search but has been sorted since but now have issue with proximity search and need help with that.....if i should have started another threat please advise sorry if i should have....Kind RegardsPete |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 14:20:04
|
| yeah start another thread.how did you resolve the dynamic search?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
financemanager
Starting Member
9 Posts |
Posted - 2008-03-30 : 14:22:58
|
| please see post by visakh16 great input from him thankyou mate.....and thanks to all who viwed the post....Kind Regards |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-03-30 : 14:29:24
|
| as much as it's a great suggestion and a pretty cool solution, good luck debugging that when you have problems..._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Imukai
Starting Member
29 Posts |
Posted - 2008-03-31 : 06:47:31
|
| Is it too early in the morning for me to fully comprehend visakh16 or is that solution going to cause issues whenever you search for a company name that happens to contain the letters "AND" or "OR" ? |
|
|
|
|
Search Expression.....