none
buscar todos los departamentos RRS feed

  • Pregunta

  • Hola,
    tengo el siguiente problema tengo un query en el cual le paso un parrametro el nombre es departamentos,si yo le paso un departamento
    ahi me funciona bien pero como hago para filtrar todo los departamentos ?
    select *
    from tabla
    where cod_departamento = @cod_depart 
    miércoles, 5 de octubre de 2011 22:32

Respuestas

  • Simplemente usas un case e igualas la columna, por ejemplo:

    select *
    from tabla
    where cod_departamento = COALESCE(@cod_depart,cod_departamento) 
    

    Este código asume que la columna cod_departamento no permitira valores nulos, hace un tiempo publique un articulo sobre este tema, puedes revisarlo en el siguiente link:

    http://geeks.ms/blogs/ghernandez/archive/2011/06.aspx

    Cualquier duda con gusto te podemos apoyar.

     


    "El talento es una disciplina tenaz y una larga paciencia"  Gustave Flaubert

     Email: info@geohernandez.com Blog: geeks.ms/blogs/ghernandez

     

    • Marcado como respuesta lopezLive viernes, 7 de octubre de 2011 15:27
    miércoles, 5 de octubre de 2011 22:51
  • Existen diferentes formas de lograr lo que quieres, pero debes tener en cuenta el desempenio, posibilidad de que el QO pueda usar indices existentes, etc.

    Una forma es la indicada por Geovanny, pero se esta forzando a SQL Server a comparar la columna consigo misma para cada fila, por lo que el optimizador tendera a escanear el indice o la tabla.

    Ejemplo:

    USE Northwind;
    GO
    DBCC freeproccache;
    GO
    SET SHOWPLAN_TEXT ON;
    GO
    DECLARE @OrderID int;
    
    SELECT *
    FROM dbo.Orders
    WHERE OrderID = COALESCE(@OrderID, OrderID);
    GO
    DECLARE @OrderID int;
    
    SET @OrderID = 10250;
    
    SELECT *
    FROM dbo.Orders
    WHERE OrderID = COALESCE(@OrderID, OrderID);
    GO
    SET SHOWPLAN_TEXT OFF;
    GO
    
    /*
    
    -- Q1
    
      |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]), WHERE:([Northwind].[dbo].[Orders].[OrderID]=CASE WHEN [@OrderID] IS NOT NULL THEN [@OrderID] ELSE [Northwind].[dbo].[Orders].[OrderID] END))
    
    
    -- Q2
    
      |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]), WHERE:([Northwind].[dbo].[Orders].[OrderID]=CASE WHEN [@OrderID] IS NOT NULL THEN [@OrderID] ELSE [Northwind].[dbo].[Orders].[OrderID] END))
    
    */
    

    Como puedes apreciar, el optimizador decide hacer un scan del indice clustered (que es por OrderID).

    Otra forma es indagar cuando el parametro es la marca NULL en el mismo predicado:

    ...
    WHERE OrderID = @OrderID OR @OrderID IS NULL;

    Esta forma padece del mismo problema que la anterior, aunque a partir de version 2008 SP1 CU5 se puede mejorar si usamos la opcion RECOMPILE, pero ojo que se necesita recompilar la sentencia en cada llamada, cosa que puede ser beneficiosa si existe la posibilidad de que el comportamiento conocido como "parameter sniffing" pueda interponerse en el desempenio de la sentencia.

    Otra forma puede ser usando multiples procedimientos, lo cual nos permite separar cada comportamiento y permitir al optimizador escojer el plan mas adecuado para cada caso.

    create procedure p1
    @p1 int
    as
    set nocount on;

    select *
    from T
    where c1 = @p1;
    go
    create procedure p2
    as
    set nocount on;

    select *
    from T
    go
    create procedure p3
    @p1 int
    as
    set nocount on;

    if @p1 is null
        exec p2;
    else
        exec p1 @p1;
    go
    exec p3;
    go
    exec p3 10250;
    go

    Tambien se puede recurrir al uso de sql dinamico. Esta opcion suele ser beneficiosa cuando tenemos muchos parametros para los cuales deseamos el mismo comportamiento. La opcion previa se hace muy engorrosa de usar cuando tenemos muchos parametros (serian muchas combinaciones de procedimientos).

    Tambien se puede aplicar ciertos trucos de acuerdo al tipo de dato, como es:

    -- suponiendo que la columna [cod_departamento] es un entero con dominio
    -- entre cero y el maximo valor
    select *
    from T
    where cod_departamento between coalesce(@cod_depart, 0) and coalesce(@cod_depart, 2147483647) ;

    Lo mismo pudieramos hacer si la columna [cod_departamento] fuese char / varchar:

    -- columna tipo string
    select *
    from T
    where cod_departamento LIKE coalesce(@cod_depart, '%');

    Es de notar que de acuerdo al tipo de acceso (seek o scan) que el optimizador decida usar, podemos tener problemas si el plan de ejecucion escogido no es el mas adecuado para el proximo valor asignado al parametro. Por ejemplo, si la primera vez se envia la marca NULL, el optimizador puede escojer hacer un scan, pero si la proxima vez que se ejecuta el sp se le pasa un valor que no sea la marca NULL, y el indice escogido tiene poca densidad para este valor entonces scanear seria mas costoso que si se hace un seek. Este problema se conoce como "parameter sniffing" (olfateo de parametros), y puedes hacer una busqueda en bing/google y leer sobre las diferentes formas de atacar este problema.

    Como decimos por aca, no existe una unica bala de plata que nos permita atacar las diferentes formas. Debes hacer un analizis de tu ambiente, indices, distribucion de datos, etc.

    Aqui te dejo un par de articulos, muy pero muy buenos, sobre este tema.

    The Curse and Blessings of Dynamic SQL
    http://www.sommarskog.se/dynamic_sql.html

    Dynamic Search Conditions in T-SQL
    http://www.sommarskog.se/dyn-search.html

     


    AMB

    Some guidelines for posting questions...

    • Editado HunchbackMVP jueves, 6 de octubre de 2011 15:18
    • Marcado como respuesta lopezLive viernes, 7 de octubre de 2011 15:28
    jueves, 6 de octubre de 2011 13:13

