none
parameterized query blank values RRS feed

  • Question

  • I'm trying to create a parameterized query that will accept a blank parameter value as a wildcard.  For instance:

    Dim empId as integeter = Nothing
    'Dim empId as integeter = 12345
    Dim cm As New MySqlCommand("SELECT * FROM employees WHERE id = @id", conn)
    Dim param As New MySqlParameter("@id", empId)
    cm.Parameters.Add(param)

    If empId = Nothing then i'd like the parameter to operate as if it was a wildcard and return all employees. 

    How does a NOTHING value get treated when passed as a parameter value?

    I suspect it's all in how I write the select query that checks for a blank parameter value and substitutes it for wildcard.  ??

    Thanks.

    Saturday, May 8, 2010 6:02 PM

Answers

  • I believe I found what I was looking for.  I thought I had already tried this method but I had my syntax wrong at the time.  So far this works for MySql, I don't know about other db's.  In the sql query I needed to check the parameter for IS NULL in my where clause.  For instance:

    SELECT * FROM table WHERE (@EmployeeId IS NULL OR employeeId = @EmployeeId)

    I can now use this query to return a list of employees or a single employee depending on if I pass a value for @Employeeid or not.  I have to do some further testing, but so far this is working.

    Now all I need to do is come up with a way to split a parameter into an array? of some kind so that i can check if a value is IN a set of values.

    SELECT * FROM table WHERE (@EmployeeId IS NULL or employeeId IN (split_string(@EmployeeId)))

    Right now I'm doing it in code with something like the below, and it's probably the only way to do it.

    query = query.replace(@EmployeeId,"'1234','1235','9382'")

    • Marked as answer by c_manboy Tuesday, May 11, 2010 5:14 PM
    Tuesday, May 11, 2010 5:14 PM

All replies

  • Sounds like this would be better done by checking the Nothingness/Null of empId before even hitting the server.
    This will save you the roundtrip etc.

    Ie.

    If (empid == null)
      Select * from;
    else
      Select * from where empid = @empid;

    Or put everything in a Stored Procedure.


    This posting is provided "AS IS" with no warranties.
    Monday, May 10, 2010 6:57 AM
  • Thanks for the reply.

    That is essentially what I've done for now.  I was just hoping that the command objects had some way of deailing with nothing values and could insert a wildcard instead, or that there was some way of writing the sql that could check for an empty value with an if(value,true,false) statement.

    Monday, May 10, 2010 1:13 PM
  • I believe I found what I was looking for.  I thought I had already tried this method but I had my syntax wrong at the time.  So far this works for MySql, I don't know about other db's.  In the sql query I needed to check the parameter for IS NULL in my where clause.  For instance:

    SELECT * FROM table WHERE (@EmployeeId IS NULL OR employeeId = @EmployeeId)

    I can now use this query to return a list of employees or a single employee depending on if I pass a value for @Employeeid or not.  I have to do some further testing, but so far this is working.

    Now all I need to do is come up with a way to split a parameter into an array? of some kind so that i can check if a value is IN a set of values.

    SELECT * FROM table WHERE (@EmployeeId IS NULL or employeeId IN (split_string(@EmployeeId)))

    Right now I'm doing it in code with something like the below, and it's probably the only way to do it.

    query = query.replace(@EmployeeId,"'1234','1235','9382'")

    • Marked as answer by c_manboy Tuesday, May 11, 2010 5:14 PM
    Tuesday, May 11, 2010 5:14 PM