Parameterizing Allow Nulls Columns

Here's a question I though would be common but can't find an answer to!

My select statement, which pulls from SQL Server tables, has a column which allows nulls. When I try to add a parameter to this column, it no longer returns rows that have null in that column when I test it with the default value of %. I want it to return all the records.

SELECT Jobs.JobID, Jobs.JobName, Engineers.Engineer FROM Jobs LEFT OUTER JOIN Engineers ON Jobs.AccountManager = Engineers.ID WHERE (Engineers.Engineer = @.Engineer)

Parameter is;
ConvertEmptyStringToNull = True
DefaultValue = %
Direction = Input
Name = Engineer
QueryStringField = Engineer
Size = 0
Type = Empty

When I run this I get no records! Isn't % suppose to return anything including nulls?

Isn't % suppose to return anything including nulls?

No. % is only valid for the LIKE operator, and even then it will not return nulls.

SELECT Jobs.JobID, Jobs.JobName, Engineers.Engineer FROM Jobs LEFT OUTER JOIN Engineers ON Jobs.AccountManager = Engineers.ID WHERE (Engineers.Engineer = @.Engineer) OR (@.Engineer='%')

|||

This works and I thank you much but the thickness of my skull is prohibiting me from absorbing why it work.

WHERE (Engineers.Engineer = @.Engineer)
if a parameter is not provided - the default of % is used and this returns every value accept for nulls. How does it treat zero length strings?
if a parameter is provided - it returns the rows that match the parameter

OR (@.Engineer='%')
if a parameter is not provided - ?
if a parameter is provided - ?

I don't understand how this connects to Engineers.Engineer.

|||

WHERE (Engineers.Engineer = @.Engineer)
if a parameter is not provided, the default of % is used and only rows in which engineer contains EXACTLY % will be returned. I assume you have no engineers that contain exactly %, so no rows will be returned.

If a parameter is provided, it returns the rows that match the parameter.

OR (@.Engineer='%')
if a parameter is not provided, the default of % is used and % always is equal to %. So it will match on every row.

if a parameter is provided (Assuming of course the parameter isn't %), then it will never match.

Recap

WHERE (Engineers.Engineer = @.Engineer) OR (@.Engineer='%')

No parameter: WHERE (FALSE) OR (TRUE)

simplified: WHERE TRUE

Parameter: WHERE (Possibly TRUE -- if they match) OR (FALSE)

simplified: WHERE (Possibly TRUE -- if they match)

Make any sense now?

|||

Perfect Sense. Thank you! So I'm guessing the use of a wildcard really doesn't work in ASP.NET or Sql Server? In Dreamweaver I can provide a string parameter of '%' and it will return all rows including nulls. The WHERE clause is Field LIKE '%'.

|||

Yes, the like operator accepts wildcards, but the equals operator does not.

WHERE Field LIKE '%' will find all fields that have 0 or more characters (NULL is not 0 or more characters, it's NULL).

WHERE Field='%' will find all fields that contain one character, and that one character must be the percent symbol.

|||LIKE '%' returns NULL fields in SQL Server and in my Dreamweaver application but not in my asp.net application. I just posted a different thread asking why it doesn't work in asp.net.