TEMA 17: Programar procedimientos almacenados

Parámetros y variables

Los parámetros y las variables son una parte fundamental de hacer dinámico a un procedimiento almacenado. Los parámetros de entrada habilitan al usuario que está ejecutando el procedimiento a pasar valores al procedimiento almacenado. Los parámetros de salida amplían la salida de los procedimientos almacenados mas allá del conjunto de resultados retornado por una consulta. Los datos desde un parámetro de salida son capturados en memoria cuando el procedimiento se ejecuta. Para retornar un valor desde el parámetro de salida se debe crear una variable para que lo mantenga. Se puede mostrar el valor con los comando SELECT o PRINT, o usar el valor para completar otro comando en el procedimiento.

En resumen, un parámetro de entrada se define en el procedimiento almacenado y un valor es provisto cuando el procedimiento se ejecuta. Un parámetro de salida se define usando la palabra clave OUTPUT. Cuando se ejecuta el procedimiento, se guarda en memoria un valor para el parámetro de salida. Para utilizarlo, se declara una variable para mantener al valor. Los valores de salida típica de muestran en pantalla una vez que terminó la ejecución del procedimiento.

El siguiente procedimiento muestra el uso de ambos parámetros, de salida y de entrada:

USE Pubs

GO

CREATE PROCEDURE dbo.SalesForTitle

   @Title.varchar(80), --Parámetro de entrada

   @YtdSales int OUTPUT, --Primer parámetro de salida

   @TitleText varchar(80) OUTPUT –Segundo parámetro de salida

AS

-- Asigna la columna de datos a los parmetros de salida y

-- controla por un título que coincida con el parámetro de entrada

SELECT @YtdSales = ytd_sales, @TitleText = title

FROM titles WHERE title LIKE @Title

GO

El parámetro de entrada es @Title (título), y los de salida son @YtdSales y @TitleText. Fíjese que los tres parámetros tienen definidos sus tipos de datos. Los parámetros de salida incluye la palabra clave OUTPUT. Después que se definen los parámetros, el comando SELECT utiliza a los tres parámetros. Primero, a los parámetros de salida se les asigna a las columnas de la consulta. Cuando se ejecuta la consulta, los parámetros de salida contendrán los valores de esas dos columnas. La cláusula WHERE del comando SELECT contiene el parámetro de entrada, @Title. Cuando se ejecuta el procedimiento, se debe proveer un valor para el parámetro de entrada o fallará la consulta.

El siguiente comando ejecuta el procedimiento almacenado que vimos:

-- Declara variables para recibir las salidas del procedimiento

DECLARE @y_YtdSales int, @t_TitleText varchar(80)

EXECUTE SalesForTitle

-- configura los valores de variables con los parámetros de salida

@YtdSales = @y_YtdSales OUTPUT,

@Titletext = @t_TitleText OUTPUT,

@Title = “%Garlic%” – especifica un valor para el parámetro de entrada

-- Muestra las variables retornadas por el procedimiento

SELECT “Title” = @t_TitleText, “Número de Ventas” = @y_YtdSales

GO

Se declaran dos variables: @y_YtdSales y @t_TitleText. Estas dos variables recibirán los valores almacenados en los parámetros de salida. Advierta que el tipo de datos de la variables que reciben son los mismos que los tipos de los correspondientes parámetros de salida. Estas dos variables pueden tener el mismo nombre que los parámetros de salida correspondientes dado que las variables en un procedimiento almacenado son locales al batch que las contiene. Por claridad, los nombres de las variables son diferentes a los de los parámetros de salida. Cuando se declara una variable, esta no se asigna con un parámetro de salida. Las variables son asignadas con parámetros de salida después del comando EXECUTE. Fíjese que la palabra OUTPUT se indica cuando el parámetro se asigna a la variable. Si no se especifica OUTPUT, la variable no puede mostrar los valores en el comando SSELECT al final del código. Por ‘ultimo, el parámetro de entrada @Title es igualado a %Garlic%. Este valor se envía a la cláusula WHERE del comando SELECT del procedimiento almacenado. Dado que la cláusula WHERE usa la palabra clave KEYWORD, se pueden usar caracteres comodines (tal como %) para que que la consulta busque aquellos títulos que contienen la palabra Garlic.

A continuación se muestra un modo mas sucinto  de ejecutar el procedimiento. Fíjese que no es necesario asignar específicamente las variables desde el procedimiento almacenado al valor del parámetro de salida o a las variables de salida declaradas aquí:

DECLARE @y_YtdSales int, @t_TitleText varchar(80)

