Setting up Stored Procedures with Optional Parameters and Using a Strongly Typed TableAdapter to Execute the said Stored Procedure

Collection of information on optional paramters in Stored Procedures, as well as passing null values into the Fill method of a stongly typed data adapter.

Introduction

I know for a fact the bits and pieces of this article are scattered around this site as well as in other programming sites. What I'm going to try and do is bring them all together in a "from start to finish" solution.

Background

Building even a simple search engine for in-program data can be complicated at times; building an advanced search engine for the same in-program data where any or all of the parameters can be used can be a nightmare.

I would like to share my solution for this problem; however, bear in mind that I'm sure there is more than one way to do this. It's simply the most efficient way I've found (balancing speed and quickly implementing it).

Pre-Planning Stage

We have in existence a database for a tattoo shop manager that contains the following tables:

Part of the shop manager program is a Client Manager which allows the user to search (or, filter is actually a more correct term) the clients in the database based on various criteria.

Everything in the image above is optional; it can be searched with all or none of the criteria.

Plan of attack

One way to implement this is once the user clicks the Search Clients button, loop through all the controls above and check each of their values and build an inline SQL Select statement based on the values present.

This approach is fairly straightforward, and you never really have to leave your code. But there are some major set backs in my opinion.

My current WinForm has 30+ input controls used for the search engine; looping through, casting, and checking each control is going to take some time, not to mention probably a couple hundred lines of code to implement.

The search looks at multiple tables; having your code creating the proper joins can potentially be bad.

Adding or removing criteria have the potential to require massive rewriting of the SQL Select statement producing code.

Another way to implement this is to write Stored Procedures that cover every conceivable combination or search criteria.

This, of course, is completely impractical, and makes even less sense than the previous option, but I thought it should be mentioned that it is possible this way.

Some simple reasons why not to use this option:

The time it would take to think up and then write each Stored Procedure, you'd be a couple years older.

Developers implementing the search engine would need a binder to hold the list of Stored Procedures for reference.

Now to the third way of implementing this. To me, this is sort of a mid-ground between the first two options. Using one Stored Procedure, we supply all the possible search parameters, but we make each of them optional so that the Stored Procedure will execute with some, all, or none of the parameters. Using a strongly typed TableAdapter, we use the previous Stored Procedure to fill our search results dataset.

Using varchar allows us to do partial word searches since trailing whitespaces are automatically trimmed off of varchar strings (not so with nchar an char). Combine that with + '%', and you can now enter the first part of a word and it will still pull all the words that start with those letters. For instance:

If we pass 'jo' in as @ClientFirstName, the procedure would select "Joe", "John", "Joseph", etc.

One last thing to notice is the DISTINCT keyword before the select statements this ensures you don't get a long list of repeating clients.

Implementation - Step 2: Filling a Strongly Typed Dataset

Using Visual Studio's Dataset Designer, we create a strongly typed dataset with our Stored Procedure. The designer automatically creates a Fill() and a GetData() method based on the parameter's of the Stored Procedure.

Now, it's just a matter of inputting our values into the Fill() method.

I've wrapped each parameter on its own line for readability, but it's still just one function call. Adding or removing search options is as simple as adding or removing a parameter.

There two really nifty things I want to point out about the Fill() method, and they both involve one single character: ?.

? is the Conditional Operator, and has been around in one form or another for years (I can remember using it back when I was coding in plain old C).

If you think of if...else and select...case statements as the foot soldiers in your programming arsenal, they get the job done, but can sometimes be brutal and messy in their tactics, and then ? is part of your special forces:

They can go places others can't (try putting an if statement as a parameter in a method call).

The first part this.NewClientCityTextBox.Text == "" is the conditional part; if it evaluates to be true, then the first value after the ?, or in this case, null is used; otherwise, the value following the :, or in this case, this.NewClientCityTextBox.Text is used.

Conditional statements can be nested, and are evaluated in a right to left fashion.

First, (this.CompletedRadioButton.Checked ? true : false) evaluates to either be true or false.

Second, this.BothRadioButton.Checked ? (bool?)null : evaluates to be either null or the result from the previous condition.

Finally, !this.UseJobIsCheckBox.Checked ? (bool?)null : evaluates to be either null or the result from the previous condition.

If you look back at the picture above, you'll notice the Job Is check box is checked, and to its right are three radio buttons that are actually contained in their own panel to group them accordingly.

Now looking at the previous nested conditional statements in plain English, this is what we're saying:

If the Completed radio button is checked, then it's true; if not, we assume that the Incompleted radio button is checked, passing false to the next step.

Now if either radio button is checked, then we know that the Incompleted radio button is not checked, so we pass null to the next step; if not, then we know that the Incompleted radio button is definitely checked, so we pass false on to the next step.

Finally, if the Job Is checkbox is not checked, then that part of the search is disabled, and regardless of what the previous values are, true, false, or null, it's automatically null; if not, it evaluates to the value of the previous statement.

