Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

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
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

Open in new window


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

Open in new window


My question is how to accomplish that to perform in the most efficient manner?

Thanks,
Ben
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

@Aneesh,

Will this also be the best option regarding performance?
Any other suggestion/s on how to improve performance with this query?

Thanks,
Ben
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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%'
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of bfuchs

ASKER

@Aneesh, @Scott,

So what will be the difference between both of the suggested versions?

Thanks,
Ben
Avatar of bfuchs

ASKER

Thank you.