EXECUTE SalesForTitle

“%Garlic%”, -- configura el valor del parámetro de entrada

@y_YtdSales OUTPUT -- recibe el primer parámetro de salida

@t_TitleText OUTPUT -- recibe el segundo parámetro de salida

-- muestra las variables retornadas por la ejecución del procedimiento

SELECT “Title” = @t_TitleText, “Número de ventas” = @y_YtdSales

GO

Cuando se ejecuta el procedimiento, este retorna lo siguiente:

Title	Number Return of Sales
NULL	NULL

Un interesante resultado de este procedimiento es que se retorna sólo una fila. Aún cuando el comando SELECT en el procedimiento devuelve múltiples filas, cada variable mantiene sólo un valor (la última fila de datos retornada). Mas adelante veremos algunas soluciones a este problema.

El comando RETURN y el manejo de errores

A menudo, se necesita codificar para manejar errores. SQL Server provee funciones y comandos para tratar con los errores que se producen durante la ejecución de un procedimiento almacenado. Las dos categorías primarias de errores de computo, tales como una base no disponible o errores de usuarios. Se utilizan códigos de retorno y la función @@ERROR para manejar errores que se producen cuando se ejecuta el procedimiento.

Los códigos de retorno pueden usarse para otros propósitos además de manejar errores. El comando RETURN se usa para generar códigos y salidas de un batch, y puede proveer d cualquier valor entero al programa que llamador. Veremos ejemplos de códigos que usan los códigos de retorno para proveer un valor tanto como para manejar errores o para otros fines.

El comando RETURN se usa primariamente para el manejo de errores porque cuando se corre el comando RETURN, el procedimiento almacenado termina inmediatamente.

Considere el procedimiento almacenado SalesForTitle del punto anterior. Si el valor especificado para el valor de entrada (@Title) no existe en la base ded atos, ejecutar el procedimiento retorna el siguiente conjunto de resultados:

Title	Number Return of Sales	Code
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean	375	0

Es mas claro explicar el usuario que no se han encontrado registroa. El ejemplo siguiente muestra como modificar el procedimiento almacenado SalesForTitle para usar un código de retorno (y proveer un mensaje mas útil):

ALTER PROCEDURE dbo.SalesForTitle

@Title varchar(80), @YtdSales int OUTPUT,

@titletext varchar (80) OUTPUT

AS

-- Controla para ver si el titulo esta la base. Sino, sale y devuelve 1

IF (SELECT COUNT(*) FROM titles WHERE title LIKE @Title) = 0

    RETURN(1)

ELSE

SELECT @YtdSales = ytd_sales, @TitleText = title

FROM titles WHERE title LIKE @Title

GO

La sentencia IF posterior la palabra clave AS determina si el parámetro de entrada es provisto cuando el procedimiento se ejecuta y si coincide con algún registro de la base de datos. Si la función COUNT retorna 0, entonces el código de retorno es puesto en 1, RETURN(1). En otro caso, se ejecuta la consulta SELECT por las ventas anuales y la información del libro. En este caso el código de retorno es igual a 0.

Para invocar el procedimiento se necesitan hacer algunos cambios. El ejemplo siguiente configura al parámetro de entrada @Title igual a Garlic%:

--Agrega @r_Code para mantener el resultado

DECLARE @y_YtdSales int, @t_TitleText varchar(80), @r_Code int

-- Ejecuta el procedimeinto e iguala @r_Code al el procedimiento

EXECUTE @r_Code = SalesForTitle

@YtdSales = @y_YtdSales OUTPUT,

@TitleText = @t_TitleText OUTPUT,

@Title = "Garlic%"

-- Determina el valor de @r_Code y ejecuta el codigo

IF @r_Code = 0

SELECT "Title" = @t_TitleText,

"Number of Sales" = @y_YtdSales, "Return Code" = @r_Code

ELSE IF @r_Code = 1

PRINT 'No matching titles in the database. Return code=' + CONVERT(varchar(1),@r_Code)

GO

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.

TEMA 15: Introducción a los procedimientos almacenados

Hasta ahora hemos utilizado el Query Analizer (o la herramienta on-line) para ejecutar comandos o grupos de comandos en el lenguaje Transact SQL. A los grupos de comandos los llamaremos scripts (guiones).

Cuando se ejecuta un script, los comandos son ejecutados por SQL Server para mostrar un conjunto de resultados, configurar un comportamiento y administrar al servidor o para manipular datos contenidos en una base de datos.

