Procedimientos almacenados con SQL Server. 1ª Parte

Aquí la introducción a procedimientos almacenados y triggers

Creación

Existen dos formas de crear procedimientos almacenados:

Utilizando la sentencia CREATE PROCEDURE o a través del Administrador corporativo.

Creación de procedimientos almacenados mediante comandos

CREATE PROCEDURE. Permite crear un procedimiento almacenado a partir de una serie de sentencias SQL unidas por sentencias adicionales de control de flujo. La sintaxis resumida de esta sentencia es la siguiente:

CREATE PROCEDURE [propietario.] nombre_procedimiento(;número)

((lista_de_parametros)) FOR REPLICATION) | (WITH RECOMPILE)

[([WITH] 1 (,]) ENCRYPTION]] AS sentencias SQL

El nombre del procedimiento será un identificador para el mismo, no puede superar los 128 caracteres de longitud.

Ejemplo de creación de un procedimiento almacenado:

CREATE PROCEDURE [Dbo].[Nombre_Procedimiento]

     @strVariable1      Char(4),

     @strVariable2       VarChar(12),

     @bteBit            bit

AS   

  /*Secuencia de sentencias de SQL Server. La secuencia mostrada es un ejemplo sencillo y se puede complicar tanto como queramos */

  IF @bteBit = 1  

    SELECT strCampo1, strCampo2, strCampo3

      FROM dbo.tbTabla

      WHERE strCampo1 = @strVariable1 AND strCampo2 = @strVariable2 AND                     strCampo3 Is Not NULL

   ELSE   /* si la variable de entrada no es 1 */

     SELECT strCampo1, strCampo2, strCampo3

      FROM dbo.tbTabla

      WHERE strCampo1 = @strVariable1 AND strCampo2 = @strVariable2 AND                      strCampo3 Is NULL

  RETURN

Es posible cambiar el nombre de un procedimiento almacenado, pero no se dispone de una sentencia específica, por lo que hay que hacerlo ejecutando el procedimiento almacenado de sistema sp_rename del siguiente modo:

 sp_rename nombre_antiguo, nombre_nuevo

Una vez creado el procedimiento almacenado podemos ejecutarlo desde el administrador de consultas o desde un programa externo utilizando la cláusula EXECUTE del siguiente modo:

EXECUTE sp_Nombre_Procedimiento ('Valor_variable1', 1, 'Valor_variable3', True)

(El 1 y True es un valor numérico y otro booleano respectivamente, esto es un ejemplo, los valores de cada variable se ajustarán al tipo determinado en el procedimiento)

Una vez creado, el procedimiento almacenado se puede modificar con la cláusula ALTER PROCEDURE al ejecutar esta cláusula, SQL server modificará el procedimiento almacenado con las modificaciones introducidas en él.

Agrupamiento de procedimientos almacenados

En una base de datos suele existir un conjunto de procedimientos diseñados para realizar tareas relacionadas, de manera que sólo tengan sentido de forma conjunta. Para estas situaciones SQL Server permite agrupar un conjunto de procedimientos almacenados de manera que puedan ser eliminados con una sola sentencia DROP PROCEDURE para ello, es necesario asignar a todos esos procedimientos el mismo nombre, al que se añade un punto y coma con un número identificativo.

--Crea tres procedimientos almacenados con el mismo nombre pero distinguidos por el numeral.

CREATE PROCEDURE Nombre_Procedimiento; 1

CREATE PROCEDURE Nombre_Procedimiento; 2

CREATE PROCEDURE Nombre_Procedimiento; 3

--Elimina de una sola vez todos los procedimientos almacenados relacionados con el mismo nombre.

DROP PROCEDURE Nombre_Procedimiento

Parámetros

