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:
-- Create date:
-- 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
Prasad BhagatPosted Aug 20, 2014, 5:46 AM
thanks for your sujjetions
i got the solution from above query and
we need to pass additional condition from frontend
thanks guys
Prasad BhagatPosted Aug 20, 2014, 5:21 AM
but the query was not working sir
in above condition i have role column then i mention above two conditions all the employees are displaying in front end
but i need to disply only actived and particler role member
Alok SaxenaPosted Aug 20, 2014, 4:24 AM
First find which table having IsActive and its data type. I am expecting IsActive column is a bit
table is tbl_param_employee_login. Try with underline code in green color.
WHERE
tbl_param_employee_login.IsActive = '1' AND
(@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 +'%')
Regards
Alok Saxena
Ankur JainPosted Aug 20, 2014, 2:43 AM
have you try like this..
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 +'%') AND tbl_param_employee_login.IsActive='1'
END
i am assuming that tbl_param_employee_login.IsActive is a datatype bit..
try this and please let me know if its worked for you or not...thanx..