? In .NET 2.0, in addition to its previously mentioned duties as the conditional operator, it is also the new nullable operator. This makes anyone who interacts with databases a very happy person!

Certain data types in C#, VB.NET, C, Java, etc., and .NET framework types such as bool, for example, don't have a null state that can be assigned null.

bool IsThisNull = null;

won't compile. In a database, a boolean can be either true, false, or null. The problem in the past has been bridging that null gap between the source code nulls and the database nulls. The Nullable operator does this quite beautifully.

bool? IsThisNull = null;

This compiles and works fine. IsThisNull still works just like a regular bool, with the added bonus that it can be null for passing value to a database Stored Procedure or inline SQL statement.

The .NET 2.0 framework, in the same fashion that they provide us with overloaded operators like ++, --, =+, also provides an overloaded nullable operator ??.

Step 3 - Call the generated Fill() method using the ? conditional operator as well as the ? Nullable operator to filter and pass the correct value to the parameter and to cast the correct types, respectively.

Author's Corner

I probably won't be putting a code sample up to download for the simple fact that this article is not meant to give you a working solution but rather a good step by step plan to creating your own working solution. Besides, all that you need to know is in the article in the <pre></pre> blocks.

In the future, I'll throw together a demo program for you to play around with; at the moment, it's used in a rather massive software suite being developed, and no one here is going to want to buy the whole farm just to find out how the tractor runs.

I always welcome comments, questions, and even complaints, so don't be shy.

History

March 24, 2006 - First edition.

March 29, 2006 - Made some minor changes; someone pointed out that I was calling it a DataAdapter and not a TableAdapter; I'm so used to using the DataAdapters in 1.1 that I completely missed it.

Share

About the Author

Not much to say really If you saw me now you'd probably think I was a vagrant or a heavy drug user

Truth is I'm just a geek with an extreemly high pain tollerance. When I'm not writing code I'm either getting something pierced, or getting another tattoo.

In general i've been programing since 6th or 7th grade (not sure when we started apple basic :-P) I've been hooked ever since. Jumped right into C which i pretty much taught myself. Picked up enough C++ when it came out to write what i needed then i learned pascal (if your wondering why i learned pascal after C well my school was a little behind the 8-ball, so I ended up with a class to catch up on some sleep) I picked up C# from the start when all you could get was the .NET sdk and the csc command line compiler. Inbetween time my uncle sent me an SQL book so I learned that (we're talking industry standard SQL not MS or Oracle's extra little extensions). For the most part ever since MS released VS.NET 2002 I've been using it and its subsequent upgrades to write an even mix of windows forms and database applications.

Currently working at Lockheed Martin's System Integration site in Owego, NY I develop custom deployment solutions for site wide software installations, as well as site level support tools, utilities and custom process automation software for both interdepartmental use as well as special requests from other bussiness areas.

WHERE
(Inquiry.ID like ISNull(@ID,Inquiry.ID))
AND(InquiryCode like ISNull(@InquiryCode,InquiryCode))
AND (ReceivedBy like ISNull(@ReceivedBy,ReceivedBy))
-- AND (ProjectName like ISNull(@ProjectName,ProjectName,@ProjectName)+'%') --This field have null values
AND (Country like ISNull (@Country,Country))
AND (Contractor like ISNull (@Contractor,Contractor))
AND (Consultant like ISNull(@Consultant,Consultant))
AND (Client like ISNull(@Client,Client))
AND (ProjectStatus.ProjectStatus like ISNull(@ProjectStatus,ProjectStatus.ProjectStatus))
AND (Users_1.Name like ISNull(@ResponsiblePerson,Users_1.Name))
AND (Contact.FName + N' ' + Contact.LName like ISNull(@ContactName,Contact.FName + N' ' + Contact.LName))
AND (StatusName like ISNull(@StatusName,StatusName))
AND (Bedget like ISNull(@Bedget,Bedget))
AND (OfferValue like ISNull(@OfferValue,OfferValue))
AND (Apprivation like ISNull(@Apprivation,Apprivation))
-- AND (Remarks <> Null OR Remarks like ISNull(@Remarks,Remarks)) --This field have null values
AND (ReceivedDate >= ISNull(@ReceivedDateFrom,ReceivedDate))
AND (ReceivedDate <= ISNull(@ReceivedDateTo,ReceivedDate))

I know this thread is a very old one. But recently I have started working on a project and this kind of stored procedure is being used in it.

I have some problem with NULL values.
My tables have some NULL values. this procedure works just fine with NON NULL values.
But when the ISNULL() compares two value, such as the return_type has NULL value in it, the whole row is ignored.
I want to display the row even if it has few NULL values.
I can't use coalesce() since i might need to filter with the columns which sometimes have a null value.

