Question

hola a todos tengo este pedazo de stored procedure

    set @sql='select a.[id_signum],(b.[last_name]+'', ''+b.[first_name]) as [name],a.[jobStage],b.[id_cats],b.[id_ah],b.[ah],lower(j.[signum]) as [lm],(j.[last_name]+'', ''+j.[first_name]) as [lmName], lower(k.[signum]) as [om],(k.[last_name]+'', ''+k.[first_name]) as [omName],c.[description] as [domian], d.[description] as [subdomian], e.[description] as [sdu], f.[description] as [serviceArea],[gtt],[start_date],[final_date], g.[description] as [team], h.[description] as [region], i.[description] as [customer],[tg2],[shift],l.[description]as[country],a.[typeProject]
    from    [RIA].[dbo].[resource_tracking] a,
            [RIA].[dbo].[resources] b,
            [RIA].[dbo].[cat_domain] c,
            [RIA].[dbo].[cat_subdomain] d,
            [RIA].[dbo].[cat_sdu] e,
            [RIA].[dbo].[cat_serviceArea] f,
            [RIA].[dbo].[cat_team] g,
            [RIA].[dbo].[cat_region] h,
            [RIA].[dbo].[cat_customer] i,
            [RIA].[dbo].[manager] j,
            [RIA].[dbo].[manager] k,
            [RIA].[dbo].[cat_country] l
    where   a.[id_signum]=b.[signum] AND
            g.[id_domain]=c.[id_domain] AND
            a.[id_subdomain]=d.[id_subdomain] AND
            a.[id_sdu]=e.[id_sdu] AND
            a.[id_service_area]=f.[id_pa] AND
            a.[id_team]=g.[id_team] AND
            g.[id_region]=h.[id_region] AND
            a.[id_customer]=i.[id_customer] AND
            b.[id_lm]=j.[id_manager] AND
            b.[id_om]=k.[id_manager] AND
            a.[id_country]=l.[id_country] AND
            g.[description]=''Integration C4'' AND --------------------------- como puedo buscar mas de 1 tipo a la vez dentro de este mismo codigo
            (   (a.[start_date]<='''+convert(varchar(20),@startDate)+''' and a.[final_date]>='''+convert(varchar(20),@endDate)+''') or
                (a.[start_date]> '''+convert(varchar(20),@startDate)+''' and a.[start_date]<='''+convert(varchar(20),@endDate)+'''   and a.[final_date]>='''+convert(varchar(20),@endDate)+''') or
                (a.[start_date]<='''+convert(varchar(20),@startDate)+''' and a.[final_date]>='''+convert(varchar(20),@startDate)+''' and a.[final_date]< '''+convert(varchar(20),@endDate)+''') or
                (a.[start_date]> '''+convert(varchar(20),@startDate)+''' and a.[final_date]< '''+convert(varchar(20),@endDate)+'''   and a.[start_date]<= a.[final_date]) )'

insert #trackingAll exec(@sql)

no se como hacer para que no me marque error si por ejemplo pongo un in en la parte del codigo que comente porq quiero hacer una busqueda de solo 3 tipos de team
de antemano gracias.

Answer 1

¿Intentaste con el operador IN?

Te quedaría algo como:

SELECT
   ...
FROM
   ...
WHERE
   ...
   AND g.[description] IN (@tiposTeam);

Donde @tiposTeam tendría este valor 'Integración C4', 'Integración A3', 'Integración B1'.

Cuidado con el valor de la variable @tiposTeam por que debes tener las comillas escapadas y sin comas de mas, ya que, en error como estos en la generación de SQL dinámico, significa que directamente no va a correr la consulta.