ALTER PROCEDURE getAllClientForDepartment
	@DepartmentID INT,
	@flag INT   -- 0 - nonactive, 1 - active, 2 - all 
 
AS

set nocount on

	DECLARE @CodeField VARCHAR( 50 )
	DECLARE @Len INT
	DECLARE @sql VARCHAR( 2000 )

	SET @CodeField = (SELECT CodeField FROM DepartmentTree WHERE ChildID = @DepartmentID)

	SET @Len = LEN(@CodeField)

	SET @sql = 'SELECT Department.DepartmentID, Department.DepartmentName, Client.ClientID, Client.ClientCode, Client.ClientName, Client.ClientLegalName, Client.ClientStatus FROM #DepartmentTree 
	INNER JOIN Department ON Department.DepartmentID = #DepartmentTree.ChildID
	INNER JOIN Client ON Department.DepartmentID = Client.DepartmentID
	WHERE SUBSTRING(CodeField, 1, ' + CAST(@Len AS VARCHAR( 10 )) + ') = ''' + @CodeField + ''' AND LEN(CodeField) >= ' + CAST(@Len AS VARCHAR( 10 )) + ' AND Department.DepartmentType = 1 
	AND Client.ClientStatus '

	SET @sql = @sql + (
		SELECT 
			CASE 
				WHEN @flag =  0  THEN ' = 0 '
				WHEN @flag =  1  THEN ' = 1 '
				WHEN @flag =  2  THEN ' in(0, 1) '
			END) + ' ORDER BY ClientName'
	
	exec(@sql)

 --print @sql
 

Private Sub bt_Click()


On Error GoTo  999 
 
dhCreateTempTable "#DepartmentTree"

Me.InputParameters = "1,2"
Me.RecordSource = "dbo.getAllClientForDepartment"

Exit Sub

 999 :
dhError
'If Err.Number =  2580  Then Resume  0 

End Sub

Private Sub bt_Click()


On Error GoTo  999 
 
DoEvents
dhCreateTempTable "#DepartmentTree"

DoEvents
Me.InputParameters = "1,2"
DoEvents
Me.RecordSource = "dbo.getAllClientForDepartment"
DoEvents

Exit Sub

 999 :
dhError
'If Err.Number =  2580  Then Resume  0 

End Sub