Ok, i don't know if this belongs in C#, Software, or Databases but since I'm generally asking how to code the procedure optimally, I figured I would put it here.
Currently I'm working on a fairly large project encompassing all order taking, fulfillment, Credits, Reporting, etc for a company and I know the design of the database currently is cracked, but I'm going to change it soon. The project has grown from just a simple little database design of only 2 or 3 tables, to being massive and I really need to clean it up desperately, and I'm working on it. One of the things that I'm really struggling with is how to write a single statement to pass optional parameters to. Such As:
I have a form and each and every value is optional, but at least one option must be selected. Depending on the options selected a different view is given, all the same fields in the view. I have that code written. What I don't want to have to do is write a stored procedure with a whole bunch of if statements or a whole bunch of SQL statements contained within a stored procedure, but I'm at a loss for another direction to go in. The code that I'm working off of is this:



what I would like, if possible is something like this:

and the parameters and optionlist would be generated within the procedure. I just can't find the syntax or how best to do this. I was thinking about a temptable, and filtering it via each option in the list that was not null, but I don't know how well that would work, or if there is a better way of doing that.

Thanks for the advice, and sorry for the lengthy post, just the only way that I knew to fully ask the question.

You can make optional parameters this way



Then you can use the ISNULL Function in Transact-SQL like so



And this should give you what you're looking for.

Hope this helps

Happy Coding!

This post has been edited by PsychoCoder: 17 Aug, 2007 - 08:06 AM

I will try what you have and see if that does what I want. I want to be able to make it so that the options that are passed either contains a value or has a null value, and a check is done internal to the procedure and executes accordingly.

tody4me,

I got the syntax backwards so dont try it until you read this!

It should be



By the way, you can also use COALESCE



But for huge queries ISNULL is faster.

Psycho,
I tried the above code that was supplied, and I also read up on the syntax and function of what that is supposed to, or expected to, do. I need a flexible command statement, based on the optional parameters that are passed in. Right now, similar code is done in the program, and just the final command string is passed back to the database. What i want is to pass the parameters to the procedure and have the procedure figure out what it should be asking for. I was thinking about doing this with a bunch of if statements, but I didn't know if there was a better way. What I'm trying to accomplish is writing a statement that lengthens or shortens based on what is not null, and the isnull will test for that, but wants to fill in the blank with something.

Here's an example to hopefully better describe what I want.
If a user selects only credits with a total of 1000 or less, I would like to have a final statement of
select <fieldList (predetermined)> from <table> where [Credit] = 1 and [Total] < 1000
Or if a user selects AccountName of XYZ Co and Date Range of YTD then the statement would be something like
select <fieldList> from <table> where [AccountName] = 'XYZ Co' and [Date] between <beg date range> and <end date range>

I hope that this clarifies better as to what i'm wanting to do.

I have decided to go the temptable route, since i can check that the parameter is null, then filter it down for anything that is not null and pass back the set of data that is remaining after all filters are applied.

Thanks for taking the time to read the post, and to psycho for making an attempt to help.

you could just set all the values to null initially, then do something like this

if ((ISNULL(stuff)) ^ (ISNULL(stuff)) ^ (ISNULL(stuff))) and so on, the exclusive or will not allow them all to be left null

I had a hard time getting the ISNULL(column1,@column1) to work so I ended up with

((@column1 IS NULL) OR (column1 IN (@column1)))

I had to use IN as I was passing a comma delimited string to the stored procedure.

Check this website:

http://www.pureobjects.com/tips/FilteredSQLsp.aspx

It explains the answer