Los procedimientos almacenados permiten que su ejecución se adapte a la situación en el que son llamados. Los procedimientos pueden recibir parámetros como las funciones de los lenguajes de programación. De este modo el procedimiento puede recibir datos en función de la situación en se realice la llamada. Los parámetros se definen en su creación y cada procedimiento puede recibir hasta 2.100 parámetros.

Cuando se ejecuta un procedimiento  con parámetros, es necesario suministrar los valores para ellos. La sintaxis de definición de un parámetro es la siguiente:

@Parametro tipo_de_datos (= valor por defecto) [OUTPUT]

El nombre de parámetro, aparece precedido por el carácter @. Este carácter pertenece al nombre del parámetro. Debe especificarse un tipo de datos para cada uno de los parámetros. Puede especificarse un valor por defecto para cada parámetro.  Se hace colocando después del nombre de parámetro el signo =  seguido por la constante que se utilizará como valor.

Parámetros de retorno. Se especifican como cualquier otro parámetro pero al final se añade la palabra OUTPUT. Cuando se ejecuta el procedimiento estos parámetros devolverán el valor que han tomado en la ejecución, estos valores se pueden almacenar en variables.

Procedimientos de recompilación forzosa. RECOMPILE

Los procedimientos almacenados se compilan, es decir, se genera una estrategia optimizando el acceso a los datos que se solicitarán sólo la primera vez que son ejecutados.

Si generamos un procedimiento almacenado con parámetros de tipos diversos. Esto supone que sea común que la optimización que se ha llevado a cabo en primera instancia no sea válida para otras ejecuciones del procedimiento. Por otra parte, es común que frecuentemente se añadan nuevos índices que hagan que la optimización de la consulta deje de ser válida. En estas situaciones la estrategia originalmente diseñada deja de tener validez y es preciso recompilar el procedimiento de nuevo, para ello podemos añadir al procedimiento almacenado la cláusula WITH RECOMPILE.

CREATE PROCEDURE [Dbo].[Nombre_Procedimiento]

     @strVariable1        Char(4),

     @strVariable2       VarChar(12),

     @bteBit         bit

WITH RECOMPILE

AS   

  /*Secuencia de sentencias de SQL Server.

Si no queremos que se recompile cada vez que se ejecute pero si la siguiente vez pues hemos creado un nuevo índice, podemos utilizar el procedimiento almacenado de sistema sp_recompile.

EXEC sp_recompile 'Dbo.Sp_Mi_Procedimient_Almacenado'

Procedimientos de descripción encriptada. ENCRYPTION

Cada procedimiento almacenado que se crea aparece en la tabla de sistema sys.syscomments como una fila en la que se almacena la descripción que se proporcionó al crearlo, de manera que dicho procedimiento queda recreado en esta tabla. Es posible encriptar esta descripción del procedimiento utilizando la cláusula WITH ENCRYPTION.

CREATE PROCEDURE [Dbo].[Nombre_Procedimiento]

     @strVariable1        Char(4),

     @strVariable2       VarChar(12),

     @bteBit         bit

WITH ENCRYPTION

AS   

  /*Secuencia de sentencias de SQL Server.

Permisos de creación

Sólo puede crear procedimientos almacenados el propietario de la base de datos aunque también puede transferir este privilegio a otros usuarios.

Sentencias SQL del procedimiento almacenado

El cuerpo del procedimiento está formado por un conjunto de sentencias SQL que realizarán las tareas que esperamos del mismo y son las que se ponen después de la cláusula AS. Los Procedimientos Almacenados pueden ocupar hasta un máximo de 250 Mb.

Sentencias admisibles

En general un procedimiento almacenado admite cualquier número y tipo de sentencias de SQL aunque existen algunas restricciones:

Sentencias de creación.

No pueden incluirse sentencias para crear objetos como

CREATE VIEW.

CREATE TRIGGER.

CREATE DEFAULT.

CREATE PROCEDURE.

CREATE RULE.

El resto de objetos de bases de datos sí pueden ser creados en un procedimiento almacenado, incluyendo tablas temporales.

Sentencias de eliminación de objetos.

La familia de sentencias DROP si está permitida.

Creación de tablas temporales.

Igual que pueden crearse tablas permanentes, también está permitido crear tablas temporales, pero sólo tendrán sentido durante el tiempo de ejecución del procedimiento.

Calificación de nombres

Los procedimientos almacenados pueden ser ejecutados por usuarios diferentes a los propietarios de los objetos a los que se hace referencia en interior de los mismos. El nombre del propietario de objeto forma parte de su nombre, aunque a veces se puede omitir. Un identificador de un objeto está calificado cuando se especifica manera explícita el identificador del propietario del mismo.

Los nombres de los objetos a los que se hace referencia en un procedimiento almacenado pueden resolverse en el momento de la definición del procedimiento o durante su ejecución, dependiendo del tipo de sentencia que se esté ejecutando.

Sentencias que se resuelven en tiempo de ejecución:

ALTER TABLE

CREATE TABLE

DROP TABLE

TRUNCATE TABLE

CREATE INDEX

DROP INDEX

UPDATE STATISTICS

Si la resolución se produce en tiempo de ejecución, los nombres de los objetos a los que se haga referencia deberán estar calificados, es decir con el propietario especificado delante del nombre, pues si no, el gestor actuará de forma errónea.

De este modo si un usuario hace uso de un procedimiento almacenado que hace uso,  de una tabla sin calificar, y posee él mismo en su propietario una tabla con el mismo nombre, el gestor utilizará esta tabla en vez de la que el creador del procedimiento almacenado había previsto.

En los ejemplos anteriores siempre poníamos

SELECT strCampo1, strCampo2, strCampo3

      FROM dbo.tbTabla

Pues si ponemos

SELECT strCampo1, strCampo2, strCampo3

      FROM tbTabla

Y el usuario que lo ejecuta tiene un propietario diferente de dbo pero una tabla llamada tbTabla, entonces hará uso de esta y no de la que se especifica en el procedimiento almacenado.

Creación de procedimientos almacenados mediante el Administrador Corporativo

Para crear directamente un procedimiento almacenado mediante el Administrador Corporativo. Debemos entrar en el  servidor y en la base de datos en los que vamos a crear el procedimiento, en el Administrador Corporativo. Seleccionamos el nodo Procedimientos almacenados, y, al pulsar con el botón derecho del ratón, seleccionamos del menú Nuevo procedimiento almacenado.

Escribimos las sentencias de creación del procedimiento almacenado sobre la plantilla que nos genera  y cuando terminamos podemos pulsar el botón depurar para comprobar la sintaxis y asegurarnos de que la sentencia es correcta.

Pulsar el botón Ejecutar para que procedimiento se cree con el nombre especificado en la sentencia. Una vez creado podremos modificar sus permisos de ejecución posicionándonos sobre el procedimiento almacenado y pulsando el botón derecho del ratón donde nos saldrá el menú.

Si pulsamos modificar nos abrirá el código del procedimiento almacenado en el área de trabajo donde podremos modificarlo testearlo y guardarlo como se ha explicado más arriba. Si queremos entre otras cosas modificar sus permisos, entonces pulsaremos en el menú la opción propiedades la cual nos abrirá un cuadro de diálogo como el mostrado abajo. En la pestaña permisos aparecen estos donde podremos modificarlos.

En la pestaña general podremos ver las características principales del procedimiento almacenado.

Eliminación de procedimientos almacenados

La sentencia DROP PROCEDURE sirve para eliminar de la base de datos un procedimiento almacenado y su sintaxis y uso resulta totalmente análogo a las que se utilizan para eliminar cualquier otro objeto de una base de datos. Excepto lo que ya se comentó sobre los procedimientos almacenados agrupados. Este tipo de procedimientos se eliminan conjuntamente con  una sola sentencia DROP PROCEDURE.