A estos scripts se les puede dar un nombre y guardar como procedimientos almacenados en el SQL Server. Posteriormente se los puede invocar de diversas maneras, tal como desde el Query Analizer, para que realicen el procesamiento de los comandos Transact SQL incorporados.

El SQL Server provee una serie de procedimientos almacenados del sistema, algunos de los cuales ya hemos utilizado en este Kit. Estos procedimientos son guardados en la base de datos Master y contienen comando Transact SQL que facilitan muchas tareas.

Propósito y ventajas de los procedimientos almacenados

Los procedimientos almacenados proporcionan ventajas de performance, un marco de trabajo, y mayores capacidades de seguridad. La mejora en el rendimiento se logra a través de un almacenamiento local (en la base de datos), código precompilado, y manejo de cachés  (almacenamientos temporarios). El marco de programación se logra a través de construcciones comunes de programación tales como parámetros de entrada/salida y reutilización de los procedimientos. Las capacidades de seguridad incluye encriptación y limitaciones de privilegios que permiten mantener a los usuarios fuera de la vista de la estructura de la base de datos subyacente, mientras se los habilita a ejecutar procedimientos almacenados  que actúan sobre la base de datos.

Rendimiento

Cada vez que un comando Transact-SQL, o conjunto de comandos, es enviado el servidor para su procesamiento, el servidor debe determinar si el remitente tiene suficientes privilegios para ejecutar esos comandos y si los comandos son válidos. Una vez que los permisos y la sintaxis de los comandos se han verificado, SQL Server construye un plan de ejecución para procesar el pedido.

Los procedimientos almacenados son más eficientes en parte porque el procedimiento es almacenado en el SQL Server cuando se crea. La sintaxis de los comandos contenidos en un procedimiento almacenado se comprueba que este libre de errores antes de ser guardado. El nombre del procedimiento almacenado se almacena en la tabla SysObjects, mientras que el texto del procedimiento se guarda en la tabla SysComments. Por otro lado, invocar al procedimiento almacenado implica ejecutar un solo comando en vez de cientos de comandos que un procedimiento almacenado podría contener.

La primera vez que se ejecuta el procedimiento, se crea un plan de ejecución y se compila al procedimiento almacenado. Los procesamientos subsecuentes del procedimiento almacenado son mucho más rápidos ya que el SQL Server no vuelve a controlar la sintaxis, ni recrea un plan de ejecución, ni se recompila el procedimiento. Por último se verifica el caché por si ya existe un plan de ejecución para ese procedimiento antes de generar un nuevo plan de ejecución.

La relativa pérdida de rendimiento producida por ubicar los planes de ejecución de los procedimientos almacenados en el caché de procedimiento se reduce ya que los planes de ejecución para todos los comandos SQL se guardan ahora en el caché de procedimientos. Por lo que un comando Transact-SQL tratará de utilizar un plan de ejecución ya existente en todos casos posibles.

Marco de programación

Una vez que se crea un procedimiento almacenado, puede ser llamado todas las veces que sea necesario. Esta capacidad provee modulación y habilita la reutilización del código. La reutilización del código mejora el mantenimiento de la base de datos al aislar la base de datos de los cambios en las prácticas del negocio. Si las reglas de negocios cambian en una organización, se puede modificar a los procedimientos almacenados para cumplir con las nuevas reglas de negocio. Todas las aplicaciones que llaman a esos procedimientos almacenados cumplirán con la nuevas reglas, sin tener que ser directamente modificados.

Tal y como otros lenguajes de programación, los procedimientos almacenados pueden aceptar parámetros de ingreso, retornar parámetros de salida, producir información de retroalimentación de la ejecución en la forma de códigos de estatus y texto descriptivo, y llamar a otros procedimientos. Por ejemplo, un procedimiento almacenado puede retornar un código de estatus a un procedimiento que lo llamó para que este procedimiento realice una operación según el código recibido.

Los desarrolladores de software pueden escribir sofisticados en un lenguaje como el C++; luego, se puede utilizar un tipo especial de procedimiento almacenado, denominado procedimiento almacenado extendido, para invocar al programa desde dentro del SQL Server.

Ante cualquier tarea simple, se debería escribir un procedimiento almacenado. Mientras más genérico sea el procedimiento más útil será para muchas bases de datos. Por ejemplo; el procedimiento almacenado sp_rename cambia el nombre de un objeto creado por el usuario, tal como una tabla, una columna o un tipo de datos definido por el usuario en la base de datos actual, pudiéndose aplicar a cualquier base de datos.

Seguridad

