ALTER PROCEDURE [dbo].[CM_DEPARTMENT_Search]
@p_DEP_ID	varchar(15)  = NULL,
@p_DEP_CODE	varchar(15)  = NULL,
@p_DEP_NAME	nvarchar(200)  = NULL,
@p_DAO_CODE	varchar(40)  = NULL,
@p_DAO_NAME	nvarchar(500)  = NULL,
@p_BRANCH_ID	varchar(15)  = NULL,
@p_GROUP_ID	varchar(15)  = NULL,
@p_TEL	varchar(20)  = NULL,
@p_NOTES	nvarchar(1000)  = NULL,
@p_RECORD_STATUS	varchar(1)  = NULL,
@p_MAKER_ID	varchar(12)  = NULL,
@p_CREATE_DT	VARCHAR(20) = NULL,
@p_AUTH_STATUS	varchar(50)  = NULL,
@p_CHECKER_ID	varchar(12)  = NULL,
@p_APPROVE_DT	VARCHAR(20) = NULL,
@p_TOP	INT = 10
AS
	--Validation is here
/*
	DECLARE @ERRORSYS NVARCHAR(15) = '' 
	IF ( [NOT] EXISTS ( SELECT * FROM [TABLE] WHERE  [CONDITION] ))
		 SET @ERRORSYS = ''
	IF @ERRORSYS <> '' 
	BEGIN
		SELECT ErrorCode Result, ''  CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS
		RETURN '0'
	END */
BEGIN TRANSACTION
SET @p_TOP =4000
declare @tmp table(BRANCH_ID varchar(15))
insert into @tmp  SELECT BRANCH_ID  FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID)
IF(@p_TOP = NULL OR @P_TOP = '' OR @P_TOP = 0)
SELECT A.*, B.BRANCH_CODE, B.BRANCH_NAME, C.AUTH_STATUS_NAME
FROM CM_DEPARTMENT A 
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS = C.AUTH_STATUS
 WHERE 1 = 1
	AND (A.DEP_ID LIKE '%' + @p_DEP_ID + '%' OR  @p_DEP_ID IS NULL OR @p_DEP_ID = '')
	AND (A.DEP_CODE LIKE '%' + @p_DEP_CODE + '%' OR  @p_DEP_CODE IS NULL OR @p_DEP_CODE = '')
	AND (A.DEP_NAME LIKE '%' + @p_DEP_NAME + '%' OR  @p_DEP_NAME IS NULL OR @p_DEP_NAME = '')
	AND (A.DAO_CODE LIKE '%' + @p_DAO_CODE + '%' OR  @p_DAO_CODE IS NULL OR @p_DAO_CODE = '')
	AND (A.DAO_NAME LIKE '%' + @p_DAO_NAME + '%' OR  @p_DAO_NAME IS NULL OR @p_DAO_NAME = '')
	AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
	AND (A.GROUP_ID LIKE '%' + @p_GROUP_ID + '%' OR  @p_GROUP_ID IS NULL OR @p_GROUP_ID = '')
	AND (A.TEL LIKE '%' + @p_TEL + '%' OR  @p_TEL IS NULL OR @p_TEL = '')
	AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
	AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
	AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
	AND (DATEDIFF(DAY, A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
	AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
	AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
	AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
	AND A.RECORD_STATUS = '1'
ELSE
SELECT TOP(CONVERT(INT,@P_TOP))A.*, B.BRANCH_CODE, B.BRANCH_NAME, C.AUTH_STATUS_NAME
FROM CM_DEPARTMENT A 
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS=C.AUTH_STATUS
 WHERE 1 = 1
	AND (A.DEP_ID LIKE '%' + @p_DEP_ID + '%' OR  @p_DEP_ID IS NULL OR @p_DEP_ID = '')
	AND (A.DEP_CODE LIKE '%' + @p_DEP_CODE + '%' OR  @p_DEP_CODE IS NULL OR @p_DEP_CODE = '')
	AND (A.DEP_NAME LIKE '%' + @p_DEP_NAME + '%' OR  @p_DEP_NAME IS NULL OR @p_DEP_NAME = '')
	AND (A.DAO_CODE LIKE '%' + @p_DAO_CODE + '%' OR  @p_DAO_CODE IS NULL OR @p_DAO_CODE = '')
	AND (A.DAO_NAME LIKE '%' + @p_DAO_NAME + '%' OR  @p_DAO_NAME IS NULL OR @p_DAO_NAME = '')
	--AND (A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')
	--AND ((A.BRANCH_ID LIKE '%' + @p_BRANCH_ID + '%' OR  @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = ''))
	AND (A.BRANCH_ID IN (SELECT * FROM @tmp))
	AND (A.GROUP_ID LIKE '%' + @p_GROUP_ID + '%' OR  @p_GROUP_ID IS NULL OR @p_GROUP_ID = '')
	AND (A.TEL LIKE '%' + @p_TEL + '%' OR  @p_TEL IS NULL OR @p_TEL = '')
	AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR  @p_NOTES IS NULL OR @p_NOTES = '')
	AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR  @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
	AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR  @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
	AND (DATEDIFF(DAY, A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) = 0 OR  @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
	AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR  @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
	AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR  @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
	AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR  @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
	AND A.RECORD_STATUS = '1'
COMMIT TRANSACTION