ASP - Stored Procedure best practice?

Hello. I was wondering if anyone would recommend or point me to a best
practice method in programming a search field on an .asp page that calls a
SQL Server stored procedure to do 'LIKE' searches? The only method that
seems to accommodate this that comes to my mind as of now is using dynamic
sql in the stored procedure but hear that it's not good practice using
dynamic sql.
Thanks in advance.
JYou can pass the comparison string in as a parameter in a parameterized
query. If you want a "wild-card search", just append '%' to the end of the
string before you pass it in. Also it's a good idea to *not* pre-pend '%'
to the front of the search string.
"J" <IDontLikeSpam@.Nowhere.com> wrote in message
news:%23otfgI$OHHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hello. I was wondering if anyone would recommend or point me to a best
> practice method in programming a search field on an .asp page that calls a
> SQL Server stored procedure to do 'LIKE' searches? The only method that
> seems to accommodate this that comes to my mind as of now is using dynamic
> sql in the stored procedure but hear that it's not good practice using
> dynamic sql.
> Thanks in advance.
> J
>|||Hi Mike.
Do you know of a recommended method in doing this for a form with multiple
fields which allows a user to specify more criteria on multiple fields?
Seems like building the dynamic sql statement in the stored procedure with
the specified field parameters is the only method I can think of to do
multiple criteria searching but I guess I still would like to avoid.
Thanks for your info and reply. Much appreciated.
J
"Mike C#" <xyz@.xyz.com> wrote in message
news:ee266d$OHHA.1248@.TK2MSFTNGP02.phx.gbl...
> You can pass the comparison string in as a parameter in a parameterized
> query. If you want a "wild-card search", just append '%' to the end of
> the string before you pass it in. Also it's a good idea to *not* pre-pend
> '%' to the front of the search string.
> "J" <IDontLikeSpam@.Nowhere.com> wrote in message
> news:%23otfgI$OHHA.4484@.TK2MSFTNGP02.phx.gbl...
>|||J (IDontLikeSpam@.Nowhere.com) writes:
> Do you know of a recommended method in doing this for a form with multiple
> fields which allows a user to specify more criteria on multiple fields?
> Seems like building the dynamic sql statement in the stored procedure with
> the specified field parameters is the only method I can think of to do
> multiple criteria searching but I guess I still would like to avoid.
I discuss a whole bunch of solutions in both dynamic and static SQL, as
well as some very interesting hybrids in an article on my web site:
http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Gonna take a look at this. Thanks Mike. Have a good weekend.
J
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98BDEE90C4CB3Yazorman@.127.0.0.1...
>J (IDontLikeSpam@.Nowhere.com) writes:
> I discuss a whole bunch of solutions in both dynamic and static SQL, as
> well as some very interesting hybrids in an article on my web site:
> http://www.sommarskog.se/dyn-search.html.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Gonna take a look at this. Thanks Mike. Have a good weekend.
J
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98BDEE90C4CB3Yazorman@.127.0.0.1...
>J (IDontLikeSpam@.Nowhere.com) writes:
> I discuss a whole bunch of solutions in both dynamic and static SQL, as
> well as some very interesting hybrids in an article on my web site:
> http://www.sommarskog.se/dyn-search.html.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sorry Erland. Didn't notice it was from someone else. Much thanks to you
also.
Take care.
J
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns98BDEE90C4CB3Yazorman@.127.0.0.1...
>J (IDontLikeSpam@.Nowhere.com) writes:
> I discuss a whole bunch of solutions in both dynamic and static SQL, as
> well as some very interesting hybrids in an article on my web site:
> http://www.sommarskog.se/dyn-search.html.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx