Conditional SQL Formatting

Question

I was wondering if it was possible and surely it must be. I'd like to basically create a dymanic SQL statement conditionally based on user input from (well for this example, we'll use 3 textbox inputs):

var firstname = document.getElementById('firstname').value
var middle = document.getElementById('middle').value
var lastname = document.getElementById('lastname').value
var SQL

(if all 3 fields are filled out...)

SQL = "SELECT * FROM TABLE WHERE [Firstname] = '" + firstname + "' AND [Middle] = '" + middle + "' AND [Lastname] = '" + lastname + "'"

(if 2 of the 3 fields are filled out...)

SQL = "SELECT * FROM TABLE WHERE [Firstname] = '" + firstname + "' AND [Lastname] = '" + lastname + "'"

Is there a better way to be doing this? Something shorter? or will I have to evaluate text in each field and then define the SQL?

Just was wondering, but it never hurts to ask.

Jay

Answer 1

Something like that should work...

SQL = "SELECT * FROM TABLE WHERE 

  1 =
  (
  case
     when LEN('" + firstname + "') > 0  and LEN('" + lastname + "') > 0 and LEN('" + middle + "') > 0 then ([Firstname] = '" + firstname + "' AND [Lastname] = '" + lastname + "' AND [Middle] = '" + middle + "')
     when LEN('" + firstname + "') = 0  and LEN('" + lastname + "') > 0 and LEN('" + middle + "') > 0 then ([Lastname] = '" + lastname + "' AND [Middle] = '" + middle + "')
     ...
  end
  )

Or better:

SQL = "SELECT * FROM TABLE WHERE 
(
  (LEN('" + firstname + "') > 0 AND [FirstName] = '" + firstname + "') 
 OR 
   LEN('" + firstname + "') = 0
) 
AND (same for secondName) 
AND (same for middle)"

Answer 2

If you able to pass the values in as parameters into the query then

SQL = " SELECT * 
    FROM TABLE 
    WHERE (@firstName is null OR [FirstName] = @firstName) 
      AND (@middle is null OR [Middle] = @middle)
          AND (@lastName is null OR [Lastname] = @lastName"

This will also stop the query cache from being spammed as you'll only be storing 1 query plan in the cache.

Answer 3

Please don't ever do this. Parameterized queries are your friend.