Can anybody modify this , when i insert Emp. name it go to first search and it does not check other paramete could you plz modify this sp for exact search on based on parameter.
--select * from Training_TRNS
--USP_SearchEmployee '','2008-04-18 00:00:00.000','','','','',''
alter Procedure USP_SearchEmployee
@EmpName varchar(100)=null,
@DateFrom varchar(100)=null,
@DateTo varchar(100)=null,
@CourseName varchar(100)=null,
@JobFunction varchar(100)=null,
@Region varchar(100)=null,
@Status varchar(100)=null
AS
BEGIN
if (@EmpName!='' and @EmpName is not null)
BEGIN
select EmpName,convert(varchar,DueDate,101) as DueDate,SpeCourse_ID as CourseName,
EmpJobFunction as JOBFunction,EmpRegion as Region,Status
from Training_TRNS where EmpName like '%'+@EmpName+'%'
END
ELSE IF (@CourseName!='' and @CourseName is not null)
BEGIN
select EmpName,convert(varchar,DueDate,101) as DueDate,SpeCourse_ID as CourseName,
EmpJobFunction as JOBFunction,EmpRegion as Region,Status
from Training_TRNS where SpeCourse_ID like '%'+@CourseName+'%'
END
ELSE IF (@JobFunction!='' and @JobFunction is not null)
BEGIN
select EmpName,convert(varchar,DueDate,101) as DueDate,SpeCourse_ID as CourseName,
EmpJobFunction as JOBFunction,EmpRegion as Region,Status
from Training_TRNS where EmpJobFunction like '%'+@JobFunction+'%'
END
ELSE IF (@Region!='' and @Region is not null)
BEGIN
select EmpName,convert(varchar,DueDate,101) as DueDate,SpeCourse_ID as CourseName,
EmpJobFunction as JOBFunction,EmpRegion as Region,Status
from Training_TRNS where EmpRegion like '%'+@Region+'%'
END
ELSE IF (@Status!='' and @Status is not null)
BEGIN
select EmpName,convert(varchar,DueDate,101) as DueDate,SpeCourse_ID as CourseName,
EmpJobFunction as JOBFunction,EmpRegion as Region,Status
from Training_TRNS where Status like '%'+@Status+'%'
END
ELSE IF (@DateFrom!='' and @DateFrom is not null)
BEGIN
select EmpName,convert(varchar,DueDate,101) as DueDate,SpeCourse_ID as CourseName,
EmpJobFunction as JOBFunction,EmpRegion as Region,Status
from Training_TRNS where convert(varchar,DueDate,101) like '%'+convert(varchar,@DateFrom,101)+'%'
END
Else
BEGIN
select EmpName,convert(varchar,DueDate,101) as DueDate,SpeCourse_ID as CourseName,
EmpJobFunction as JOBFunction,EmpRegion as Region,Status
from Training_TRNS
END
END
