Who would have thought?

I needed a stored procedure that could take several possible values but some would not be passed. I’m sure some of you found better ways to do this. I’ve seen some insane ways of using XML and creating dynamic SQL to do this, but I didn’t want any dynamic SQL.

So I had this as a list of parameters

@CustomerID char(5) = null,

@CustomerName varchar(100) = null,

@CustomerAddress1 varchar(100) = null

This is just a sample of parameters, could be much more or less.

Then I have my “IF” statements.

If @CustomerID is null

            Set @CustomerID = ‘%’

If @CustomerName is null

            Set @CustomerName = ‘%’

Else

            Set @CustomerName = @CustomerName + ‘%’

If @CutomerAddress1 is null

            Set @CustomerAddress1 = ‘%’

Else

            Set @CustomerAddress1 = @CustomerAddress1 + ‘%’

Ok, now let’s look at the select statement:

Select * from Customers

            Where

                        CustomerID LIKE @CustomerID AND

                        CustomerName LIKE @CustomerName AND

                        CustomerAddress1 LIKE @CustomerAddress1

Now if you have never done a SP like this, what this allows me to do is pass any one value or all 3 values and boom the query will return a filtered result set back. I pass back nulls for all 3 parameters then I will see all customers, if I pass back a value for any one parameter then it will filter on that one value, if I send in more then one parameter value then it will filter on how ever many I populated.

Column LIKE ‘%’ returns everything.

So why did I write this blog post and talk at the very top about Chars and  %% percent signs?

Well I noticed an interesting bug (if you want to call it that). If the parameter is a CHAR datatype and you set the parameter (because NULL was passed) to ‘%’ then the query would not return anything. Not a single record, what really puzzled me was the fact that if I manually put in CustomerID = ‘%’ then it worked just fine.

So I figured there is something with the setting of the parameter (which is defined exactly like how the column is defined (granted I don’t like Char ID’s)). Then it struck me, what if when I do “SET CharColumn = ‘%’” that it actually turned out to look like (’%       ‘) since I only populated the first char, then it will only pass back rows that match this. So I had to do some fast thinking. So then I populated the Char parameter like this:

Set CharParameter = ‘%%%%%’

Why does this work you might ask? Well it should look like (‘%%%%%‘) since I filled the chars of a length of 5 with percent signs. Hence nothing is in-between the % signs and it gives back all values. This isn’t a problem with other datatypes that are not fixed length, but it sure was a real headache to find.

I hope this helps someone else, since I couldn’t find anything on this.