locked
Dynamic PL/SQL for ASP.NET form RRS feed

  • Question

  • User-1934530859 posted

    I have a big form with a lot of fields. Every field is a potential selection in the where clause. Is there an easier or better way to this then have a million IF statements to dynamically build a SQL SELECT statement?

    Tuesday, March 22, 2011 9:33 AM

Answers

  • User904860486 posted

    Why have to write IF statement.

    Suppose you have a Textbox Say Id which will take part in where clause. Now you will pass TextBox.Text value to stored procedure with variable (say) @ID

    In stored procedure you can use this variable in your clause as

    where (@ID='' or ID=@ID) and ...

    where ID is column name in table you are referncing.

    In short if there are n variables as @Var1,@Var2,@Var3

    Your Where clause will be: where (@Var1='' or Var1=@Var1) and (@Var2='' or Var2=@Var2) and (@Var3='' or Var3=@Var3)

    where Var1,Var2,Var3 are column names

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 22, 2011 9:43 AM

All replies

  • User904860486 posted

    Why have to write IF statement.

    Suppose you have a Textbox Say Id which will take part in where clause. Now you will pass TextBox.Text value to stored procedure with variable (say) @ID

    In stored procedure you can use this variable in your clause as

    where (@ID='' or ID=@ID) and ...

    where ID is column name in table you are referncing.

    In short if there are n variables as @Var1,@Var2,@Var3

    Your Where clause will be: where (@Var1='' or Var1=@Var1) and (@Var2='' or Var2=@Var2) and (@Var3='' or Var3=@Var3)

    where Var1,Var2,Var3 are column names

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 22, 2011 9:43 AM
  • User-2098386135 posted
    There is some more discssion about the topic here -->http://forums.asp.net/t/1219987.aspx
    Tuesday, March 22, 2011 2:01 PM