CREATE PROCEDURE [dbo].[fetch_team_member]
    @team_tech_id nvarchar(max) = '',
    @member_tech_id nvarchar(max) = '',
    @fields nvarchar(max) = '',
    @department_ids nvarchar(max) = '',
    @team_id nvarchar(max) = '',
    @team_lead_id nvarchar(max) = '',
    @member_department_id nvarchar(max) = '',
    @status nvarchar(max) = '',
    @no_nested bit = 0,
    @forSales bit = 0,
    @filterClause nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    SET @fields = iif(@fields = '','*',@fields);
    SET @select_query = 'select '+@fields+' from dim_users where 1=1'
    IF  @team_tech_id != ''
        BEGIN
            SET @select_query = @select_query + ' and  my_team_tech_id in (' + @team_tech_id + ')'
        END
    IF  @member_tech_id != ''
        BEGIN
            SET @select_query = @select_query + ' and  technology_id in (' + @member_tech_id + ')'
        END
    IF  @team_id != ''
        BEGIN
            SET @select_query = @select_query + ' and  team_id in (' + @team_id + ')'
        END

    IF  @team_lead_id != '' and @forSales = 0
        BEGIN
            SET @select_query = @select_query + ' and  team_lead_id in (' + @team_lead_id + ')'
        END
    IF  @team_lead_id != '' and @forSales = 1
        BEGIN
            SET @select_query = @select_query + ' and  sales_team_lead_id in (' + @team_lead_id + ')'
        END

    IF @department_ids != ''
    	BEGIN
            SET @select_query = @select_query + ' and  my_team_dept_id in (' + @department_ids + ')'
        END
    IF @member_department_id != ''
        BEGIN
            SET @select_query = @select_query + ' and department_id IN (' + @member_department_id+')'
        end
    IF @status != ''
        BEGIN
            SET @select_query = @select_query + ' and user_status = ' + @status
        end
    IF @no_nested = 1
        BEGIN
            SET @select_query = @select_query + ' and user_id not in (select lead_id from dim_teams where lead_id is not null) ' +
                                ' and user_id not in (select ex_employee_team_lead from view_user_master where user_status = 0 and ex_employee_team_lead is not null) '
        end
    IF @filterClause != ''
        BEGIN
            SET @select_query = @select_query + @filterClause
        end
        SET @select_query = @select_query + ' order by display_name asc'
   EXEC sp_executesql @select_query;
go

