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 = 0
Select col1, col2, col3 From tblMyTable Where Status=1 if @MyParamNum1 <> 0 then and col4=@MyParamNum1 end if if @MyParamNum2 <> 0 then and col5=@MyParamNum2 end if
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 = 0
Select col1, col2, col3 From tblMyTable Where Status=1 if @MyParamNum1 <> 0 then and col4=@MyParamNum1 end if if @MyParamNum2 <> 0 then and col5=@MyParamNum2 end if
quote:Originally posted by boyfriendalex can u explain more details about your problem? so you need to build dynamical query with variables in procedure?
Yes. With optional variables
I 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 SP
quote:Originally posted by boyfriendalex can u explain more details about your problem? so you need to build dynamical query with variables in procedure?
Yes. With optional variables
I 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 SP
quote:Originally posted by visakh16 will 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 searched
SP: dynamically building Select Statement
Posted - 07/21/2008 : 14:40:18