Quite an old article, but this has changed my life a bit. I've been doing it wrong all these years. I'd first insert values into a temp table and then discard them based on where a parameter value is null or not. Yes, that was inefficient. I'm thankful for this article.

I think since that first IF is verifying a bunch of items being null, couldn't the subset query have a simplified WHERE clause (as in almost none)? IF all parameters are null, Then WHERE is not needed, right?

I have set up a stored procedure as you described, but when I execute, it doesn't return all the records that match my parameter(s). There are two fields in the database (including one that I am matching), that are sometimes Null.
The records returned are those that match my (1) parameter and where the other field is not Null.

Using ISNULL you can't handle more than two options. It's an A or B and nothing else method, if you need to handle more than two options you need to either nest your ISNULL methods which can get ugly or use COALESCE in place of ISNULL. COALESCE allows you to have 2 or more options and it works in the same way as ISNULL in that if the first option is null it tries the seccond option but unlike ISNULL it continues through the list of options you give it. so if the second option is null it goes to the third and so on and so forth. It adds one extra thing as well if it has evaluated every option passed into it and they are all null then it returns a null value.

I did not use COALESCE for the simple fact that all fields in my database are strictly non-null. However for what you are doing you might need it. If I'm completely missing what you need feel free to e-mail me back with the table structure (what's sometimes null. etc) and the stored procedure (when i first wrote mine it didn't work like i expected i had to go back and do it in chunks to make sure each part worked so that also might be the problem) and i'll take a look at it and try to help you figure it out.

I am also not getting the output.In my table I have records where colums may have null values. I followed the steps but in the where cluase if I give OR instead of AND its working and if I try to print the variable values its blank rather it should be '%'.
Any idea whats happening?
example
declare @FormNum varchar(50) = null,
@RepFirstName Varchar(50) = null,
@RepLastName Varchar(50) = null

Just FYI - and not directly relevant to your article - SQL queries like your stored proc are not very efficient (I speak from experience). This is one area where one could be justified in writing dynamic SQL, either in the DAL or the stored proc itself (or if you insist that this is business logic, in the BLL), as the performance benefit would be substantial.

The Good: you figured out and wrote an article on how to use the TableAdapter class.

The Bad: your implementation is inefficient, and places business logic in the data layer!

The ugly: The IDE can do all of this for you! -and- For those of us usinng transactional searches... your code doesn't work because table adapters can't use transactions below the DTC level.

Your article makes fine sense for a beginner, but it lacks depth. Also, your title says "data adapter" when you are using "table adapters." They are two different classes!!! Correct your title, use the Data Adapter, as it can generate your stored procs for you! Even when searching by a ton of parameters, you should let the IDE generate all that code for you! Overload your stored procs with parameter collections instead of using "if" statements in your code. Also, get to know the DataAdapter, and using the TableAdapter will come much easier.

I haven't had time to contribute an article on ADO2, but first chance I get, I'll do so.

First off thanks for pointing things out I'm never going to claim to know the most efficient way of doing things and feed back like this is the only way i'm going to learn more.

first off you're completely right about the TableAdapter thing i'm used to only having the old DataAdapter and i didn't even think about it when i was typing this up. Now you said i wrote an article about how to use the TableAdapter class and that's not what the point was, yes it does show how use the TableAdapter to fill a dataset but the extra point i was trying to get across is using the Nullable operator along with the TableAdapter's fill method to pass in null values for types that normally don't have null states.

second unless i completely miss understood the definition of bussiness logic and where it should go i don't see how i have bussiness loging in the data layer, all the stored procedure does is provide a means to filter the results from the database using some none or all of the filtering criteria. however the program uses the stored procedure and what it does with the data to me seems pretty seperated.
CO
third i'm pretty sure i said in the article that the TableAdapters were created automatically by the IDE, and yes it does do all that for you but you still have to use the fill method yourself especially if the parameter values can come from multiple objects and controls in the code. but if you know how to get the IDE to automatically fill in that big fill method with all the correct values please let me know.

My experience with sql started with learning just plain old sql so i've never really done anything with transactions at all not that i don't want to i've just never had the time or taken the time to learn so i'm not even sure why you say it won't work but i would very much like to know why.

This article was the culmination of me reaching a point where i had to have a way to quickly filter items from the database in any way i wanted so i googled the hell out of optional parameters in sql stored procedures and the combination of all the articles i read through both on here and other sites led me to learning how to put the stored procedure together now i know i could have used COALESCE instead of ISNULL but there was never going to be more than two states of the paremeters so for what i needed the ISNULL worked just fine. If you know how to do optional parameters in an sql stored procedure a more efficient way please point me in the right direction cause everything i found pointed me to what i described in this article.

Also if you know another way for the TableAdapter to handle null values than how i did it let me know as well

If anything sounds like i'm being sarcastic i appologize i do not mean it to be. but i do want to learn better ways to do things.