Otro capacidad importante de los procedimientos almacenados es que mejoran la seguridad a través de la encriptación y el aislamiento. Los usuarios de las bases de datos pueden tener permisos de ejecutar un procedimiento almacenado sin tenerlos para acceder directamente a los objetos de la bases de datos sobre las que opera el procedimiento almacenado. Además un procedimiento almacenado puede ser encriptado cuando se lo crea o modifica inhabilitando a los usuarios a leer los comandos Transact-SQL contenidos en el procedimiento almacenado. Estas capacidad de seguridad permiten aislar la estructura de la base de datos del usuario de la base de datos, con la consiguiente ganancia en seguridad.

Categorías de procedimientos almacenados

Existen cinco categorías de procedimientos almacenados: procedimientos almacenados del sistema, procedimientos almacenados locales, procedimientos almacenados temporarios, procedimientos almacenados extendidos y procedimientos almacenados remotos.

Procedimientos almacenados del sistema

Los procedimientos almacenados del sistema son guardados en la base de datos Master y son típicamente identificados por el prefijo sp_ . Ellos realizan una amplia variedad de tareas para soportar las funciones del SQL Server soportando: llamadas de aplicaciones externas para datos de las tablas del sistema, procedimientos generales para administración de las bases de datos, y funciones de administración de seguridad. Por ejemplo, se pueden ver los privilegios de una tabla usando el procedimiento almacenado de catálogo sp_table_privileges. El comando siguiente utiliza este procedimiento almacenado para mostrar los privilegios de la tabla stores en la base de datos Pubs:

USE PubsGO
EXECUTE sp_table_privileges Stores

Una tarea común de administración de bases de datos  es consultar la información acerca de los usuarios y procesos que se están ejecutando en una base de datos. Este paso es importante cuando se debe dar de baja la base de datos. El siguiente comando usa el procedimiento sp_who para mostrar todos los procesos en uso por el usuario LAB1\Administrator:

EXECUTE sp_who @loginame=’LAB1\administrator’

La seguridad de base de datos es crítica para la mayoría de las organizaciones que almacenan datos privados en su base de datos. El comando siguiente usa el procedimiento almacenado del sistema sp_validatelogins para mostrar cualquier usuario y grupo de usuarios Windows NT o Windows 2000 huérfanos que no existan más y que aún tengan entradas en las tablas del sistema del SQL Server:

EXECUTE sp_validatelogins

Hay cientos de procedimientos almacenados del sistema incluidos con el SQL Server. Para una lista completa de los procedimientos almacenados, consultar “System Store Procedures” en SQL Server Books Online.

Procedimientos almacenados locales

Los procedimientos almacenados locales son usualmente almacenados en una base de datos y están típicamente diseñados para completar tareas en la base de datos donde residen. Un procedimiento almacenado local se podría crear también para personalizar código de los procedimientos almacenados del sistema. Para crear una tarea personalizada basada sobre un procedimiento almacenado del sistema, primero copie el contenido del procedimiento almacenado del sistema y guarde el nuevo procedimiento almacenado y guarde el nuevo procedimiento almacenado como un procedimiento almacenado local.

Procedimientos almacenados temporarios

Un procedimiento almacenado temporario es similar a un procedimiento almacenado local, pero existe sólo hasta que se cierre la conexión que lo creó o se dé de baja el SQL Server, dependiendo del tipo de procedimiento almacenado. Estos procedimientos tienen una existencia volátil debido a que son creados son almacenados en la base de datos TempDB. TempDB se recrea cuando se reinicia el servidor; por lo tanto, todos los objetos dentro de la base de datos desaparecen después de la base de datos. Los procedimientos almacenados temporarios son muy útiles si Ud. está accediendo a versiones anteriores del SQL Server que no soportan la reutilización de los planes de ejecución y si no se quiere almacenar las tareas que serán ejecutadas con distintos parámetros.

Hay tres tipos de procedimiento almacenado temporarios: locales (también llamados privados), globales, y procedimientos almacenados en TempDB. Un procedimiento almacenado temporario local siempre comienza con #, un procedimiento almacenado temporario global siempre comienza con ##. Mas adelante se explicará como crear cada tipo de procedimiento almacenado temporario. El alcance de ejecución de un procedimiento almacenado local esta limitado a la conexión que lo creó. Todos los usuarios que tienen conexión a la base de datos, sin embargo, pueden ver el procedimiento almacenado en la ventana del Object Browser del Query Analizer. Dado que se encuentra limitado en su alcance, no existe la posibilidad de colisión de nombres con otras conexiones que crean procedimientos almacenados temporarios. Para asegurar unicidad, SQL Server  agrega al nombre de un procedimiento almacenado con una serie de caracteres _ y un número único de conexión. Los privilegios no pueden ser garantizado a otros usuarios para el procedimiento almacenado temporario. Cuando la conexión que creó el procedimiento almacenado temporario se cerró, el procediemiento se elimina de la TempDB.

Cualquier conexión para la base de datos puede ejecutar un procedimiento almacenado temporario global. Este tipo de procedimientos debe tener un nombre único , dado todas las conexiones pueden ejecutarlo y, cómo todos los procedimientos almacenados temporarios, este es creado en TempDB. El permiso para ejecutar procedimientos almacenados es automáticamente garantizado al rol público y no puede ser cambiado. Un procedimiento almacenado temporario global es casi tan volátil como los locales. Este tipo de procedimiento es removido cuando la conexión usada para crear el procedimiento se cierra y cualquier conexión que este ejecutando el procedimiento almacenado es completada.

Los procedimientos almacenados temporarios creados directamente en la TempDB son diferentes a los procedimientos almacenados locales y globales en lo siguiente:

·        Se pueden configurar permisos para ellos.

·        Existen aún después que la conexión que los creó se terminan

·        No son removidos hasta que el SQL Server  no sea apagado.

Ya que este tipo de procedimiento almacenado se crea directamente en la TempDB, es importante calificar completamente los objetos de base de datos referenciados por comandos Transact-SQL en el código. Por ejemplo, se debe referenciar la tabla Authors, la cual es propiedad de dbo en la base de datos Pubs, como pubs.dbo.authors.

Procedimientos almacenados extendidos

Un procedimiento almacenado extendido usa un programa externo, compilado como una DLL, librería de vínculos dinámicos (dynamic link library) para expandir las capacidades de un procedimiento almacenado. Un número de procedimiento almacenado del sistema son también calificados como procedimientos almacenados extendidos. Por ejemplo, el programa xp_sendmail, el cual envía un mensaje y un archivo de conjunto de resultados de una consulta adjunto a una dirección específica de email, es tanto un procedimiento almacenado de sistema como un procedimiento almacenado extendido. La mayoría de los procedimientos almacenados extendidos usan el prefijo xp_ como un convención de nombre. Sin embargo, hay algunos procedimientos almacenados extendidos que comienzan con el prefijo sp_, y hay algunos procedimientos almacenados del sistema que no son procedimientos extendidos y usan el prefijo xp_. Por lo tanto, no se puede depender sobre convención de nombres para identificar procedimientos almacenados del sistema y procedimientos almacenados extendidos.

Se puede usar la función OBJECTPROPERTY para determinar si un procedimiento almacenado es extendido o no. OBJECTPROPERTY retorna un valor de 1 si es un procedimiento extendido (IsExtendedProc), indicando un procedimiento almacenado extendido, o retorna un valor de 0, indicando que no es un procedimiento extendido. El siguiente ejemplo demuestra que el sp_prepare es un procedimiento almacenado extendido y que xp_logininfo no es un procedimiento almacenado extendido:

USE Master

·        un procedimiento almacenado extendido que usa el prefijo sp_ SELECT OBJECTPROPERTY(object_id(‘sp_prepare’), ‘IsExtendedProc’)

 Este ejemplo retorna un valor de 1

USE Master

·        un procedimiento almacenado que no es extendido y usa el prefijo xp_ SELECT OBJECTPROPERTY(object_id(‘xp_logininfo’), ‘IsExtendedProc’)

 Este ejemplo retorna un valor de 0.

Tema 14: Modificar datos en una Base de Datos SQL Server

Un componente esencial de un sistema de bases de datos es la capacidad para modificar los datos que se encuentran almacenados dentro del sistema. SQL Server soporta métodos para agregar filas de datos a las tablas de una base de datos SQL Server, cambiar los datos en las filas existentes, y eliminar filas.

Insertar datos en un base de datos SQL Server

SQL Server varios métodos para insertar datos a una base de datos:

• El comando INSERT
• El comando SELECT...INTO
• El comando WRITETEXT y varias opciones en la API (Application Programming Interface) de la base de datos, que se pueden usar para agregar texto e imágenes a una fila.

NOTA

Un comando INSERT trabaja tanto sobre tablas como sobre vistas (con algunas restricciones).

Usar el comando INSERT para agregar datos

El comando INSERT agrega una o más nuevas filas a una tabla. En un tratamiento simplificado, el comando INSERT toma la siguiente forma:

