Stored Procedures con parámetros

 

Seguramente hemos tenido que realizar un Stored Procedure o un query donde hemos tenido que agregar parámetros y nos vemos en la necesidad de realizar un query muy extenso y jugar a realizar todas las combinaciones posibles (si el parámetro 1 viene en null y el parámetro 2 no viene, solo se agrega en el where el parámetro 2) realizando el calculo de 2ⁿ.

En el ejemplo siguiente vemos como en la forma tradicional obtenemos una consulta donde se tiene un párametro para la BibliotecaId y otro para el RolId, y lo que queremos obtener es el listado de operadores que tienen permisos según los parámetros, quedando como a continuación se indica:

 

IF (@BibliotecaID is null)

BEGIN

   

    IF (@RolID is null)

    BEGIN

        Select *

        from bibroloperador

    END

    ELSE

    BEGIN

        Select *

        from bibroloperador

        where Rolid = @Rolid

    END

   

END

ELSE

BEGIN

    -- BibliotecaId is not null

    IF (@RolID is null)

    BEGIN

        Select *

        from bibroloperador

        where BibliotecaId = @BibliotecaID

    END

    ELSE

    BEGIN

        Select *

        from bibroloperador

        where BibliotecaId = @BibliotecaID

        And Rolid = @Rolid

    END

 

END

Realizando una modificación en el query, lo podemos dejar de la siguiente manera, la cual es mas fácil de entender y actualizar:

Select *

from bibroloperador

where (1 = Case

            When (@BibliotecaID is null) then 1

             When ((@BibliotecaID is not null) AND Bibliotecaid = @Bibliotecaid) then 1

             Else 0

          END)

 

AND (1 =   Case

             When (@RolID is null) then 1

             When ((@RolID is not null) AND Rolid = @Rolid) then 1

             Else 0

      END)

En este ultimo query, vemos que cada registro se va a evaluar de acuerdo a la clausula WHERE, donde si 1 = 1, entonces ese registro si lo regresará el SELECT, es por eso el 1 = CASE.

El CASE evaluará si el parámetro viene en NULL, donde si es así quiere decir que no habrá filtro por ese campo y por lo tanto el registro debe desplegarse en el SELECT, al igual en el segundo WHEN: donde el parámetro no viene NULL pero el dato de ese parámetro es igual al del campo del registro evaluado y por lo tanto también debe regresarse en el SELECT.

Si el parámetro no viene en NULL, ni es igual al del campo, quiere decir entonces que trae un valor del cual no queremos que se regrese en el SELECT por lo tanto el CASE regresará un 0, y 1 = 0 , por lo tanto el registro no lo regresará el SELECT.

 

Bibliografía:

Landrum, R., McGehee, S., & Voytek, W. J. (2008). Pro SQL Server 2008 reporting services. Berkeley, CA: Apress.