Can Any budy update thie sp for search , i think i have put search condition which is not rigth . If you have expertise in sql then update it , It's very urgent i have to upload it toady ..
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!='' or @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!='' or @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!='' or @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!='' or @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!='' or @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!='' or @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) =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
