Sql Substring and Search String I have a table in my database for storing supplier contacts and it is laid out like so:

Table: supplier_contacts
    contact_id  INT IDENTITY(1,1)
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NULL,
    supplier_id INT


I have an external VB.Net project that will be using the database, and will be passing in contact names as string with the first and last names put together.  For example:

Database:
    first_name = John
    last_name = DOe

VB.Net:
    name = "John Doe"


My thought in order to properly get the contact_id value is to just put together a stored procedure that can be called that does a search through the table using substrings and such.  My issue is I can't get it to work.

This is basically what I have some up with for a sort of "proof of concept" query to see if I can get this to work.

DECLARE @Contact   VARCHAR(150) --Value for the full contact name
DECLARE @ContactID INT

SELECT @ContactID = contact_id
  FROM supplier_contacts
 WHERE first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1) AND
       last_name = SUBSTRING(@Contact, CHARINDEX(' ', @Contact) + 1, LEN(@Contact))


My problem comes when the last_name column is blank, since it isn't a mandatory field.  So I am not sure how to set up the last_name condition to essentially be an optional check.
How about something like this..

DECLARE @Contact   VARCHAR(150) --Value for the full contact name
DECLARE @ContactID INT

SELECT @ContactID = contact_id
  FROM supplier_contacts
 WHERE (first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1) AND
       last_name = SUBSTRING(@Contact, CHARINDEX(' ', @Contact) + 1, LEN(@Contact)))
or
first_name = SUBSTRING(@Contact, 1, CHARINDEX(' ', @Contact) - 1)