0

I like a lot of the built in features the built-in DataSet designer provides in VS2010 and do not want to have to change to something entirely different if at all possible. The problem is, to have optional parameters, I need to create completely independent functions for each combination of parameters. So for 6 parameters, I would need 63 different functions. That's obviously completely unmanageable.

Is there a way to have one function which ONLY adds a parameter to the generated WHERE clause of my SQL if it has a value, and otherwise, it ignores it?

just.another.programmer
  • 8,579
  • 8
  • 51
  • 90

1 Answers1

2

You could add these optional paremeters into the WHERE-Clause with ISNULL:

WHERE (YourTable.Column = ISNULL(@Column, YourTable.Column))

On this way the SQL works with or without the (optional) parameter. If the value is null it won't affect the result. You only have to add all parameters into the DataAdapter's parameter-collection(the optional with AllowDbNull=true). But i'm fairly sure that they will be automatically added correctly.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • How would I add this to auto-gen DataSet designer code (xsd)? – just.another.programmer Oct 05 '11 at 14:04
  • Why do you want to add something? The parameters should be created automatically when they are in the `Where-Clause`. – Tim Schmelter Oct 05 '11 at 14:23
  • Brilliant! I somehow misunderstood what you were suggesting the first time. Do you have any idea of the performance impact of using so many ISNULL's and truisms (ie Table.Column=Table.Column)? – just.another.programmer Oct 05 '11 at 14:34
  • It shouldn't impact the performance, have a look at following article for a comparison between `COALESCE`(what you could use just as well),`ISNULL` and the "natural" `IS NULL OR ...`: http://blogs.x2line.com/al/archive/2004/03/01/189.aspx – Tim Schmelter Oct 05 '11 at 14:57
  • I just realized, there's a big advantage to using the Table.Column IS NULL syntax over the ISNULL syntax. If you have a null value in the column in the DB, using ISNULL will not return the row (because of ANSI NULL comparison rules). If you use IS NULL, it will return the row either way (more of the expected behavior in this case). – just.another.programmer Oct 05 '11 at 16:03