INSERT [INTO] tabla_o_vista [(lista_de_columnas)] valores_de_datos

Este comando hace que los valores de los datos (valores_de_datos) sean insertados como una o mas filas en la tabla o vista. La lista de los nombres de columnas (lista_de_columnas), separadas por comas, se usan para especificar las columnas que recibirán los datos. Si no se indican columnas, todas las columnas de la tabla o vista recibirán datos. Si solo se indica una lista parcial de columnas, el resto de las columnas recibirán un valor nulo o el valor configurado por defecto para esa columna, en caso que lo tenga.

Además, no se deben asignar valores a los siguientes tipos de columnas, dado que SQL Server genera automáticamente este valor.

• Columnas con la propiedad IDENTITY
• Columnas con una definición DEFAULT que use la función NEWID()
• Columnas computadas

NOTA

La palabra clave INTO en un comando INSERT es opcional y solo se utiliza para clarificar el código.

Los valores ingresados deben coincidir con la lista de columnas. La cantidad de valores provistos debe ser igual a la cantidad de columnas indicadas en la lista de columnas, y el tipo de dato, precisión, y escala de cada valor debe coincidir con los de las columnas correspondientes.

Cuando se define un comando INSERT, se puede usar la cláusula VALUES para especificar los valores de los datos para una fila o usar una subconsulta SELECT para especificar los valores para una o más columnas.

Usar el comando INSERT...VALUES para agregar datos

Una cláusula VALUES permite especificar los valores para una fila de la tabla. Los valores son indicados a través de una lista de expresiones escalares separadas por comas. Estos valores deben ser implícitamente convertibles al tipo, precisión y escala de las columnas correspondientes. Si no se especifica la lista de columnas, los datos deben ser ingresados en el mismo orden que tiene en la definición de la tabla o vista.

Por ejemplo, suponga que se crea la siguiente tabla en la base de datos Pubs:

USE Pubs

CREATE TABLE NuevosLibros

(

LibroID INT IDENTITY(1,1) NOT NULL,

LibroTitulo VARCHAR(80) NOT NULL,

LibroTipo CHAR(12) NOT NULL

CONSTRAINT [tipolibro_df] DEFAULT ('no informado'),

CiudadEd VARCHAR(50) NULL

)

Una vez creada la tabla, se decide ingresar datos a una fila de la tabla. EL siguiente comando INSERT utiliza la cláusula VALUES para insertar una nueva fila en la tabla NuevosLibros:

USE Pubs

INSERT INTO NuevosLibros (LibroTitulo, CiudadEd)

VALUES ('Life Without Fear', 'Chicago')

En este comando, los valores han sido definidos para la columnas LibroTitulo y CiudadEd. Sin embargo, no es necesario incluir la columna LibroID en el comando INSERT, dado que la columna LibroID se define con la propiedad IDENTITY, porque los valores para esa columna se generan automáticamente. Además, al no ingresarse un valor para la columna TipoLibro, SQL Server automáticamente inserta el valor por defecto (no informado) en la columna al ejecutar el comando.

Usar una subconsulta SELECT para agregar datos

Se puede usar una subconsulta SELECT dentro de un comando INSERT para agregar datos a una tabla desde otra u otras tablas o vistas. Una subconsulta permite agregar más de una fila a la vez.

NOTA: Una subconsulta SELECT en un comando INSERT se utiliza para agregar subconjuntos de datos existentes a una tabla, mientras que la cláusula VALUES se usa para guardar datos nuevos en una tabla.

El siguiente comando INSERT usa una subconsulta SELECT para insertar filas en la tabla NuevosLibros:

USE Pubs

INSERT INTO NuevosLibros (LibroTitulo, LibroTipo)

SELECT Titulo, Tipo

FROM Titulos

WHERE Tipo = 'mod_cook'

Este comando INSERT usa la salida de una subconsulta SELECT para proveer los datos que se usarán para ser insertados en la tabla NuevosLibros.

Usar el comando SELECT...INTO para agregar datos

EL comando SELECT INTO permite crear una nueva tabla y llenarla con el resultado del comando SELECT. Ver Tema 2 de este módulo

Agregar datos ntext, text, o imagen a filas ya insertadas

SQL Server incluye varios métodos para agregar valores ntext, text, o imagen a un fila:

