Dear all,
USE [Gateway_Two]
GO
/****** Object: StoredProcedure [dbo].[usp_get_employees_list] Script Date: 08/20/2014 11:49:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_get_employees_list]
@str nvarchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
dbo.tbl_param_employee_master.ID,
dbo.tbl_param_employee_master.FIRST_NAME,
dbo.tbl_param_employee_master.MIDDLE_NAME,
dbo.tbl_param_employee_master.LAST_NAME,
(CASE dbo.tbl_param_employee_master.GENDER WHEN 'M' THEN 'Male' ELSE 'FeMale' end) as gender,
dbo.tbl_param_employee_login.username,
tbl_param_employee_login.IsActive,
dbo.tbl_param_employee_master.JOB_LOCATION,
dbo.tbl_param_role_master.Role,
(select top 1 department_name from dbo.tbl_param_department_master where id = CONVERT(int,dbo.tbl_param_employee_master.DEPARTMENT)) as dept,
-- dbo.tbl_param_department_master.department_name AS dept,
(select top 1 value from dbo.tbl_param_reference_master where id = EMPLOYEE_TYPE) as emptype,
dbo.tbl_param_employee_master.DATE_BIRTH as dob,
(CASE dbo.tbl_param_employee_login.id WHEN NULL THEN 0 else dbo.tbl_param_employee_login.id END) as login_id
FROM
dbo.tbl_param_employee_master LEFT OUTER JOIN
dbo.tbl_param_employee_login ON dbo.tbl_param_employee_master.ID = dbo.tbl_param_employee_login.employee_id LEFT OUTER JOIN
dbo.tbl_param_role_master ON dbo.tbl_param_employee_master.Employee_Role_Id = dbo.tbl_param_role_master.id LEFT OUTER JOIN
dbo.tbl_param_department_master ON dbo.tbl_param_employee_master.Employee_Department = dbo.tbl_param_department_master.id
WHERE
(@str ='0' OR dbo.tbl_param_employee_master.FIRST_NAME LIKE '%'+ @str +'%') OR
(@str ='0' OR dbo.tbl_param_employee_master.Last_NAME LIKE '%'+ @str +'%') OR
(@str ='0' OR dbo.tbl_param_department_master.department_name LIKE '%'+ @str +'%') OR
(@str ='0' OR dbo.tbl_param_employee_login.username LIKE '%'+ @str +'%') OR
(@str ='0' OR dbo.tbl_param_role_master.Role LIKE '%'+ @str +'%') OR
(@str ='0' OR (select top 1 value from dbo.tbl_param_reference_master where id = EMPLOYEE_TYPE) LIKE '%'+ @str +'%')
END
From above procedure i want retreve only employees when they are isactive true
please tell me the answer asap