SELECT FirstName, LastName, dbo.sp_GetBadge(FirstName+' '+LastName) As 'Badge'
FROM EmployeeTable

CREATE FUNCTION fn_GetBadge(@FullName nVarChar(255)) RETURNS nVarChar(255) AS
BEGIN
	-- 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 function
END
GO

set @BadgeNum = (select top 1 NUM
from EmployeeInfo
where (FIRSTNAME Like @FirstName) AND (LASTNAME Like @LastName)
order by [COUNT] desc)