bfuchs
asked on
Trying to add condition to an table in-line function
Hi Experts,
I'm trying to modify my current function to have an option to filter by a combined field in case user types a space.
current
to something like following
My question is how to accomplish that to perform in the most efficient manner?
Thanks,
Ben
I'm trying to modify my current function to have an option to filter by a combined field in case user types a space.
current
ALTER FUNCTION [dbo].[EmployeestblSelectByFirstPH] (
@sWhere varchar(2000)
)
RETURNS TABLE
AS
RETURN
SELECT top 100
ID, FirstName + ' ' + LastName as Name, Title
from dbo.Employeestbl
where FirstName like @sWhere + '%'
And (NOT EXISTS
(SELECT EmployeeID
FROM dbo.TovInfo
WHERE (Day >= GETDATE() - 60) AND (EmployeeID = dbo.Employeestbl.ID))) AND (dbo.Employeestbl.Title = 'rn' OR
dbo.Employeestbl.Title = 'lpn') AND (NOT (dbo.Employeestbl.EmployeeStatus IN ('Inactive', 'Disapproved')) OR
dbo.Employeestbl.EmployeeStatus IS NULL)
order by FirstName, LastName
to something like following
USE [PlacementNP]
GO
ALTER FUNCTION [dbo].[EmployeestblSelectByFirstPH] (
@sWhere varchar(2000)
)
RETURNS TABLE
AS
RETURN
SELECT top 100
ID, FirstName + ' ' + LastName as Name, Title
from dbo.Employeestbl
where
case when CHARINDEX(@sWhere,' ')=0 then
FirstName like @sWhere + '%'
else
FirstName + ' ' + LastName like @sWhere + '%'
end
And (NOT EXISTS
(SELECT EmployeeID
FROM dbo.TovInfo
WHERE (Day >= GETDATE() - 60) AND (EmployeeID = dbo.Employeestbl.ID))) AND (dbo.Employeestbl.Title = 'rn' OR
dbo.Employeestbl.Title = 'lpn') AND (NOT (dbo.Employeestbl.EmployeeStatus IN ('Inactive', 'Disapproved')) OR
dbo.Employeestbl.EmployeeStatus IS NULL)
order by FirstName, LastName
My question is how to accomplish that to perform in the most efficient manner?
Thanks,
Ben
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Scott,
I'm not sure that will work that way, for instance if you search for just 'Jo', the first part of your query will search for FirstName like 'jo' instead of firstName like 'jo%'
I'm not sure that will work that way, for instance if you search for just 'Jo', the first part of your query will search for FirstName like 'jo' instead of firstName like 'jo%'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
With % sign wont it search for firstName like 'Jo%' and lastname like 'd%' if my search string is 'Jo d' ?
Yes. I don't see the reason for restricting the search on the first name just because the last name is present. If that is a fixed condition, then the code would need changed.
However, I'd be far more concerned about the code's assumption that a first name can't/won't contain a space, because I don't think that's always true. "John Patrick" or "Mary Jo", for example, are sometimes treated as (just) a first name, and that could easily match some last names as well.
However, I'd be far more concerned about the code's assumption that a first name can't/won't contain a space, because I don't think that's always true. "John Patrick" or "Mary Jo", for example, are sometimes treated as (just) a first name, and that could easily match some last names as well.
ASKER
@Aneesh, @Scott,
So what will be the difference between both of the suggested versions?
Thanks,
Ben
So what will be the difference between both of the suggested versions?
Thanks,
Ben
ASKER
Thank you.
ASKER
Will this also be the best option regarding performance?
Any other suggestion/s on how to improve performance with this query?
Thanks,
Ben