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.

Permisos en SQL Server 2000

Hace unos días me vi en la necesidad de crear diferentes funciones de usuarios para la Base de Datos (BD), en donde una función requería tener acceso sólo a unas tablas y otras funciones a otras tablas. El caso aquí es que el sistema que accede a esta BD se maneja por medio de Stores Procedures (SP), para lo cual investigué (mediante prueba y error) si es necesario asignar todos los permisos de SELECT, INSERT y DELETE a las tablas que se mandan llamar de los SP o si solo asignando el permiso de EXECUTE era suficiente.

Resultados:

• Al habilitar el permiso de EXECUTE del SP se heredan los permisos a lo que se ejecute dentro (INSERT, UPDATE y DELETE), por lo tanto solo es necesario asignar estos permisos en los SP y no en todas las tablas.
  
• No se pueden realizar ALTER y DROP a objetos de otro owner, solo se podrá hacer a los objetos creados por el mismo usuario. Ejemplo: Si nuestro usuario es [Prueba] y si dentro de un SP se realiza un DROP o ALTER al objeto [dbo].[Tabla], no se podría realizar, ya que solo se podría realizar si fuera [Prueba].[Tabla].
  
• El permiso de creación de tablas se otorga al usuario mediante la siguiente instrucción:
  • Grant CREATE TABLE To <useraccount>
    
• NOTA: Este permiso sólo habilita la creación de tablas que tengan como owner el usuario al cual tiene el permiso, es decir, si se especifica en el CREATE otro owner, como por ejemplo [dbo].[NuevaTabla], arrojaría un error. En este caso solo se tendría que hacer el CREATE TABLE [NuevaTabla] y esto generará la tabla como [usuario].[NuevaTabla].

Observación: Cuando dentro de un SP se manejan consultas dinámicas, por ejemplo:

     Declare @Query as varchar(8000)  --Se declara la variable que se usará para generar el query

     select @Query = 'Select * from Usuarios'  -- Se genera la consulta dinámica

     exec (@Query)  -- Se ejecuta la consulta dinámica

Se observó que es necesario dar los privilegios necesarios para las tablas que se usan en dicho query, en este ejemplo se tendría que otorgar el permiso de SELECT a la tabla "Usuarios".

Proyecto Final - Análisis de Datos del OPAC de CÓDICE (UANL)

En este video se muestra un análisis de datos realizado sobre la información de las cantidades de consultas realizadas en el Catalogo Electrónico de la Universidad Autónoma de Nuevo León (www.codice.uanl.mx).

La información utilizada es la obtenida desde abril de 2008 hasta diciembre de 2010, a la cual se le aplican los métodos vistos en la Análisis de datos para la toma de decisiones, como lo son: Outliers, Series de tiempos y Visualización de Datos.