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.