名称详细信息不清楚 [英] Name details getting not clear

问题描述

ALTER PROCEDURE [dbo].[HRLeaveRequestSearch]
(	
	
	 @dateFrom		datetime = null
	,@dateTo		datetime = null
	,@employeename  varchar(50) = null
	,@leavetype		int  = null
	,@leaveStatus	int = null
)AS
BEGIN

	SELECT [LeaveRequestId]
		  ,HRT_LeaveRequest.[Code]
		  ,[HRM_Employee].[FirstName] + ' ' +ISNULL([HRM_Employee].[MiddleName],'') + ' '+ ISNULL([HRM_Employee].[LastName],'') [FirstName]
		  ,[RequestedDate]
		  ,[Description]
		  ,[DateFrom]
		  ,[DateTo]
		  ,[NoOfDays]
		  ,[LeaveTypeXId]
		  ,HRM_LeaveType.Name 	LeaveType 
		  ,[EmployeeXId]
		  ,[LeaveStatusId]
		  ,[ContactNumberOnLeave]
		  ,HRT_LeaveStatus.Name	LeaveStatus	--Changed on 11-07-2011 added the alias name 'LeaveStatus'
	  FROM [HRT_LeaveRequest]
	    JOIN HRM_LeaveType 
			ON HRM_LeaveType.LeaveTypeId = HRT_LeaveRequest.LeaveTypeXId 
	  	JOIN HRT_LeaveStatus 
			ON HRT_LeaveStatus.LeaveStatusId = HRT_LeaveRequest.LeaveStatusXId 
		JOIN HRM_Employee 
			ON HRT_LeaveRequest.EmployeeXId = HRM_Employee.EmployeeId
	WHERE	(@employeename is null or ([HRM_Employee].[FirstName] + ' ' +ISNULL([HRM_Employee].[MiddleName],'') + ' '+ ISNULL([HRM_Employee].[LastName],'')) LIKE '%' + @employeename  + '%')
		
		AND (@dateFrom is null or HRT_LeaveRequest.DateFrom between CONVERT(date,@dateFrom ,103) and CONVERT(date,@dateTo ,103))
	    AND (@dateTo is null or HRT_LeaveRequest.DateTo  between  CONVERT(date,@dateFrom ,103) and CONVERT(date,@dateTo ,103))
	    --or (CONVERT(date,@dateFrom, 103) <= CONVERT(date,DateFrom,103) AND  CONVERT(date,@dateTo , 103) >= CONVERT(date,DateTo,103) and (HRM_Employee.FirstName LIKE '%' + @employeename  + '%' or @employeename is null))
	    --and HRT_LeaveRequest.DateFrom between CONVERT(date,@dateTo , 103) and CONVERT(date,@dateFrom, 103)  and HRT_LeaveRequest.DateTo between CONVERT(date,@dateFrom, 103) and CONVERT(date,@dateTo, 103)
	    AND (@leavetype is null or HRM_LeaveType.LeaveTypeId = @leavetype)
	    AND ( @leaveStatus is null or HRT_LeaveStatus.LeaveStatusId = @leaveStatus)
	  ORDER BY HRT_LeaveRequest.DateFrom  
end

我已经编写了上面的存储过程.当我输入@employeename作为测试时，我得到输出.
但是，当我输入@employeename作为测试1或在@employee名称中提供空格时，我无法获取详细信息.请帮我解决这个问题.

I have written the above stored procedure. When I enter @employeename as test I''m getting output.
But when I enter the @employeename as test 1 or giving a space in @employee name I cant get the details. Please help me to solve this.

推荐答案

WHERE	
(
  @employeename is null 
  or (
      [HRM_Employee].[FirstName] + ' ' +ISNULL([HRM_Employee].[MiddleName],'') + ' '+      
      ISNULL([HRM_Employee].[LastName],'')) LIKE '''%' + LTRIM(RTRIM(@employeename))  + '''%'
)

这篇关于名称详细信息不清楚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！