Hi Jim,I tried creating a function for this initially and just retried at your suggestion. However, I can't get it to store because of an error with "Incorrect syntax near the keyword 'select'." ...which is where I tell it to "SELECT TOP 1" - it just won't do that!Any ideas how to solve this?CREATE FUNCTION fn_GetBadge(@FullName nVarChar(255)) RETURNS nVarChar(255) ASBEGIN -- Declare the return variable here declare @BadgeNum nVarChar(255) -- Declare other variables needed declare @FirstName nVarChar(255), @LastName nVarChar(255) declare @index int, @space nVarChar(1), @lastChar nVarChar(1) -- Add the T-SQL statements to compute the return value here 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 if (@LastName!='') begin set @FirstName=@FirstName + '%' set @LastName='%' + @LastName set @BadgeNum=select top 1 NUM from EmployeeInfo where (FIRSTNAME Like @FirstName) AND (LASTNAME Like @LastName) order by [COUNT] desc end else begin set @BadgeNum=@LastName end return @BadgeNum -- Return the result of the functionENDGO
Regards,~Joe
Avoid Sears Home Improvement