• Se pueden especificar relativamente pequeños montos de datos en un comando INSERT de la misma forma que se especifican datos char, nchar, o binary.
• Utilizar el comando WRITETEXT, que permite actualización interactiva, y no registrada, de una columna existente tipo text, ntext, o image. Este comanado sobrescribe completamente cualquier dato preexistente en la columna que afecta. Un comando WRITETEXT no puede usarse sobre columnas de una vista.
• Las aplicaciones ADO (Active Data Object) pueden usar el método AppendChunk para especificar grandes cantidades de datos tipo ntext, text, o image.
• Las aplicaciones OLE DB, por su parte, utilizan la interfase ISequentialStream.
• Las aplicaciones ODBC (Open Database Connectivity) usan el SQLPutData para ingresar nuevos valores del tipo ntext, text, o image.
• Por último, las aplicaciones DB-Library usan la función Dbwritetext.

Modificar datos en una base de datos SQL Server

Una vez que se crean las tablas y que se ingresan datos, cambiar y actualizar los datos se convierte en una tarea de mantenimiento diario SQL Server provee varios métodos para cambiar datos en una tabla existente:

• El comando UPDATE
• La API de la base de datos y los cursores
• El comando UPDATETEXT

Estos procedimientos se pueden tanto a tablas como a vistas (con algunas restricciones)

Usar el comando UPDATE para modificar datos

El comando UPDATE puede cambiar datos en una columna simple, en grupos de columnas, o en todas las columnas de una tabla o vista. Este comando se puede usar también para actualizar filas en un servidor remoto, ya sea usando el nombre de un servidor conectado o las funciones OPENROWSET, OPENDATASOURCE, y OPENQUERY (siempre y cuando el proveedor OLE DB usada para acceder al servidor remoto soporte actualizaciones). Un comando UPDATE que referencia a una tabla o una vista puede cambiar los datos en sólo un tabla base por vez.

NOTAUna actualización será exitosa sólo si el nuevo valor es compatible con el tipo de datos de la columna y cumple con todas las restricciones asociadas a esta.

Las cláusulas más importantes del comando UPDATE son:

• SET
• WHERE
• FROM

Usar la cláusula SET para modificar datos.

SET indica que columna será actualizada y los nuevos valores que se guardarán. Los valores en las columnas indicadas serán actualizados con los valores provistos en la cláusula SET en todas las filas que cumplan con la condición de búsqueda especificada en la cláusula WHERE. Si no se especifica la cláusula WHERE, todas las columnas serán actualizadas.

Por ejemplo, el siguiente comando UPDATE incluye la cláusula SET que incrementa el precio de los libros en la tabla NuevosLibros un 10 por ciento:

USE Pubs

UPDATE NuevosLibros

SET Precio = Precio * 1.1

En este comando, no se usa la cláusula WHERE, por lo que todas las filas serán modificadas (salvo aquellas que tengan un valor nulo para la columna Precio).

Usar la cláusula WHERE para modificar datos

La cláusula WHERE realiza dos funciones:

• Indica las filas a actualizar
• Indica las filas de la tabla fuente que califican para proveer valores de actualización cuando se especifica la cláusula FROM.

Si no se indica la cláusula WHERE, todas las filas serán actualizadas.

En el siguiente comando UPDATE, la cláusula WHERE se utiliza para limitar la actualización a sólo aquellas filas que cumplen con la condición definida en la cláusula:

USE Pubs

UPDATE NuevosLibros

SET LibroTipo = 'popular'

WHERE LibroTipo = 'popular_comp'

Este comando cambia el nombre del tipo de libro de aquellas filas con el valor popular_comp por el de popular. Si no se hubiese puesto la cláusula WHERE todos los valores de LibroTipo tomarían el valor popular.

Usar una cláusula FROM para modificar los datos

Se puede usar la cláusula FROM para traer datos desde una o más tablas o vistas a la tabal que se quiere actualizar. Por ejemplo, en el siguiente comando UPDATE, la cláusula FROM incluye un inner join que combina los títulos en las tablas NuevosLibros y Titulos:

USE Pubs

UPDATE NuevosLibros

SET Precio = Titulos.Precio

FROM NuevosLibros JOIN Titulos

ON NuevosLibros.LibroTitulo = Titulos.Titulo

En este comando, los valores de Precio en la tabla LibrosNuevos se actualizan al mismo valor que la columna Precio de la tabla Titulos.

Usar APIs y cursores para modificar datos

Las APIs ADO, OLE DB, y ODBC soportan actualizar los datos de la fila actual sobre la que la aplicación se encuentra posicionada en un conjunto de resultados. Además, cuando se usa un cursor del servidor Transact-SQL, se puede actualizar la fila actual utilizando el comando UPDATE con la cláusula WHERE CURRENT OF. Los cambios realizados con esta cláusula sólo afectarán a la fila donde se encuentre posicionado el cursor. Este punto se verá más en detalle en otro módulo del Kit.

