March 1, 2010 at 8:47 am
We've got a jacked up Employee Table in our SQL 2000 database - An old VB5 application uses a Microsoft Access database. Once a day, a job on the SQL Server is run to pull data from the Access database over to the server. It deletes the existing SQL Employee table and replaces it with the data from Access.
There are no keys in the table, and all of the fields are straight nVarChar(255) fields. The old VB5 application failed to trim leading or trailing spaces from the text fields and does no error checking.
To compound the problem, we have machine records that store the Full Name of the Employee who was operating certain equipment. Matching Full Names to the correct Employee Badge Number has proved difficult.
I've written a stored procedure called "sp_GetBadge(@FullName nVarChar(255))" to handle this problem.
It starts by trimming the spaces from the full name, then looks for the first blank space. If it finds one, it creates a @FirstName variable out of the first part of the string, then starts at the end of the string and again looks for the first space from the rear (for last names like "De La Rosa") to fill the @LastName.
So, given a full name like " Maria Jo De La Rosa " (I'm hoping that is the worst case scenario), I am able to create @FirstName of "Maria" and @LastName of "Rosa".
Now, I need to select where "(FIRSTNAME Like 'Maria%') AND (LASTNAME Like '%Rosa')"; however, every time I try to append the '%' wild card to my search variables and use the 'Like' filter, I get 0 records returned. If I select where "(FIRSTNAME=@FirstName) AND (LASTNAME=@LastName)" (i.e. No Wild Cards - exact match only), all entries with single word first names and last names will display.
Could someone a little more knowledgeable on writing stored procedures take a look at my code to let me know what I may have done wrong?
PROCEDURE [dbo].[sp_GetBadge](@FullName nVarChar(255)) AS
BEGIN
declare @FirstName nVarChar(255), @LastName nVarChar(255)
declare @space nVarChar(1), @lastChar nVarChar(1)
declare @index int
set @space=' ' -- constant - do not change
set @FirstName='' -- initialize just in case there are no spaces!
set @LastName=LTrim(RTrim(@FullName))
set @index=CharIndex(@space, @LastName)
if (0 < @index) Begin
set @FirstName=SubString(@LastName, 1, @index)
set @index=Len(@LastName)-1
set @lastChar=SubString(@LastName, @index, 1) -- starts at the end...
While (@lastChar!=@space) Begin
set @index=@index-1 -- ...and walks back one char at a time
set @lastChar=SubString(@LastName, @index, 1)
End
set @LastName=SubString(@LastName, @index+1, Len(@LastName))
End
If (@LastName!='') Begin
SET @FirstName=@FirstName + '%'
SET @LastName='%' + @LastName
SELECT TOP 1 NUM
FROM EmployeeTable
WHERE (FIRSTNAME Like @FirstName) AND (LASTNAME Like @LastName)
ORDER BY [COUNT] DESC
End Else Begin
SELECT @LastName As 'NUM'
End
END
March 1, 2010 at 10:03 am
March 1, 2010 at 10:52 am
Happy to help. I didn't "see it" necessarily though, I tested it with the following:
declare @fullname Nvarchar(255)
set @fullname = ' abc def '
declare @FirstName nVarChar(255), @LastName nVarChar(255)
declare @space nVarChar(1), @lastChar nVarChar(1)
declare @index int
set @space=' ' -- constant - do not change
set @FirstName='' -- initialize just in case there are no spaces!
set @LastName=LTrim(RTrim(@FullName))
set @index=CharIndex(@space, @LastName)
if (0 < @index) Begin
set @FirstName=SubString(@LastName, 1, @index - 1)
set @index=Len(@LastName)-1
set @lastChar=SubString(@LastName, @index, 1) -- starts at the end...
While (@lastChar!=@space) Begin
set @index=@index-1 -- ...and walks back one char at a time
set @lastChar=SubString(@LastName, @index, 1)
End
set @LastName=SubString(@LastName, @index+1, Len(@LastName))
End
select '&' + @fullname + '&' ,'&' + @firstname+ '&','&' + @lastname+ '&'
Regards,
Toby
March 1, 2010 at 11:51 am
Before I add a new topic, perhaps you could answer my question on "Part 2" quickly:
How do I call this stored procedure (sp_GetBadge) from within another stored procedure - or use it in a SELECT statement?
For Example:SELECT FirstName, LastName, dbo.sp_GetBadge(FirstName+' '+LastName) As 'Badge'
FROM EmployeeTableI have tried using the stored procedure 'sp_GetBadge' with with and without the parenthesis, but my SQL error is either "Invalid object name 'dbo.sp_GetBadge'." (with parenthesis) or "Incorrect syntax near '+'." (without parenthesis).
March 1, 2010 at 2:49 pm
Are you using the Execute keyword before the procedure as in:
Execute dbo.sp_GetBadge ?
March 1, 2010 at 2:53 pm
Oh, I think I see the problem. If you need to use it in a select statement then you probably want to create a table value function for it instead of a stored procedure. Look up Create Function in BOL. Another way to use the select statement from the procedure is to create a table variable or temp table and stuff if with the results of the procedure, but I would recommend against that.
Regards,
Toby
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply