TEMA 16: Creando, ejecutando, modificando y borrando procedimientos almacenados

Como se guarda un procedimiento

Cuando se crea un procedimiento, SQL Server chequea la sintaxis de los comandos Transact-SQL que incluye. Si la sintaxis es incorrecta, SQL Server generará un mensaje  de error “sintax incorrect” (sintaxis incorrecta), y el procedimiento no será creado. Si el procedimiento pasa el chequeo de sintaxis, el procedimiento se guarda, escribiéndose su nombre y otras informaciones (tal como un número autogenerado de identificación) en la tabla SysObject. El texto usado para crear el procedimiento se escribe en la tabla SysComments de la base de datos actual.

El siguiente comando SELECT consulta a la tabla SysObjects en la base de datos Pubs para mostrar el número de identificación del procedimiento almacenado ByRoyalty:

SELECT [name].[id] FROM [pubs].[dbo].[SysObjects]

WHERE [name] = ‘byroyalty’

 Esta consulta retorna lo siguiente:

name	id
byroyalty	581577110

Usando la información retornado de la tabla SysObjects, el próximo comando SELECT consulta a la tabla SysComments al usar el número de identificación del procedimiento almacenado ByRoyalty:

SELECT [text] FROM [pubs].[dbo].[SysComments]

WHERE [id] = 581577110

Esta consulta retorna el texto del procedimiento almacenado, cuyo número de identificación es el 581577110.

Usar el procedimiento almacenado de sistema sp_helptext es una mejor opción para mostrar el texto usado para crear un objeto (tal como un procedimiento almacenado no encriptado), dado el texto es retornado en múltiples filas. 

Métodos para crear procedimientos almacenados

SQL Server provee muchos métodos que se pueden usar para crear un procedimiento almacenado: el comando Transact-SQL CREATE PROCEDURE, SQL-DMO (usando el objeto StoredProcedure y escapa alcance de este Kit), a través del árbol de consola del Enterprise Manager, el asistente para crear procedimientos almacenados (al cual se puede acceder a travé del Enterprise Manager).

El comando CREATE PROCEDURE

Se puede usar el comando CREATE PROCEDURE, o su versión abreviada, CREATE PROC, para crear un procedimiento almacenado en el Query Analyzer o una herramienta de comando como osql. Cuando utiliza CRETE PROC, se pueden realizar las siguientes tareas:

·        Especificar agrupamientos de procedimientos almacenados

·        Definir parámetros de entrada-salida, sus tipos de datos, y sus valores por defecto. Cuando se definen parámetros de entrada y salida, estos siempre van precedidos por el signo @, seguido del nombre del parámetro y luego una designación del tipo de dato. Los parámetros de salida deben incluir la palabra clave OUTPUT para diferenciarlos de los de entrada.

·        Usar códigos de retorno para mostrar información acerca del éxito o falla de una tarea.

·        Controlar si un plan de ejecución debería ser guardado temporariamente (en el cache) para un procedimiento.

·        Encriptar el contenido del procedimiento almacenado por razones de seguridad.

·        Especificar las acciones que deberá tomar el procedimiento almacenado cuando se ejecute.

Proveer de contexto a un procedimiento almacenado

Con la excepción de los procedimiento almacenado temporarios, un procedimiento almacenado se crea siempre en la   base de datos actual. Por lo tanto, Ud. siempre debería especificar la base de datos actual usando el comando USE nombre_base seguido por el por el comando GO ntes de crear un procedimiento almacenado- Se puede usar la lista desplegable Cahnge Database en el Query Analizer para seleccionar la base de datos actual.

El script siguiente selecciona la base Pubs y luego crea un procedimientos llamado ListAuthorNames (lista de los nombres de los autores) , que pertenece a dbo:

USE Pubs

GO

CREATE PROCEDURE [dbo].[ListAuthorNames]

AS

SELECT [au_fname], [aufname] FROM [pubs].[dbo].[authors]

 Fíjese que el nombre del procedimiento está totalmente calificado en el ejemplo. Un nombre  de procedimiento almacenado totalmente incluye el nombre de su propietario (en este caso dbo) y el nombre del procedimiento, ListAuthorNames. Se especifica dbo como propietario si se quiere asegurar que la tarea del procedimiento almacenado correrá sin importar la tabla propietaria en la base de datos. El nombre de la base de datos no es parte del nombre totalmente calificado de un procedimiento almacenado cuando se usa el comando CREATE PROCEDURE.

Crear procedimientos almacenados temporarios

Para crear un procedimiento almacenado temporal local, agregue adelante del nombre del procedimiento el símbolo #. Este signo numeral instruye al SQL Server para que cree el procedimiento en la TempDB. Para crear un procedimiento almacenado temporario global, , se agrega adelante del nombre del procedimiento un doble símbolo numeral ##, que también será creado en la TempDB. SQL Server ignora la base de datos actual cuando crea un procedimiento temporario. Por definición, un procedimiento almacenado sólo puede existir en la TempDb. Para crear un procedimiento almacenado directamente en la TempDB que no es ni local ni global haga que la TempDB sea la base de datos actual y luego cree el procedimiento. El ejemplo siguiente crea un procedimiento almacenado temporario local, un procedimiento almacenado temporario global y un procedimiento directamente en la TempDB:

·        crear un  procedimiento temporario local

CREATE PROCEDURE #localtemp

AS

SELECT * from [pubs].[dbo].[authors]

GO

·        crear un  procedimiento temporario global

CREATE PROCEDURE #globaltemp

AS

SELECT * from [pubs].[dbo].[authors]

GO

·        crear un procedimiento almacenado temporario que sea local en tempdb

USE TEMPDB

GO

CREATE PROCEDURE directtemp

AS

SELECT * from [pubs].[dbo].[authors]

GO

Nombres de bases de datos totalmente calificadas son especificadas en los comandos SELECT. Si el procedimiento no se ejecuta en el contexto de una base de datos específica, luego nombre de bases de datos totalmente calificadas aseguran que se está referenciando a la base de datos apropiada.

En el tercer parte del ejemplo, cuando se crea un procedimiento almacenado temporario directamente en la base de datos TempDB, se debe hacer a la base de datos TempDB la base actual antes de crearlo, o se debe calificar totalmente el nombre ([TempDb].[dbo].[directtemp]). Tal como los procedimientos almacenados del sistema, que se guardan en la base Masters, los procedimientos almacenados temporarios locales y globales están disponibles para ser usados por su nombre corto (sin importar la base de datos actual).

Agrupar, almacenar temporariamente en memoria y encriptar procedimientos almacenados

Los procedimientos almacenados puedes ser agrupados lógicamente al momento de su creación. Esta técnica es muy útil para procedimientos almacenados que deberían ser administrados cono una unidad independiente y que son usados por una aplicación específica. Para agrupar procedimientos almacenados, se debe asignar a cada procedimiento del grupo el mismo nombre y agregarle un número único separado por un punto y coma. Por ejemplo: el denominar a dos procedimientos almacenados con ProcAgrupado;1 y ProcAgrupado;2 al momento de su creación, los agrupa lógicamente. Cuando se ve el contenido de ProcAgrupado, se ve el código para ambos ProcAgrupado;1 y ProcAgrupado;2.

Por defecto, un plan de ejecución de un procedimiento almacenado se guarda en memoria temporal (cacheo) la primera vez que se ejecuta, y no es cacheado de nuevo hasta que el servidor se reinicie o hasta que la estructura de una tabla usada por el procedimiento almacenado cambie. Por razones de performance se podría no querer cachear un plan de ejecución de un procedimiento almacenado. Por ejemplo, cuando los parámetros de un procedimiento almacenado varían considerablemente de una ejecución a la siguiente, cachear un plan de ejecución es contraproducente. Para provocar que un procedimiento almacenado sea recomplilado cada vez que se ejecuta, agregue las palabras claves WITH RECOMPILE cuando crea el procedimiento almacenado. Se puede también forzar una recompilación mediante el uso del procedimiento almacenado del sistema sp_recompile o especificando WITH RECOMPILE cuando se ejcuta el procedimiento.

Encriptar un procedimiento almacenado protege su contenido de ser visto. Para encriptar un procedimiento almacenado, use la palabra clave WITH ENCRYPTION cuando crea el procedimiento. Por ejemplo, el código siguiente crea un procedimiento encriptado llamado Protected:

USE Pubs

GO

CREATE PROC [dbo].[protected] WITH ENCRYPTION

AS

SELECT [au_fname], [au_lname] FROM [pubs].[dbo].[authors]

Las palabras claves WITH ENCRYPTION encriptan la columna de texto del procedimiento almacenado en la tabla SysComments. Un modo simple para determinar si un procedimiento almacenado está encriptado es usar la función OBJECTPROPERTY:

•      controla si el procedimiento almacenado esta encriptado retornando 1 para IsEncrypted

SELECT OBJECTPROPERTY(object_id(‘protected’), ‘IsEncrypted’)

O llamando al procedimiento con sp_halptext:

•      si el procedimiento esta encriptado, retorna “The objects comments have been encrypted.”

EXEC sp_helptext protected

Un procedimiento almacenado encriptado no puede ser replicado. Después que un procedimiento almacenado es encriptado, SQL Server lo desencripta para sólo para su ejecución. Su definición no puede ser desencriptada para ser vista por nadie, incluido el propietario del procedimiento almacenado. Por lo tanto, se debe estar seguro que se cuenta con una copia en algún lugar seguro antes de encriptar un procedimiento almacenado.

Enterprise Manager

Se puede crear un procedimiento almacenado directamente en el Enterprise Managrer. Para ello, se expande el árbol de la consola para su servidor y luego se expande la base de datos donde se creará el procedimiento almacenado. Clic derecho sobre el nodo Stored Procedure, y luego clic sobre New Stored Procedure. Cuando aparezca el cuadro de diálogo Stored Procedure Properties – New Stored Procedure se ingresa el contenido del nuevo procedimiento almacenado. La figura muestra el cuadro de diálogo Stored Procedure Properties – New Stored Procedure que contiene el código del ejemplo anterior.

Se puede, además, controlar la sintaxis del procedimiento almacenado antes de su creación y guardar una plantilla que aparecerá siempre que se cree un nuevo procedimiento almacenado y se configuren permisos. Una vez que el procedimiento almacenado se crea, se puede abrir las propiedades del procedimiento y configurar los permisos. Por defecto el propietario del procedimiento y el administrador tienen permisos totales sobre el procedimiento almacenado.

Las plantillas son muy útiles porque proveen un marco de trabajo para crear documentación consistente sobre los procedimientos almacenados. Comúnmente se agrega texto al encabezamiento que describe como se deberá documentar cada procedimiento almacenado que se crea.

El asistente Create Stored Procedure

El asistente Create Stored Procedure (crear procedimientos almacenados) permite crear nuevos procedimientos almacenados paso a paso. Se puede acceder al asistente seleccionando Wizars (asistentes) desde el menú Tools (herramientas). En la ventana Select Wizard (seleccionar asistente), expandir la opción Database (base de datos), y luego seleccionar Create Stored Procedure Wizard (asistente para crear procedimientos almacenados) y presionar Ok. A partir de quí se deben completar los pasos indicados por el asistente. La Figura muestra la pantalla de bienvenida del Create Stored Procedure Wizard mostrando las cosas que se pueden hacer con él.

El asistente Create Stores Procedures lo habilita para que Ud. cree procedimientos que inserten, eliminen, o actualicen datos en las tablas. Para modificar los procedimientos almacenados que crea el asistente, se puede editarlos dentro del asistente o utilizar otras herramientas (como el Query Analizer).

Crear y agregar procedimientos almacenados extendidos

Después de crear un procedimiento almacenado extendido, se debe registrar con SQL Server.  Sólo los usuarios que tiene permisos de administrador pueden registrar un procedimiento almacenado con el SQL Server. Para registrar un procedimiento almacenado extendido, se puede usar el procedimiento del sistema sp_addextendedproc en el Query analyzer o usar el Enterprise Manager. En el Enterprise Manager, expanda la base de datos Master, clic derecho sobre el nodo Extended Stored Procedure (procedimiento almacenado extendido), y clic sobre la opción New Extended Stored Procedure (nuevo procedimiento almacenado extendido). Los procedimientos almacenados extendidos sólo pueden ser agregados a la base de datos Master. 

Resolución diferida de nombres

Cuando se crea un procedimiento almacenado, SQL Server no controla por la existencia de los objetos que este pueda referenciar. Esto es así porque es posible que un objeto, tal como una tabla referenciada en un procedimiento almacenado no existe al momento de la creación del procedimiento almacenado. Esta capacidad se denomina resolución diferida de nombres. La verificación de la existencia de los objetos se produce al momento de la efectiva ejecución del procedimiento.

Cuando se refiere a un objeto (como una tabla) en un procedimiento almacenado se debe asegurar que se especifica el propietario del objeto. Por defecto, SQL Server  asume que el creador del procedimiento almacenado es también el propietario de los objetos referenciados en el procedimiento. Para evitar confusión, considere especificar a dbo como el propietario de todos objetos (tanto los procedimientos almacenados como los objetos referenciados dentro de estos).

Ejecutar un procedimiento almacenado

Como ya vimos, se puede correr un procedimiento almacenado en el Query Analyzer simplemente ingresando su nombre y cualquier parámetro requerido. Por ejemplo se vió el contenido de un procedimiento almacenado ingresando sp_helptext seguido del nombre del procedimiento almacenado a ser visto. El nombre del procedimiento almacenado a ser visto es en este caso el valor del parámetro.

Si el procedimiento almacenado no es el primer comando en un batch, para ejecutarlo se debe preceder el nombre del procedimiento con las palabras claves EXECUTE o EXEC. 

Llamar un procedimiento almacenado para ejecución

Cuando se especifica el nombre de un procedimiento, el nombre puede ser totalmente calificado, tal como [nombre_base].[propietario].[nombre_proc] o, si la base que contiene al procedimiento es la base actual, mediante el comando USE, entonces se puede ejecutar el procedimiento almacenado especificando sólo [propietario].[nombre_proc]. Si el nombre de el nombre del procedimiento es único en la base de datos activa, se puede simplemente especificar [nombre_proc].

Nota: En este tema se han utilizado los identificadores entre [] en los nombres de los objetos, aún cuando dichos nombres no violan la reglas de nombres de objetos, con fines de claridad de los ejemplos.

Nombres totalmente calificados no son necesarios cuando se ejecutan procedimientos del sistema que tienen el prefijo sp_, ni para procedimientos almacenados temporarios locales, o globales. SQL Server  buscará en la base Master por cualquier procedimiento almacenado que tenga el prefijo sp_ donde el propietario sea dbo. Para evitar confusión, no se debe nombrar procedimientos almacenados locales con los mismos nombres de procedimientos almacenados en la base Master. Si ello ocurre, se debe asegurar que el propietario de la base sea otro que dbo. SQL Server  no busca automáticamente en la base master por procedimientos almacenados extendidos, Por lo tanto, o se lo invoca por su nombre totalmente calificado o se cambia la base actual a la que pertenece el procedimiento. 

Especificar parámetros y sus valores

Si un procedimiento almacenado requiere valores de parámetros, se deben especificar cuando se ejecuta el procedimiento. Cuando se definen parámetros de entrada y salida se los precede del símbolo @, seguido por el nombre del parámetro y la designación del tipo de dato. Cuando se los invoca para ser ejecutados, se debe incluir un valor para el parámetro (y opcionalmente, el nombre del parámetro). Los próximos dos ejemplos corren el procedimiento almacenado au_info de la base Pubs con dos parámetros: @lastname (apellido) y @firstname (nombre):

 --llama al procedimiento almacenado con los valores de los parámetros

USE Pubs

GO

EXECUTE au_info Green, Marjorie

--llama al procedimiento alamcenado con los valores y nombres de los parámetros

USE Pubs

GO

EXECUTE au_info

Green, Marjorie

@lastname = ‘Green’, @firstname = ‘Marjorie’

En el primer ejemplo, los valores de los parámetros fueron especificados pero los nombres no. Si se especifican los valores sin sus correspondientes nombres, los valores se deben indicar en el mismo orden con se especificaron en la definición del procedimiento. En el segundo ejemplo, los nombres y los valores son ambos indicados. En este caso, se los puede poner en cualquier orden. Aquellos parámetros a los que se les especifica un valor por defecto en la definición del procedimiento no necesitan ser obligatoriamente especificados cuando se los llama.

La lista siguiente muestra algunas sintaxis opcionales disponibles cuando se ejecuta un procedimiento almacenado:

• Un variable de código de retorno de tipo entero (integer) para almacenar valores retornados desde el procedimiento almacenado. La palabra clave RETURN con un valor (o unos valores) debe ser especificada en el procedimiento almacenado para que este variable opcional funcione.
• Un punto y coma seguida por un número de grupo. Para procedimientos almacenados agrupados, se puede o ejecutar todos los procedimientos en el grupo simplemente especificando el nombre del procedimiento almacenado, o se puede incluir un número para indicar cual procedimiento almacenado se desea ejecutar. Por ejemplo, si se crean dos procedimientos almacenados llamados ProcAgrupado;1 y ProcAgrupado;2, se puede correr a ambos ingresando EXEC ProcAgrupado. O, se puede ejecutar el procedimiento 2 escribiendo EXEC ProcAgrupado;2. Si se definen parámetros en los procedimientos almacenados agrupados, cada nombre de parámetro debe ser único en el grupo.
• Variables para mantener los parámetros definidos en el procedimiento almacenado. Las variables son definidas con el uso de la palabra clave DECLARE antes de usar EXECUTE.

Ejecutar procedimiento almacenado cuando arranca SQL Server

Ya sea por razones de performance, administración, o cumplimiento de tareas, se puede indicar que al arrancar el SQL Server se ejecuten procedimientos almacenados. Se utiliza para ello el procedimiento almacenado sp_procoption. Este procedimiento acepta tres parámetros: @ProcName, @OptionName y @OptionValue. El comando siguiente configura un procedimiento almacenado llamado AutoStart para que arrnque automáticamente:

USE Master

GO

EXECUTE sp_procoption

@procname = autostart,

@optionname = startup,

@optionvlue = true

Sólo los procedimientos de dbo y ubicados en la base master pueden ser configurados para ejecutarse automáticamente. Para ejecutar al arrancarse el SQL Server a procedimientos ubicados en otras bases deberán ser llamados desde un procedimiento almacenado ubicado en la Master y configurado para arrancar automáticamente. Llamar a un procedimiento desde otro se denomina llamadas anidadas.

Se puede configurar a un procedimiento para que arranque automáticamente desde el Enterprise Manager. Se accede a la base Master, se hace clic en el nodo Stored Procedures. Se selecciona al procedimiento almacenado, y luego clic derecho. Se abre el cuadro de diálogo Properties (Propiedades) y se marca la casilla de selección Execute Whenever SQL Server Start (ejecutar siempre que el SQL Server arranca).

Para determinar si un procedimiento almacenado arranca automáticamente, se puede ejecutar la función OBJECTPROPERTY y controlar la propiedad ExecIsStartup. Por ejemplo el código siguiente invoca a la función para determinar si el procedimiento AutoStart está configurado para ejecutarse automáticamente:

USE Master

SELECT OBJECTPROPERTY(object_id(‘autostart’), ‘ExecIsStartup’)

Para deshabilitar procedimientos para que no continúen ejecutándose automáticamente, se puede correr el procedimiento almacenado sp_configure. El siguiente comando configura al SQL Server para que los procedimientos almacenados configurados para ejecutarse automáticamente no se arranquen la próxima vez que se encienda el SQL Server:

EXECUTE sp_configure

@configname = ‘scan for startup procs’, @configvalue = 0

RECONFIGURE

GO

Modificar procedimientos almacenados

Se puede usar el comando ALTER PROCEDURE (ALTER PROC en su versión abreviada),para modificar el contenido de un procedimiento almacenado de usuario, utilizando el Query Analyzer o una herramienta de comandos como osql. La sintaxis del comando ALTER PROCEDURE es casi idéntica a la del CREATE PROCEDURE.

La ventaje de usar el comando ALTER PROCEDURE  en vez de eliminar el procedimiento y crearlo de nuevo es que con este comando se retiene la mayoría de la propiedades configuradas para el procedimiento, tales como su ID. El conjunto de permisos, y su bandera de arranque. Para mantener la encriptación o la configuración de recompilación, se debe especificar con las palabras claves WITH ENCRYPTION o WITH RECOMPILE cuando se ejecuta ALTER PROCEDURE.

Se puede usar el Enterprise Manager o el Query Analyzer para modificarprocedimientos almacenados de usuario. En el Enterprise Manager, clic derecho sobre el procedimiento almacenado y luego clic en Properties. En el cuadro de diálogo Stored Procedure Properties, modifique los comandos del procedimiento que aparecen en la caja Text, y luego clic en OK. Usando el Query Analyzer, clic derecho en el procedimiento almacenado y clic en Edit. Después de modificar un procedimiento almacenado ejecútelo.

Para modificar el nombre de un procedimiento almacenado definido por el usuario, se usa el procedimiento almacenado sp_rename. El comando siguiente renombra el procedimiento almacenado ByRoyalty a RoyaltyByAuthorID:

USE PUBS

GO

EXECUTE sp_rename

@objname = ‘byroyalty’, @newname = ‘RoyaltyByAutorID’,

@objtype = ‘object’

Procedimientos almacenados definidos por el usuario pueden ser renombrados en el Enterprise Manager haciendo clic derecho en el nombre del procedimiento almacenado y clic en Rename (renombrar)

Se deberá tener cuidado cuando se renombre un procedimiento almacenado u otros objetos, como tablas. Los procedimientos almacenados pueden ser anidados. Si se produce una llamada a un objeto renombrado, el procedimiento que llama no podrá ubicar al objeto renombrado y generará un mensaje de error.

Eliminar procedimientos almacenados

Se puede usar el comando DROP PROCEDURE, o su versión abreviada DROP PROC, para eliminar un procedimiento almacenado definido por el usuario, varios procedimientos a la vez o un conjunto de procedimientos agrupados. El comando siguiente borra dos procedimientos en la base de datos Pubs: Procedure01 y Procedure02:

USE Pubs

GO

DROP PROCEDURE procedure01, procedure02

Fíjese que primero se configuró a Pubs como la base actual. No se puede especificar un nombre de base de datos cuando se elimina un procedimiento. El nombre totalmente calificado para eliminar procedimientos es [propietario].[nombre_procedimiento]. Si se ha creado un procedimiento almacenado del sistema definido por el usuario (con prefijo sp_), el comando DROP PROC buscará primero en la base actual y luego en la Master.

Para borrar un grupo de procedimientos almacenados, especifique el nombre del procedimiento. No se puede borrar parte de un grupo de procedimientos almacenados con el comando DROP PROCEDURE. Por ejemplo si se tiene un grupo de procedimientos llamados ProcAgrupados conteniendo ProcAgrupados;1 y ProcAgrupados;2 no se puede borrar ProcAgrupados;1 sin borrar ProcAgrupados;2. Si se necesita eliminar un procedimiento dentro de un grupo de procedimientos, se elimina el grupo y luego se lo recrea sin el procedimiento a eliminar.

Los cuidados indicados al final del punto anterior también son válidos para las operaciones de eliminación.