Modificar datos ntext, text, o image

SQL Server provee varios métodos para modificar valores del tipo ntext, text, o image en una fila cuando se reemplazan los valores completos:

• Se pueden especificar relativamente pequeños montos de datos en un comando UPDATE de la misma forma que se modifican datos char, nchar, o binary.
• Utilizar los comandos WRITETEXT o UPDATETEXT, para modificar datos ntext, text, o image.
• Las aplicaciones ADO (Active Data Object) pueden usar el método AppendChunk para especificar grandes cantidades de datos tipo ntext, text, o image.
• Las aplicaciones OLE DB, por su parte, utilizan la interfase ISequentialStream.
• Las aplicaciones ODBC (Open Database Connectivity) usan el SQLPutData para ingresar nuevos valores del tipo ntext, text, o image.
• Por último, las aplicaciones DB-Library usan la función Dbwritetext.

SQL Server soporta, además, actualizaciones de sólo una porción de datos del tipo ntext, text, o image. En DB-Library, se puede hacer usando la función Dbupdatetext. Todas las otras aplicaciones y los scripts Transact-SQL, batches, procedimientos almacenados y disparadores pueden usar el comando UPDATETEXT para actualizar sólo una porción de columnas ntext, text, o image.

Eliminar datos de una base de datos SQL Server

SQL Server soporta varios métodos para eliminar datos de una tabla:

• El comando DELETE
• APIs y cursores
• El comando TRUNCATE TABLE

Usar el comando DELETE para eliminar datos

Un comando DELETE remueve una o más filas en una tabla o vista. La sintaxis siguiente es una forma simplificada del comando DELETE:

DELETE tabla_o_vista FROM tabla_fuente WHERE condicion_de_busqueda

En tabla_o_vista se colocan los nombre de la tabla o vista en que serán eliminadas las filas. Serán eliminadas todas las filas que cumplan con la condición de búsqueda especificada en la cláusula WHERE. Si esta no se indica, se eliminarán todas las filas de la tabla o vista. La cláusula FROM indica tablas, vistas o combinaciones adicionales que se pueden usar en el predicado de condición de la cláusula WHERE. No se borran las filas de las tablas indicadas en la cláusula FROM, sino sólo de la especificada en la cláusula DELETE.

Si a una tabla se le eliminan todas tablas las misma permanece en la base de datos de todos modos. Para eliminar la tabla de la base de datos se debe usar el comando DROP TABLE.

Considere el comando DELETE siguiente:

USE Pubs

DELETE NuevosLibros

FROM Titulos

WHERE NuevosLibros.LibroTitulo = Titulos.Titulo

AND Titulos.Royalty = 10

En este comando, se borrarán aquellas filas de la tabla NuevosLibros con un royalty del 10 por ciento. Este valor de royalty se toma de la columna royalty de la tabla Titulos.

Usar APIs y Cursores para eliminar datos

Las APIs ADO, OLE DB, y ODBC soportan eliminación de la fila actual sobre la cual se encuentra posicionada la aplicación en el conjunto de resultados. Además, cuando se usa un cursor del servidor Transact-SQL, se puede eliminar la fila actual utilizando el comando UPDATE con la cláusula WHERE CURRENT OF. Los cambios realizados con esta cláusula sólo afectarán a la fila donde se encuentre posicionado el cursor. Este punto se verá más en detalle en otro módulo del Kit.

Usar el comando TRUNCATE TABLE para eliminar datos

El comando TRUNCATE TABLE es una forma rápida y no registrada de eliminar todas las filas en una tabla. Este método es casi siempre más rápido que un comando DELETE sin condiciones, porque DELETE graba un registro de transacciones por cada fila eliminada, mientras que TRUNCATE TABLE solo graba registro de liberación de las páginas de datos completas. El comando TRUNCATE TABLE inmediatamente libera el espacio ocupado por los datos de la tabla e índices. También se liberan las páginas de distribución de todos los índices.

El comando TRUNCATE TABLE siguiente elimina todas las filas de la tabla NuevosLibros en la base de datos Pubs:

USE Pubs

TRUNCATE TABLE NuevosLibroswBooks

Como en el caso del comando DELETE, la definición de la tabla se mantiene en la base de datos después que se aplica un comando TRUNCATE TABLE (incluidos sus índices y objetos asociados). Para eliminar la definición de la tabla se debe usar el comando DROP TABLE.