Database

SELECT <br />
    docIndex, docLName, docFName, docMName<br />
FROM <br />
    tblStaffPhysicians<br />
WHERE<br />
    (docFName LIKE '%' + ? + '%') AND<br />
    ((docMName IS NULL) OR (docMName LIKE '%' + ? + '%')) AND<br />
    (docLName LIKE '%' + ? + '%')

<code>
CREATE PROCEDURE ia.SP_StaffPhysicianSelectCommand
(
@Param2 varchar(35)
)
AS
SET NOCOUNT ON;

-- Declare local variables
declare @firstName nvarchar(35)
declare @middleName nvarchar(35)
declare @lastName nvarchar(35)
declare @count int
declare @pos int
declare @oldPos int
declare @Param2 nvarchar(35)

-- Initialize local variables
set @oldPos = 0
set @count = 0
set @Param2 = 'a c'

-- Parse the name parameter
set @pos = charindex(' ', @Param2)
while (@pos > 0)
begin
	set @count = @count + 1
	if (@count <= 2)
		begin
			if (@count = 1)
				-- First name
				set @firstName = substring(@Param2, @oldPos, @pos - @oldPos)
			else
				-- Middle name
				set @middleName = substring(@Param2, @oldPos, @pos - @oldPos)
		end
	else if (@count = 3)
		begin
	 		-- Last name
			set @lastName = right(@Param2, len(@Param2) - @oldPos + 1)
			break
		end

	-- Set a position marker and iterate
	set @oldPos = @pos + 1
	set @pos = charindex(' ', @Param2, @pos + 1)

	-- If there are no further spaces, but the end of the string
	-- has not been reached, place the remainder into the next 
	-- open slot
	if (@pos = 0 and @count < 3)
		begin
			if (@count = 1)
				set @middleName = right(@Param2, len(@Param2) - @oldPos + 1)
			else 
				set @lastName = right(@Param2, len(@Param2) - @oldPos + 1)
			set @count = @count + 1
		end
end

-- Evaluate the count: if only 2 words were found, 
-- assume they were a first and last name, adjut the 
-- values accordingly
if (@count = 2)
	begin
		set @lastName = @middleName
		set @middleName = null
	end

-- Finally, select the matching names
SELECT 
	docIndex, docLName, docFName, docMName 
FROM 
	dbo.tblStaffPhysicians
WHERE 
	docFName like '%' + @firstName + '%' AND
	((docMName is null and @middleName is null) OR (docMName like '%' + isnull(@middleName, '') + '%')) AND 
	docLName like '%' + @lastName + '%'	
ORDER BY docLName, docFName
</code>