How to Write a Query That Uses Variables
Posted on 2004-11-05
I have a problem that I'm trying to solve using SQL, and though I'm pretty sure it can be done, my query writing skills are pretty remedial, which makes it tough for me. And I've been reading code samples and documentation all day, to no avail.
Here is the situation. I need to extract employee's company IDs from a field in one table, where there is a matching name from another table. Sounds easy, but there is not a consistent format for the names in the table containing the IDs, and also in that table the ID has been appended to the name field following a hyphen.
So, what I have to deal with is this: (example field value from each table)
- Need to get IDs for all users in this table
- tblSupported.NAME_FULL -- sample data = RIGBY, LOIS
- This is the table I need to get them from
- tblUs1Test.UserName -- sample date = Lois.Rigby - G221077
My game plan was to strip out the names from the first table mentioned using substring and charindex functions, and store the contents in variables, and then use charindex to see if the contents of the variables existed in the second table. Once I get the select statement working, I'll either append the ID to the first table, or make it a select into statement and create a new table with the names and IDs. The code I came up with is listed below, but the Query Analyzer definitely does not like my syntax.
If anyone has any ideas, I would appreciate them very much.
declare @fname varchar(30), @lname varchar (30)
select @lname = substring (tblSupported.NAME_FULL, 1, charindex (',', tblSupported.NAME_FULL)
select @fname = substring (tblSupported.NAME_FULL, charindex (',', tblSupported.NAME_FULL) + 1,
len (tblSupported.NAME_FULL) - charindex (',', tblSupported.NAME_FULL) + 1
select /*userID*/substring (tblUs1Test.UserName, charindex ('-', tblUs1Test.UserName) + 1,
len(tblUs1Test.UserName)- charindex ('-', tblUs1Test.UserName) + 1) as UserID,
/*UserName*/tblSupported.NAME_FULL as UserName
from tblUs1Test, tblSupported
where ((charindex (upper (@fname), upper (tblUs1Test.UserName)) > 0 ) and
(charindex (upper (@lname), upper (tblUs1Test.UserName)) > 0 ))