Todas las respuestas

  • Simplemente usas un case e igualas la columna, por ejemplo:

    select *
    from tabla
    where cod_departamento = COALESCE(@cod_depart,cod_departamento) 
    

    Este código asume que la columna cod_departamento no permitira valores nulos, hace un tiempo publique un articulo sobre este tema, puedes revisarlo en el siguiente link:

    http://geeks.ms/blogs/ghernandez/archive/2011/06.aspx

    Cualquier duda con gusto te podemos apoyar.

     


    "El talento es una disciplina tenaz y una larga paciencia"  Gustave Flaubert

     Email: info@geohernandez.com Blog: geeks.ms/blogs/ghernandez

     

    • Marcado como respuesta lopezLive viernes, 7 de octubre de 2011 15:27
    miércoles, 5 de octubre de 2011 22:51
  • Hola,

    gracias  por responder , pero como hago para el parametro asuma que son todo los departamentos y no uno ?, porque si yo le paso 1 el me trae  todo en base a ese numero pero como hago  para que me traiga (1,2,3,4).

    me di entender

    miércoles, 5 de octubre de 2011 23:35
  • Mira, con el código que te doy hace exactamente eso, puesto que realiza un comparativo y en caso que el parametro sea nulo el establece una condición de igualdad, es decir cod_departamento =cod_departamento  lo cuál siempre sera cierto y te retornara las filas con todos los departamentos.

    Cualquier duda con gusto te podemos apoyar.

    "El talento es una disciplina tenaz y una larga paciencia"  Gustave Flaubert

     Email: info@geohernandez.com Blog: geeks.ms/blogs/ghernandez

    miércoles, 5 de octubre de 2011 23:43
  • Existen diferentes formas de lograr lo que quieres, pero debes tener en cuenta el desempenio, posibilidad de que el QO pueda usar indices existentes, etc.

    Una forma es la indicada por Geovanny, pero se esta forzando a SQL Server a comparar la columna consigo misma para cada fila, por lo que el optimizador tendera a escanear el indice o la tabla.

    Ejemplo:

    USE Northwind;
    GO
    DBCC freeproccache;
    GO
    SET SHOWPLAN_TEXT ON;
    GO
    DECLARE @OrderID int;
    
    SELECT *
    FROM dbo.Orders
    WHERE OrderID = COALESCE(@OrderID, OrderID);
    GO
    DECLARE @OrderID int;
    
    SET @OrderID = 10250;
    
    SELECT *
    FROM dbo.Orders
    WHERE OrderID = COALESCE(@OrderID, OrderID);
    GO
    SET SHOWPLAN_TEXT OFF;
    GO
    
    /*
    
    -- Q1
    
      |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]), WHERE:([Northwind].[dbo].[Orders].[OrderID]=CASE WHEN [@OrderID] IS NOT NULL THEN [@OrderID] ELSE [Northwind].[dbo].[Orders].[OrderID] END))
    
    
    -- Q2
    
      |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]), WHERE:([Northwind].[dbo].[Orders].[OrderID]=CASE WHEN [@OrderID] IS NOT NULL THEN [@OrderID] ELSE [Northwind].[dbo].[Orders].[OrderID] END))
    
    */
    

    Como puedes apreciar, el optimizador decide hacer un scan del indice clustered (que es por OrderID).

    Otra forma es indagar cuando el parametro es la marca NULL en el mismo predicado:

    ...
    WHERE OrderID = @OrderID OR @OrderID IS NULL;

    Esta forma padece del mismo problema que la anterior, aunque a partir de version 2008 SP1 CU5 se puede mejorar si usamos la opcion RECOMPILE, pero ojo que se necesita recompilar la sentencia en cada llamada, cosa que puede ser beneficiosa si existe la posibilidad de que el comportamiento conocido como "parameter sniffing" pueda interponerse en el desempenio de la sentencia.

    Otra forma puede ser usando multiples procedimientos, lo cual nos permite separar cada comportamiento y permitir al optimizador escojer el plan mas adecuado para cada caso.

    create procedure p1
    @p1 int
    as
    set nocount on;

    select *
    from T
    where c1 = @p1;
    go
    create procedure p2
    as
    set nocount on;

    select *
    from T
    go
    create procedure p3
    @p1 int
    as
    set nocount on;

    if @p1 is null
        exec p2;
    else
        exec p1 @p1;
    go
    exec p3;
    go
    exec p3 10250;
    go

    Tambien se puede recurrir al uso de sql dinamico. Esta opcion suele ser beneficiosa cuando tenemos muchos parametros para los cuales deseamos el mismo comportamiento. La opcion previa se hace muy engorrosa de usar cuando tenemos muchos parametros (serian muchas combinaciones de procedimientos).

    Tambien se puede aplicar ciertos trucos de acuerdo al tipo de dato, como es:

    -- suponiendo que la columna [cod_departamento] es un entero con dominio
    -- entre cero y el maximo valor
    select *
    from T
    where cod_departamento between coalesce(@cod_depart, 0) and coalesce(@cod_depart, 2147483647) ;

    Lo mismo pudieramos hacer si la columna [cod_departamento] fuese char / varchar:

    -- columna tipo string
    select *
    from T
    where cod_departamento LIKE coalesce(@cod_depart, '%');

    Es de notar que de acuerdo al tipo de acceso (seek o scan) que el optimizador decida usar, podemos tener problemas si el plan de ejecucion escogido no es el mas adecuado para el proximo valor asignado al parametro. Por ejemplo, si la primera vez se envia la marca NULL, el optimizador puede escojer hacer un scan, pero si la proxima vez que se ejecuta el sp se le pasa un valor que no sea la marca NULL, y el indice escogido tiene poca densidad para este valor entonces scanear seria mas costoso que si se hace un seek. Este problema se conoce como "parameter sniffing" (olfateo de parametros), y puedes hacer una busqueda en bing/google y leer sobre las diferentes formas de atacar este problema.

    Como decimos por aca, no existe una unica bala de plata que nos permita atacar las diferentes formas. Debes hacer un analizis de tu ambiente, indices, distribucion de datos, etc.

    Aqui te dejo un par de articulos, muy pero muy buenos, sobre este tema.

    The Curse and Blessings of Dynamic SQL
    http://www.sommarskog.se/dynamic_sql.html

    Dynamic Search Conditions in T-SQL
    http://www.sommarskog.se/dyn-search.html

     


    AMB

    Some guidelines for posting questions...

    • Editado HunchbackMVP jueves, 6 de octubre de 2011 15:18
    • Marcado como respuesta lopezLive viernes, 7 de octubre de 2011 15:28
    jueves, 6 de octubre de 2011 13:13
  • Gracias Alejandro, lo que señalas es muy acertado, realmente hay cosas en los que el uso de SQL Dinamico resulta la mejor opción, pero siempre tiene una etiqueta de "Usar con precaución", esos articulos de Sommarskog son lectura obligatoria, hay una traducción en español para uno de ellos, voy a buscar el enlace.

    Cuando realizas este tipo de filtro, el tema de los indices es vital, como bien señala Alejandro cabe el uso de un Scan al indice o a la tabla.

    Saludos.

     


    "El talento es una disciplina tenaz y una larga paciencia"  Gustave Flaubert

     Email: info@geohernandez.com Blog: geeks.ms/blogs/ghernandez

     

    jueves, 6 de octubre de 2011 17:25
  • lopezLive, revise su otro post.  En ese otro post le doy una solución a este problema.  Justamente por eso es que no es bueno postear en dos lugares a la vez.  En fin, espero le sirva.  A mí me sirve de maravilla.
    Jose R. MCP
    jueves, 6 de octubre de 2011 17:28
  • Hola,

    Muchas gracias , me fue muy util

    viernes, 7 de octubre de 2011 15:28