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 |
Corobori
Posting Yak Master
105 Posts |
Posted - 2008-07-21 : 14:40:18
|
I am rewriting an old application and moving a whole bunch of select queries to Stored Procedure.In my original application I was building the SQL Statement dynamically prior of running it. I am trying to find out the best practice to do it in SQL Server.Here is a pseudo-code of what I want:@MyParamNum1 int = 0@MyParamNum2 int = 0Select col1, col2, col3 From tblMyTableWhere Status=1if @MyParamNum1 <> 0 then and col4=@MyParamNum1end if if @MyParamNum2 <> 0 then and col5=@MyParamNum2end ifjean-lucwww.corobori.com |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-21 : 14:50:44
|
Select col1, col2, col3 From tblMyTableWhere Status=1and (col4=@MyParamNum1 or @MyParamNum1=0)and (col5=@MyParamNum2 or @MyParamNum2=0) |
|
|
boyfriendalex
Starting Member
15 Posts |
Posted - 2008-07-22 : 02:07:39
|
quote: Originally posted by Corobori I am rewriting an old application and moving a whole bunch of select queries to Stored Procedure.In my original application I was building the SQL Statement dynamically prior of running it. I am trying to find out the best practice to do it in SQL Server.Here is a pseudo-code of what I want:@MyParamNum1 int = 0@MyParamNum2 int = 0Select col1, col2, col3 From tblMyTableWhere Status=1if @MyParamNum1 <> 0 then and col4=@MyParamNum1end if if @MyParamNum2 <> 0 then and col5=@MyParamNum2end ifjean-lucwww.corobori.com
can u explain more details about your problem?so you need to build dynamical query with variables in procedure? |
|
|
Corobori
Posting Yak Master
105 Posts |
Posted - 2008-07-22 : 13:24:58
|
quote: Originally posted by boyfriendalexcan u explain more details about your problem?so you need to build dynamical query with variables in procedure?
Yes. With optional variablesI also need to parse a string. Say my user are going to send as a search string "Toyota Corolla" I have to build the where statement either WHERE myTxtField like '%Toyota%' OR myTxtField like '%Corolla%' and the other option WHERE myTxtField like '%Toyota%' AND myTxtField like '%Corolla%' Up to now it is being done in VB where I am preparint the complete SQL Statement but for security reason I want/need to do that in a SPjean-lucwww.corobori.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-22 : 14:33:50
|
quote: Originally posted by Corobori
quote: Originally posted by boyfriendalexcan u explain more details about your problem?so you need to build dynamical query with variables in procedure?
Yes. With optional variablesI also need to parse a string. Say my user are going to send as a search string "Toyota Corolla" I have to build the where statement either WHERE myTxtField like '%Toyota%' OR myTxtField like '%Corolla%' and the other option WHERE myTxtField like '%Toyota%' AND myTxtField like '%Corolla%' Up to now it is being done in VB where I am preparint the complete SQL Statement but for security reason I want/need to do that in a SPjean-lucwww.corobori.com
will you be having another parameter to decide if you want AND/OR for search strings given? |
|
|
Corobori
Posting Yak Master
105 Posts |
Posted - 2008-07-24 : 10:23:25
|
quote: Originally posted by visakh16will you be having another parameter to decide if you want AND/OR for search strings given?
Yes, they are about half a dozen of parameters involved in the where statement. Say 5 are simple ones, by simple I mean numerical Ids (mycategoryid=3) and the one left is a search in a string field where I can have any number of search strings where either all string or any string must are too be searchedjean-lucwww.corobori.com |
|
|
Corobori
Posting Yak Master
105 Posts |
Posted - 2008-07-25 : 15:14:57
|
I haved solved this issue. Thanks to all.jean-lucwww.corobori.com |
|
|
|
|
|