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
|