T-SQL

T-SQL PERSONALIZADO

Transact - SQL    

Hand On LAB de Sql Server Managment Studio Introducción a Transact SQL Programación con Transact SQL Fundamentos de Transact SQL o o

Primeros pasos con Transact SQL Scripts y lotes.

 Tipos de datos en Transact SQL o o o o o o o

Tipos de datos numéricos. Tipos de datos de caracter. Tipos de datos de fecha. Tipos de datos binarios. Tipo de datos XML. Otros tipos de datos. Tipos de datos personalizados.

 Variables en Transact SQL o o

Declarar variables es Transact SQL Asignar variables en Transact SQL

 Equivalencia de datos de SQL Server y .NET  Operadores en Transact SQL  Estructuras de control en Transact SQL o o o o

Estructura IF Estructura CASE Bucle WHILE Estructura GOTO

 Control de errores en Transact SQL o o o o

Uso de TRY CATCH Funciones especiales de Error La variable de sistema @@ERROR Generar un error con RAISERROR

 Consultar datos en Transact SQL o o o

La sentencia SELECT La cláusula WHERE La cláusula ORDER BY

 Consultas agregadas o o o o o o o

La cláusula GROUP BY La cláusula HAVING AVG Count Max, Min Sum Uso de Select TOP con consultas agregadas

 Select FOR XML o o

Clausula FOR XML. Campos y variables XML.

 Operaciones con conjuntos.

o o o

UNION EXCEPT INTERSECT

 Insertar datos en Transact SQL o o o o

Inserción individual de filas. Insertción múltiple de filas. Inserción de valores por defecto. Clausula OUTPUT

 Actualizar datos en Transact SQL o o o

Update Update INNER JOIN Clausula OUTPUT

 Borrar datos en Transact SQL o o o

Delete Clausula OUTPUT Truncate Table

 Transacciones en Transact SQL o o o o

Concepto de transaccion Transacciones implicitas y explicitas Transacciones anidadas. Puntos de recuperacion

 Procedimientos almacenados en Transact SQL  Funciones en Transact SQL o o o

Funciones escalares Funciones en linea Funciones en línea de multiples sentencias

 Funciones integradas de Transact SQL (I) o o o o

Cast y Convert Isnull COALESCE GetDate y GetUTCDate

 Triggers en Transact SQL o o

Trigger DML Trigger DDL

 Cursores en Transact SQL  SQL dinámico en Transact SQL o

La instrucción comando EXECUTE

 El procedimiento almacenado sp_executesql

Hands on LAB de SQL Server Managment Studio 2005 

Inicar SSMS (Sql Server Managment Studio)



Inicio de session



Interface Gráfica de SQL Server Management Studio

1. Seleccionar la Base de Datos 2. 3. 4. 5. 6. 7.

Abrir el Servidor conectado Desplegar la base de Datos NORTHWIND Desplegar los Objetos Ejm. Tablas Desplegar las características de Tabla (COLUMNAS) Escribir el Script (“Conjunto de Ordenes T-SQL”) Ventana de resultado de la consulta

Modelo de Datos de la Base de Datos NorthWind

Orders = Cabecera de Ordenes Orders Details = Detalle de Ordenes Customers = Clientes Employees = Empleados Products = Productos Categories = Categorias Observar las relaciones que apunta en dirección de (PK=Primary Key) y (FK=Foreign Key) esta son de gran utilidad para nuestro caso, ya que en el cursos empleamos las uniones JOIN no servirán como referencia

Ejercicios corridos con las estructuras de la base de datos northwind SELECT O.ORDERID, O.ORDERDATE, O.FREIGHT, C.COMPANYNAME, C.CONTACTNAME, E.LASTNAME+' '+E.FIRSTNAME AS EMPLEADO, P.PRODUCTNAME, X.CATEGORYNAME, OD.QUANTITY, OD.UNITPRICE, (OD.QUANTITY * OD.UNITPRICE) AS TOTAL FROM ORDERS O INNER JOIN [ORDER DETAILS] OD ON O.ORDERID=OD.ORDERID INNER JOIN CUSTOMERS C ON O.CUSTOMERID=C.CUSTOMERID INNER JOIN EMPLOYEES E ON O.EMPLOYEEID=E.EMPLOYEEID INNER JOIN PRODUCTS P ON OD.PRODUCTID=P.PRODUCTID INNER JOIN CATEGORIES X ON P.CATEGORYID=X.CATEGORYID

Vista de los resultados

DECLARE @TABLA TABLE(CODIGO VARCHAR(5),EMPRESA VARCHAR(50),MES INT, MONTO MONEY) INSERT INTO @TABLA SELECT C.CUSTOMERID AS CODIGO, C.COMPANYNAME AS EMPRESA, MONTH(ORDERDATE) AS MES, SUM(O.FREIGHT) AS MONTO FROM ORDERS O INNER JOIN CUSTOMERS C ON O.CUSTOMERID = C.CUSTOMERID WHERE YEAR(ORDERDATE)=1997 GROUP BY C.CUSTOMERID,C.COMPANYNAME,MONTH(ORDERDATE); WITH RES AS ( SELECT * FROM @TABLA PIVOT(SUM(MONTO) FOR MES IN( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS PVT) SELECT CODIGO,EMPRESA, ISNULL([1],0.0) AS [ENE], ISNULL([2],0.0) AS [FEB], ISNULL([3],0.0) AS [MAR], ISNULL([4],0.0) AS [ABR], ISNULL([5],0.0) AS [MAY], ISNULL([6],0.0) AS [JUN], ISNULL([7],0.0) AS [JUL], ISNULL([8],0.0) AS [AGO], ISNULL([9],0.0) AS [SET], ISNULL([10],0.0) AS [OCT], ISNULL([11],0.0) AS [NOV], ISNULL([12],0.0) AS [DIC] FROM RES ORDER BY EMPRESA

Transact SQL 2005 Este documento describe las nuevas características de T-SQL para SQL Server 2005.

Introducción Microsoft Sql Server 2005 (Code Name = YUKON) es un producto con muchas novedades comparándolo con su antecesor SQL 2000. Sin duda que uno de sus mayores cambios ha sido la inclusión del CLR dentro del motor de base de datos. Transact SQL (TSQL) es el lenguaje que usamos para escribir: Store Procedures – Triggers – Querys – Etc. SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, pero que no posee la potencia de los lenguajes de programación. Transact SQL es el lenguaje de programación que proporciona SQL Server para ampliar SQL con los elementos característicos de los lenguajes de programación: variables, sentencias de control de flujo, bucles ... Cuando se desea realizar una aplicación completa para el manejo de una base de datos relacional, resulta necesario utilizar alguna herramienta que soporte la capacidad de consulta del SQL y la versatilidad de los lenguajes de programación tradicionales. Transact SQL es el lenguaje de programación que proporciona SQL Server para extender el SQL estándar con otro tipo de instrucciones.

Transact SQL existe desde las primeras versiones de SQL Server, si bien a lo largo de este tutorial nos centraremos en la versión SQL Server 2005. ¿Qué vamos a necesitar? Para poder seguir este tutorial correctamente necesitaremos tener los siguientes elementos: 

Un servidor SQL Server 2005. Podemos descargar gratuitamente la versión SQL Server Express desde el siguiente enlace. http://www.microsoft.com/downloads/details.aspx?familyid=220549B5-0B074448-8848-DCC397514B41&displaylang=es



Herramientas cliente de SQL Server. Recomendamos: http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD14E3D-94B8-5A0F62BF7796&displaylang=es

Programación con Transact SQL Introducción SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, pero que no posee la potencia de los lenguajes de programación. No permite el uso de variables, estructuras de control de flujo, bucles ... y demás elementos característicos de la programación. No es de extrañar, SQL es un lenguaje de consulta, no un lenguaje de programación. Sin embargo, SQL es la herramienta ideal para trabajar con bases de datos. Cuando se desea realizar una aplicación completa para el manejo de una base de datos relacional, resulta necesario utilizar alguna herramienta que soporte la capacidad de consulta del SQL y la versatilidad de los lenguajes de programación tradicionales. Transact SQL es el lenguaje de programación que proporciona Microsoft SQL Server para extender el SQL estándar con otro tipo de instrucciones y elementos propios de los lenguajes de programación. Con Transact SQL vamos a poder programar las unidades de programa de la base de datos SQL Server, están son:    

Procedimientos almacenados Funciones Triggers Scripts

Pero además Transact SQL nos permite realizar programas sobre las siguientes herramientas de SQL Server: 

Service Broker

Fundamentos de Transact SQL Primeros pasos con Transact SQL Para programar en Transact SQL es necesario conocer sus fundamentos. Como introducción vamos a ver algunos elementos y conceptos básicos del lenguaje.  

 

Transact SQL no es CASE-SENSITIVE, es decir, no diferencia mayúsculas de minúsculas como otros lenguajes de programación como C o Java. Un comentario es una aclaración que el programador incluye en el código. Son soportados 2 estilos de comentarios, el de línea simple y de multilínea, para lo cual son empleados ciertos caracteres especiales como son: o -- Para un comentario de línea simple o /* ... */ Para un comentario de varias lineas Un literal es un valor fijo de tipo numérico, carácter, cadena o lógico no representado por un identificador (es un valor explícito). Una variable es un valor identificado por un nombre (identificador) sobre el que podemos realizar modificaciones. En Transact SQL los identificadores de variables deben comenzar por el carácter @, es decir, el nombre de una variable debe comenzar por @.Para declarar variables en

Transact SQL debemos utilizar la palabra clave declare, seguido del identificador y tipo de datos de la variable. Veamos algunos ejemplos:

-- Esto es un comentario de línea simple /* Este es un comentario con varias líneas. Conjunto de Líneas. */ declare @nombre varchar(50)-- declare declara una variable -- @nombre es el identificador de la -- variable de tipo varchar set @nombre = 'www.Microsoft.com' -- El signo = es un operador -- www.Microsoft.com es un literal print @Nombre -- Imprime por pantalla el valor de @nombre. -- No diferencia mayúsculas ni minúsculas

Scripts y lotes. Un script de Transact SQL es un conjunto de sentencias de Transact SQL en formato de texto plano que se ejecutan en un servidor de SQL Server. Un script está compuesto por uno o varios lotes. Un lote delimita el alcance de las variables y sentencias del script. Dentro de un mismo script se diferencian los diferentes lotes a través de las instrucción GO.

-- Este es el primer lote del script SELECT * FROM COMENTARIOS GO -- GO es el separador de lotes -- Este es el segundo lote del script SELECT getdate() -- getdate() es una función integrada que devuelve -- la fecha En ocasiones es necesario separar las sentencias en varios lotes, porque Transact SQL no permite la ejecución de ciertos comandos en el mismo lote, si bien normalmente también se utilizan los lotes para realizar separaciones lógicas dentro del script.

Tipos de datos en Transact SQL Cuando definimos una tabla, variable o constante debemos asignar un tipo de dato que indica los posibles valores. El tipo de datos define el formato de almacenamiento, espacio que de discomemoria que va a ocupar un campo o variable, restricciones y rango de valores validos. Transact SQL proporciona una variedad predefinida de tipos de datos . Casi todos los tipos de datos manejados por Transact SQL son similares a los soportados por SQL.

Tipos de datos numéricos. SQL Server dispone de varios tipos de datos numéricos. Cuanto mayor sea el número que puedan almacenar mayor será en consecuencia el espacio utilizado para almacenarlo. Como regla general se recomienda usar el tipo de dato mínimo posible. Todos los dato numéricos admiten el valor NULL. Bit. Una columna o variable de tipo bit puede almacenar el rango de valores de 1 a 0. Tinyint. Una columna o variable de tipo tinyint puede almacenar el rango de valores de 0 a 255. SmallInt. Una columna o variable de tipo smallint puede almacenar el rango de valores -32768 a 32767. Int. Una columna o variable de tipo int puede almacenar el rango de valores -231 a 231-1 . BigInt. Una columna o variable de tipo bigint puede almacenar el rango de valores -263 a 263-1 . Decimal(p,s). Una columna de tipo decimal puede almacenar datos numéricos decimales sin redondear. Donde p es la precisión (número total del dígitos) y s la escala (número de valores decimales) Float. Una columna de datos float puede almacenar el rango de valores -1,79x-10308 a 1,79x10308, , si la definimos con el valor máximo de precisión. La precisión puede variar entre 1 y 53. Real. Sinónimo de float(24). Puede almacenar el rango de valores -3,4x-1038 a 3,4x-1038, Money. Almacena valores numéricos monetarios de -263 a 263-1, con una precisión de hasta diez milésimas de la unidad monetaria. SmallMoney. Almacena valores numéricos monetarios de -214.748,3647 a 214.748,3647, con una precisión de hasta diez milésimas de la unidad monetaria. Todos los tipos de datos enteros pueden marcarse con la propiedad identity para hacerlos auto numéricos.

DECLARE @bit bit, @tinyint tinyint, @smallint smallint, @int int, @bigint bigint, @decimal decimal(10,3), -- 10 digitos, 7 enteros y -- 3 decimales @real real, @double float(53), @money money set @bit = 1 print @bit set @tinyint = 255 print @tinyint set @smallint = 32767 print @smallint set @int = 642325 print @int set @decimal = 56565.234 -- Punto como separador decimal print @decimal set @money = 12.34 print @money

Tipos de datos de caracter. Char(n). Almacena n caracteres en formato ASCII, un byte por cada letra. Cuando almacenamos datos en el tipo char, siempre se utilizan los n caracteres indicados, incluso si la entrada de datos es inferior. Por ejemplo, si en un char(5), guardamos el valor 'A', se almacena 'A ', ocupando los cinco bytes. Varchar(n).Almacena n caracteres en formato ASCII, un byte por cada letra. Cuando almacenamos datos en el tipo varchar, únicamente se utilizan los caracteres necesarios, Por ejemplo, si en un varchar(255), guardamos el valor 'A', se almacena 'A', ocupando solo un byte bytes. Varchar(max). Igual que varchar, pero al declararse como max puede almacenar 231-1 bytes. Nchar(n).Almacena n caracteres en formato UNICODE, dos bytes por cada letra. Es recomendable utilizar este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer a diferentes idiomas. Nvarchar(n).Almacena n caracteres en formato UNICODE, dos bytes por cada letra. Es recomendable utilizar este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer a diferentes idiomas. Nvarchar(max).Igual que varchar, pero al declararse como max puede almacenar 231-1 bytes.

Tipos de datos de fecha. Datetime. Almacena fechas con una precisión de milisegundo. Debe usarse para fechas muy específicas.

SmallDatetime. Almacena fechas con una precisión de minuto, por lo que ocupa la mitad de espacio de que el tipo datetime, para tablas que puedan llegar a tener muchos datos es un factor a tener muy en cuenta. TimeStamp.Se utiliza para marcar un registro con la fecha de inserción - actualización. El tipo timestamp se actualiza automáticamente cada vez que insertamos o modificamos los datos.

Tipos de datos binarios. Binary. Se utiliza para almacenar datos binarios de longitud fija, con una longitud máxima de 8000 bytes. Varbinary. Se utiliza para almacenar datos binarios de longitud variable, con una longitud máxima de 8000 bytes..Es muy similar a binary, salvo que varbinary utiliza menos espacio en disco. Varbinary(max).Igual que varbinary, pero puede almacenar 231-1 bytes

Tipo de datos XML. XML.Una de las grandes mejoras que incorpora SQL Server 2005 es el soporte nativo para XML. Como podemos deducir, este tipo de datos se utiliza para almacenar XML.

DECLARE @myxml XML set @myxml = (SELECT @@SERVERNAME NOMBRE FOR XML RAW, TYPE) print cast(@myxml as varchar(max)) Obtendremos la siguiente salida:



Otros tipos de datos. UniqueIdentifier. Se utiliza para identificadores únicos. Para generar identificadores únicos debemos utilizar la función NEWID().

DECLARE @myuniqueid UNIQUEIDENTIFIER set @myuniqueid = NEWID() print cast(@myuniqueid as varchar(36)) Obtendremos la siguiente salida: 46141D79-102C-4C29-A620-792EA0208637 Sql_Variant.Permite almacenar valores de diferentes tipos de datos. No puede almacenar varchar(max), xml, timestamp y tipos de datos definidos por el usuario.

Tipos de datos personalizados.

Transact SQL permite la creación de tipos de datos personalizados, a través de la instrucción CREATE TYPE. Personalmente, desaconsejo el uso de tipos de datos personalizados.

CREATE TYPE MD5 FROM CHAR(32) NULL GO DECLARE @miMD5 MD5 set @miMD5 = '0000000000000000000000000000000A' print @miMD5

Variables en Transact SQL A.

Declarar variables es Transact SQL

Una variable es un valor identificado por un nombre (identificador) sobre el que podemos realizar modificaciones. En Transact SQL los identificadores de variables deben comenzar por el carácter @, es decir, el nombre de una variable debe comenzar por @. Para declarar variables en Transact SQL debemos utilizar la palabra clave declare, seguido del identificador y tipo de datos de la variable.

-- Esto es un comentario de linea simple /* Este es un comentario con varias líneas. Conjunto de Líneas. */ declare @nombre varchar(50)-- declare declara una variable -- @nombre es el identificador de la -- variable de tipo varchar set @nombre = 'www.Microsoft.com' -- El signo = es un operador -- www.Microsoft.com es un literal print @Nombre -- Imprime por pantalla el valor de @nombre. -- No diferencia mayúsculas ni minúsculas

B.

Asignar variables en Transact SQL

En Transact SQL podemos asignar valores a una variable de varias formas:   

A través de la instruncción set. Utilizando una sentencia SELECT. Realizando un FETCH de un cursor.

El siguiente ejemplo muestra como asignar una variable utilizando la instrucción SET.

DECLARE @nombre VARCHAR(100) -- La consulta debe devolver un único registro SET @nombre =

(SELECT nombre FROM CLIENTES WHERE ID = 1)

PRINT @nombre

El siguiente ejemplo muestra como asignar variables utilizando una sentencia SELECT.

DECLARE @nombre VARCHAR(100), @apellido1 VARCHAR(100), @apellido2 VARCHAR(100)

SELECT

@nombre=nombre , @apellido1=Apellido1, @apellido2=Apellido2

FROM CLIENTES WHERE ID = 1

PRINT @nombre PRINT @apellido1 PRINT @apellido2 Un punto a tener en cuenta cuando asignamos variables de este modo, es que si la consulta SELECT devuelve más de un registro, las variables quedarán asignadas con los valores de la última fila devuelta.

Por último veamos como asignar variables a través de un cursor.

DECLARE @nombre VARCHAR(100), @apellido1 VARCHAR(100), @apellido2 VARCHAR(100)

DECLARE CDATOS CURSOR FOR SELECT nombre , Apellido1, Apellido2 FROM CLIENTES

OPEN CDATOS FETCH CDATOS INTO @nombre, @apellido1, @apellido2

WHILE (@@FETCH_STATUS = 0) BEGIN PRINT @nombre PRINT @apellido1 PRINT @apellido2 FETCH CDATOS INTO @nombre, @apellido1, @apellido2 END

CLOSE CDATOS DEALLOCATE CDATOS Veremos los cursores con más detalle más adelante en este tutorial.

Equivalencia de datos de SQL Server y .NET La siguiente lista muestra los tipos de datos de SQL Server 2005 y sus equivalentes con CRL, para el namespace System.Data.SqlTypes y los tipos nativos de CRL .NET FrameWork

SqlBytes, SqlBinary SqlBytes, SqlBinary

CLR data type (.NET Framework) Byte[] Byte[]

SqlBytes, SqlBinary

byte, Byte[]

ninguno ninguno ninguno

ninguno ninguno ninguno

SqlChars, SqlString

Char, String, Char[]

SQL Server varbinary binary varbinary(1), binary(1) image varchar char nvarchar(1), nchar(1)

CLR data type (SQL Server)

SqlChars, SqlString nvarchar

SQLChars es mejor para la transferencia de datos y String, Char[] SQLString obtiene mejor rendimiento para operaciones con Strings.

nchar text

SqlChars, SqlString ninguno

String, Char[] ninguno

ntext uniqueidentifier rowversion

ninguno SqlGuid ninguno

ninguno Guid Byte[]

bit tinyint smallint

SqlBoolean SqlByte SqlInt16

Boolean Byte Int16

int bigint smallmoney

SqlInt32 SqlInt64 SqlMoney

Int32 Int64 Decimal

money numeric decimal real float

SqlMoney SqlDecimal SqlDecimal SqlSingle SqlDouble

Decimal Decimal Decimal Single Double

smalldatetime datetime sql_variant User-defined type(UDT) table

SqlDateTime SqlDateTime ninguno

ninguno

DateTime DateTime Object Misma clase que la definida en el assemblie. ninguno

cursor

ninguno

ninguno

ninguno

timestamp xml

ninguno SqlXml

ninguno ninguno

Operadores en Transact SQL La siguiente tabla ilustra los operadores de Transact SQL . Tipo de operador

Operadores

Operador de asignación

=

Operadores aritméticos

+ (suma) - (resta) * (multiplicación) / (división) ** (exponente) % (modulo)

Operadores relacionales = (igual a) <> (distinto de) o de comparación != (distinto de) < (menor que) > (mayor que) >= (mayor o igual a) <= (menor o igual a) !> (no mayor a) !< (no menor a) Operadores lógicos

Operador de concatenación

AND (y lógico) NOT (negacion) OR (o lógico) & (AND a nivel de bit) | (OR a nivel de bit) ^ (OR exclusivo a nivel de bit)

+

ALL (Devuelve TRUE si el conjunto completo de comparaciones es TRUE) ANY(Devuelve TRUE si cualquier elemento del conjunto de comparaciones es TRUE)

Otros

BETWEEN (Devuelve TRUE si el operando está dentro del intervalo) EXISTS (TRUE si una subconsulta contiene filas) IN (TRUE si el operando está en la lista) LIKE (TRUE si el operando coincide con un patron) NOT (Invierte el valor de cualquier operador booleano) SOME(Devuelve TRUE si alguna de las comparaciones de un conjunto es TRUE)

Estructuras de control en Transact SQL Estructura condicional IF La estuctura condicional IF permite evaluar una expresion booleana (resultado SI - NO), y ejecutar las operaciones contenidas en el bloque formado por BEGIN END.

IF () BEGIN ... END ELSE IF () BEGIN ... END ELSE BEGIN ... END Ejemplo de la estructura condicional IF.

DECLARE @Web varchar(100), @diminutivo varchar(3) SET @diminutivo = 'DJK' IF

@diminutivo = 'DJK' BEGIN PRINT 'www.microsoft.com' END ELSE BEGIN PRINT 'Otra Web (peor!)' END

La estructura IF admite el uso de subconsultas:

DECLARE @coPais int, @descripcion varchar(255) set @coPais = 5 set @descripcion = 'España' IF EXISTS(SELECT * FROM PAISES WHERE CO_PAIS = @coPais) BEGIN UPDATE PAISES SET DESCRIPCION = @descripcion WHERE CO_PAIS = @coPais END ELSE BEGIN INSERT INTO PAISES (CO_PAIS, DESCRIPCION) VALUES (@coPais, @descripcion) END

Estructura condicional CASE La estructura condicional CASE permite evaluar una expresion y devolver un valor u otro. La sintaxis general de case es:

CASE WHEN THEN WHEN THEN ELSE -- Valor por defecto END Ejemplo de CASE.

DECLARE @Web varchar(100), @diminutivo varchar(3) SET @diminutivo = 'DJK' SET @Web = (CASE @diminutivo WHEN 'DJK' THEN 'www.Microsoft.com' WHEN 'ALM' THEN 'www.aleamedia.com' ELSE 'www.Microsoft.com' END) PRINT @Web

Otra sintaxis de CASE nos permite evaluar diferentes expresiones:

CASE WHEN = THEN WHEN = THEN ELSE -- Valor por defecto END El mismo ejemplo aplicando esta sintaxis:

DECLARE @Web varchar(100), @diminutivo varchar(3) SET @diminutivo = 'DJK' SET @Web = (CASE WHEN @diminutivo = 'DJK' THEN 'www.Microsoft.com' WHEN @diminutivo = 'ALM' THEN 'www.aleamedia.com' ELSE 'www.Microsoft.com' END) PRINT @Web

Otro aspecto muy interesante de CASE es que permite el uso de subconsultas.

DECLARE @Web varchar(100), @diminutivo varchar(3) SET @diminutivo = 'DJK' SET @Web = (CASE WHEN @diminutivo = 'DJK' THEN (SELECT web FROM WEBS WHERE id=1) WHEN @diminutivo = 'ALM' THEN (SELECT web FROM WEBS WHERE id=2) ELSE 'www.Microsoft.com' END) PRINT @Web

Bucle WHILE El bucle WHILE se repite mientras expresion se evalue como verdadero. Es el único tipo de bucle del que dispone Transact SQL.

WHILE BEGIN ... END Un ejemplo del bucle WHILE.

DECLARE @contador int SET @contador = 0 WHILE (@contador < 100) BEGIN SET @contador = @contador + 1 PRINT 'Iteracion del bucle ' + cast(@contador AS varchar) END Podemos pasar a la siguiente iteración del bucle utilizando CONTINUE.

DECLARE @contador int SET @contador = 0 WHILE (@contador < 100) BEGIN SET @contador = @contador + 1 IF (@contador % 2 = 0) CONTINUE PRINT 'Iteracion del bucle ' + cast(@contador AS varchar) END El bucle se dejará de repetir con la instrucción BREAK.

DECLARE @contador int SET @contador = 0 WHILE (1 = 1) BEGIN SET @contador = @contador + 1 IF (@contador % 50 = 0) BREAK PRINT 'Iteracion del bucle ' + cast(@contador AS varchar) END También podemos utilizar el bucle WHILE conuntamente con subconsultas.

DECLARE @coRecibo int WHILE EXISTS (SELECT * FROM RECIBOS WHERE PENDIENTE = 'S')-- Ojo, la subconsulta se ejecuta -- una vez por cada iteracion -- del bucle! BEGIN SET @coRecibo = (SELECT TOP 1 CO_RECIBO FROM RECIBOS WHERE PENDIENTE = 'S') UPDATE RECIBOS SET PENDIENTE = 'N' WHERE CO_RECIBO = @coRecibo END

Estructura GOTO La sentencia goto nos permite desviar el flujo de ejecución hacia una etiqueta. Fué muy utilizada en versiones anteriores de SQL Server conjuntamente con la variable de sistema @@ERROR para el control de errores. Actualmente, se desaconseja el uso GOTO, recomendandose el uso de TRY - CATCH para la gestion de errores.

DECLARE @divisor int, @dividendo int, @resultado int SET @dividendo = 100 SET @divisor = 0 SET @resultado = @dividendo/@divisor IF @@ERROR > 0 GOTO error PRINT 'No hay error' RETURN error: PRINT 'Se ha producido una division por cero'

Control de errores en Transact SQL Uso de TRY CATCH A partIr de la versión 2005, SQL Server proporciona el control de errores a través de las instrucciónes TRY y CATCH. Estas nuevas instrucciones suponen un gran paso adelante en el control de errores en SQL Server, un tanto precario en las versiones anteriores. La sintaxis de TRY CATCH es la siguiente:

BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH El siguiente ejemplo ilustra el uso de TRY - CATCH.

BEGIN TRY DECLARE @divisor int , @dividendo int, @resultado int SET @dividendo = 100 SET @divisor = 0 -- Esta línea provoca un error de división por 0 SET @resultado = @dividendo/@divisor PRINT 'No hay error' END TRY BEGIN CATCH PRINT 'Se ha producido un error' END CATCH

Funciones especiales de Error Las funciones especiales de error, están disponibles únicamente en el bloque CATCH para la obtención de información detallada del error. Son:      

ERROR_NUMBER(), devuelve el número de error. ERROR_SEVERITY(), devuelve la severidad del error. ERROR_STATE(), devuelve el estado del error. ERROR_PROCEDURE(), devuelve el nombre del procedimiento almacenado que ha provocado el error. ERROR_LINE(), devuelve el número de línea en el que se ha producido el error. ERROR_MESSAGE(), devuelve el mensaje de error.

Son extremadamente útiles para realizar una auditoría de errores.

BEGIN TRY DECLARE @divisor int , @dividendo int, @resultado int SET @dividendo = 100 SET @divisor = 0 -- Esta linea provoca un error de division por 0 SET @resultado = @dividendo/@divisor PRINT 'No hay error' END TRY BEGIN CATCH PRINT ERROR_NUMBER() PRINT ERROR_SEVERITY() PRINT ERROR_STATE() PRINT ERROR_PROCEDURE() PRINT ERROR_LINE() PRINT ERROR_MESSAGE() END CATCH Lógicamente, podemos utilizar estas funciones para almacenar esta información en una tabla de la base de datos y registrar todos los errores que se produzcan.

La variable de sistema @@ERROR En versiones anteriores a SQL Server 2005, no estaban disponibles las instrucciones TRY CATCH. En estas versiones se controlaban los errores utilizando la variable global de sistema @@ERROR, que almacena el número de error producido por la última sentencia Transact SQL ejecutada.

DECLARE @divisor int , @dividendo int , @resultado int SET @dividendo = 100 SET @divisor = 0 -- Esta linea provoca un error de division por 0 SET @resultado = @dividendo/@divisor IF @@ERROR = 0 BEGIN PRINT 'No hay error' END ELSE BEGIN PRINT 'Hay error' END El uso de @@ERROR para controlar errores puede provocar multitud de problemas. Uno de los más habituales es sin duda, incluir una nueva sentencia Transact SQL entre la línea que provoco el error y la que lo controla. Esa nueva instrucción restaura el valor de @@ERROR y no controlaremos el error. El siguiente ejemplo ilustra esta situación:

DECLARE @divisor int , @dividendo int , @resultado int SET @dividendo = 100 SET @divisor = 0 -- Esta línea provoca un error de división por 0 SET @resultado = @dividendo/@divisor PRINT 'Controlando el error ...' -- Esta línea estable @@ERROR a cero IF @@ERROR = 0 BEGIN -- Se ejecuta esta parte! PRINT 'No hay error' END ELSE BEGIN PRINT 'Hay error'

END

Generar un error con RAISERROR En ocasiones es necesario provocar voluntariamente un error, por ejemplo nos puede interesas que se genere un error cuando los datos incumplen una regla de negocio. Podemos provocar un error en tiempo de ejecución a través de la función RAISERROR.

DECLARE @tipo int, @clasificacion int SET @tipo = 1 SET @clasificacion = 3 IF (@tipo = 1 AND @clasificacion = 3) BEGIN RAISERROR ('El tipo no puede valer uno y la clasificacion 3', 16, -- Severidad 1 -- Estado ) END La función RAISERROR recibe tres parámetros, el mensaje del error (o código de error predefinido), la severidad y el estado. La severidad indica el grado de criticidad del error. Admite valores de 0 al 25, pero solo podemos asignar valores del 0 al 18. Los errores el 20 al 25 son considerados fatales por el sistema, y cerraran la conexión que ejecuta el comando RAISERROR. Para asignar valores del 19 al 25 necesitares ser miembros de la función de SQL Server sysadmin. El estado es un valor para permitir que el programador identifique el mismo error desde diferentes partes del código. Admite valores entre 1 y 127, permite tratar

Consultar datos en Transact SQL La sentencia SELECT La sentencia SELECT nos permite consultar los datos almacenados en una tabla de la base de datos. El formato de la sentencia select es: SELECT [ALL | DISTINCT ][ TOP expression [ PERCENT ] [ WITH TIES ] ] FROM [ INNER | LEFT [OUTER]| RIGHT [OUTER] | CROSS] [JOIN ] ON [ AND|OR ] [WHERE [ AND|OR ]] [GROUP BY ] [HAVING [ AND|OR ]] [ORDER BY [ASC | DESC] El siguiente ejemplo muestra una consulta sencilla que obtiene el código y la "familia" de una tabla llamada familias (representaría familias de productos por ejemplo).

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS

El uso del asterisco indica que queremos que la consulta devuelva todos los campos que existen en la tabla.

SELECT * FROM FAMILIAS

Ahora vamos a realizar una consulta obteniendo además de los datos de familias, los datos de las categorías y los productos.

SELECT * FROM FAMILIAS INNER JOIN CATEGORIAS ON CATEGORIAS.CO_FAMILIA = FAMILIAS.CO_FAMILIA INNER JOIN PRODUCTOS ON PRODUCTOS.CO_CATEGORIA = CATEGORIAS.CO_CATEGORIA La combinación se realiza a través de la cláusula INNER JOIN, que es una clausula exclusiva, es decir las familias que no tengan categorías y productos asociados no se devolverán. Si queremos realizar la consulta para que no sea exclusiva, tenemos que utilizar LEFT JOIN. El uso de la palabra reservada OUTER es opcional.

SELECT * FROM FAMILIAS LEFT OUTER JOIN CATEGORIAS ON CATEGORIAS.CO_FAMILIA = FAMILIAS.CO_FAMILIA LEFT OUTER JOIN PRODUCTOS ON PRODUCTOS.CO_CATEGORIA = CATEGORIAS.CO_CATEGORIA Los registros que no tengan datos relacionados en una consulta LEFT JOIN devolverán en valor null en los campos que correspondan a las tablas en las que no tienen dato. También podemos forzar un producto cartesiano (todos con todos) a través de CROSS JOIN.

SELECT * FROM FAMILIAS CROSS JOIN CATEGORIAS

La cláusula WHERE La cláusula WHERE es la instrucción que nos permite filtrar el resultado de una sentencia SELECT.

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS WHERE CO_FAMILIA = 1 Por supuesto, podemos especificar varias condiciones para el WHERE:

SELECT * FROM FAMILIAS WHERE CO_FAMILIA = 1 OR CO_FAMILIA = 2 Podemos agrupar varios valores para una condición en la cláusula IN:

SELECT * FROM FAMILIAS WHERE CO_FAMILIA IN ( 1 , 2)

La cláusula WHERE se puede utilizar conjuntamente con INNER JOIN, LEFT JOIN ...

SELECT

FAMILIAS.CO_FAMILIA, FAMILIAS.FAMILIA

FROM FAMILIAS INNER JOIN CATEGORIAS ON CATEGORIAS.CO_FAMILIA = FAMILIAS.CO_FAMILIA WHERE FAMILIAS.CO_FAMILIA > 1

Siempre que incluyamos un valor alfanumérico para un campo en la condición WHERE este debe ir entre comillas simples:

SELECT * FROM FAMILIAS WHERE FAMILIA = 'FAMILIA 1' Para consultar campos alfanuméricos, es decir, campos de texto podemos utilizar el operador LIKE conjuntamente con comodines.

SELECT * FROM FAMILIAS WHERE FAMILIA LIKE 'FAM%' Los comodines que podemos utilizar en son los siguientes:      

% , representa cualquier cadena de texto de cero o más caracteres de cualquier longitud. _ , representa un carácter. [a-d], representa cualquier carácter del intervalo a-d. [abcd], representa cualquier carácter del grupo abcd. [^a-d], representa cualquier carácter diferente del intervalo a-d. [^abcd], representa cualquier carácter distinto del grupo abcd.

También podemos obtener los valores distintos utilizando DISTINCT.

SELECT DISTINCT FAMILIA -- Devuelve los distintos valores de FAMILIA FROM FAMILIAS Podemos limitar el número de registros que devuelve la consulta a través de la cláusula TOP. La cláusula TOP admite como parámetros un valor numérico entero o un porcentaje (sólo a partir de la versión 2005)

SELECT TOP 10 * -- Devuelve 10 registros FROM FAMILIAS SELECT TOP 50 PERCENT * -- Devuelve el 50% de los registros FROM FAMILIAS

La cláusula TOP se puede combinar con WITH TIES en consultas agregadas.

La cláusula ORDER BY Podemos especificar el orden en el que serán devueltos los datos a través de la cláusula ORDER BY.

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS ORDER BY FAMILIA DESC También podemos indicar el índice del campo en la lista de selección en lugar de su nombre :

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS ORDER BY 2 DESC -- Ordena por FAMILIA

Consultas agregadas La cláusula GROUP BY La cláusula GROUP BY combina los registros devueltos por una consulta SELECT obteniendo uno o varios valores agregados(suma, valor mínimo y máximo ...). Para cada registro se puede crear un valor agregado si se incluye una función SQL agregada, como por ejemplo Sum o Count, en la instrucción SELECT. Su sintaxis es:

SELECT [ALL | DISTINCT ] [TOP [WITH TIES]] [{,}] [{,}] FROM | [{,|}] [WHERE [{ AND|OR }]] [GROUP BY [{,}]] [HAVING [{ AND|OR }]] [ORDER BY | [ASC | DESC] [{,| [ASC | DESC ]}]] Si se utiliza GROUP BY pero no existe una función SQL agregada en la instrucción SELECT se obtiene el mismo resultado que con una consulta SELECT DISTINCT. Los valores Null en los campos GROUP BY se agrupan y no se omiten. No obstante, los valores Null no se evalúan en ninguna de las funciones SQL agregadas. Todos los campos de la lista de campos de SELECT deben incluirse en la cláusula GROUP BY o como argumentos de una función SQL agregada. El siguiente ejemplo realiza una "cuenta" de los datos que hay en la tabla PRODUCTOS.

SELECT COUNT(*) FROM PRODUCTOS

Este otro ejemplo, muestra la suma del PRECIO de cada uno de los productos que componen un pedido, para calcular el total del pedido agrupado por los datos del cliente.

SELECT

CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2, SUM(PRECIO) -- Total del pedido

FROM DETALLE_PEDIDO INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE GROUP BY CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2

Siempre que incluyamos una clausula WHERE en una consulta agregada esta se aplica antes de calcular el valor agregado. Es decir, si sumamos el valor de las ventas por producto, la suma se calcula después de haber aplicado el filtro impuesto por la cláusula WHERE.

SELECT

CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2, SUM(PRECIO) -- Total del pedido

FROM DETALLE_PEDIDO INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE -- La cláusula WHERE se aplica antes de realizar el calculo WHERE CLIENTES.NOMBRE != 'UN NOMBRE' GROUP BY CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2

La cláusula HAVING Es posible que necesitemos calcular un agregado, pero que no necesitemos obtener todos los datos, solo los que cumplan una condición del agregado. Por ejemplo, podemos calcular el valor de las ventas por producto, pero que solo queramos ver los datos de los productos que hayan vendido más o menos de una determinada cantidad. En estos casos debemos utilizar la cláusula HAVING. Una vez que GROUP BY ha combinado los registros, HAVING muestra cualquier registro agrupado por la cláusula GROUP BY que satisfaga las condiciones de la cláusula HAVING. Se utiliza la cláusula WHERE para excluir aquellas filas que no desea agrupar, y la cláusula HAVING para filtrar los registros una vez agrupados.

HAVING es similar a WHERE, determina qué registros se seleccionan pero después de calcular el agregado. Una vez que los registros se han agrupado utilizando GROUP BY, HAVING determina cuáles de ellos se van a mostrar. HAVING permite el uso de funciones agregadas.

SELECT

CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2, SUM(PRECIO) -- Total del pedido

FROM DETALLE_PEDIDO INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE -- La cláusula WHERE se aplica antes de realizar el calculo WHERE CLIENTES.NOMBRE != 'UN NOMBRE' GROUP BY CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2 HAVING SUM(PRECIO) > 100

Funciones agregadas. Transact SQL pone a nuestra disposición múltiples funciones agregadas, las más comunes son:     

MAX MIN COUNT SUM AVG

AVG Calcula la media aritmética de un conjunto de valores contenidos en un campo especificado de una consulta. Su sintaxis es la siguiente

AVG()

En donde expr representa el campo que contiene los datos numéricos para los que se desea calcular la media o una expresión que realiza un cálculo utilizando los datos de dicho campo. La media calculada por Avg es la media aritmética (la suma de los valores dividido por el número de valores). La función Avg no incluye ningún campo Null en el cálculo.

SELECT

CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2, AVG(PRECIO) -- Promedio del pedido

FROM DETALLE_PEDIDO INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE GROUP BY CLIENTES.NOMBRE, CLIENTES.APELLIDO1, CLIENTES.APELLIDO2

Count Calcula el número de registros devueltos por una consulta. Su sintaxis es la siguiente:

COUNT() En donde expr contiene el nombre del campo que desea contar. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL). Puede contar cualquier tipo de datos incluso texto. Aunque expr puede realizar un cálculo sobre un campo, Count simplemente cuenta el número de registros sin tener en cuenta qué valores se almacenan en los registros. La función Count no cuenta los registros que tienen campos null a menos que expr sea el carácter comodín asterisco (*). Si utiliza un asterisco, Count calcula el número total de registros, incluyendo aquellos que contienen campos null. Count(*) es considerablemente más rápida que Count(Campo).

SELECT COUNT(*) FROM PEDIDOS SELECT CLIENTES.NOMBRE, COUNT(*) FROM PEDIDOS INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE GROUP BY CLIENTES.NOMBRE

Max, Min Devuelven el mínimo o el máximo de un conjunto de valores contenidos en un campo especifico de una consulta. Su sintaxis es:

MIN() MAX() En donde expr es el campo sobre el que se desea realizar el cálculo. Expr puede incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).

SELECT

CLIENTES.NOMBRE, MIN(PEDIDOS.FX_ALTA), MAX(PEDIDOS.FX_ALTA)

FROM PEDIDOS INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE GROUP BY CLIENTES.NOMBRE

Sum Devuelve la suma del conjunto de valores contenido en un campo especifico de una consulta. Su sintaxis es:

SUM() En donde expr representa el nombre del campo que contiene los datos que desean sumarse o una expresión que realiza un cálculo utilizando los datos de dichos campos. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).

SELECT

CLIENTES.NOMBRE, SUM(PEDIDOS.TOTAL_PEDIDO)

FROM PEDIDOS INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE GROUP BY CLIENTES.NOMBRE

Uso de Select TOP con consultas agregadas. Podemos utilizar SELECT TOP con consultas agregadas como con cualquier otra instrucción Transact SQL. En estos casos, la cláusula TOP se aplica después de calcular el agregado, devolviendo las N filas indicadas. En este escenario es posible que queramos obtener los N valores que satisfagan una condición. Por ejemplo, queremos si queremos obtener los tres primeros clientes con mayores pedidos, usaríamos una consulta parecida a esta:

SELECT TOP 3 CLIENTES.NOMBRE, SUM(DETALLE_PEDIDO.PRECIO) FROM DETALLE_PEDIDO INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE GROUP BY CLIENTES.NOMBRE ORDER BY 2 -- SUM(DETALLE_PEDIDO.PRECIO_UNIDAD) Sin embargo, puede darse el caso, de que el cuarto cliente devuelto por la consulta tenga un valor agregado idéntico al tercero, (es decir, están empatados). El uso de TOP 3 discriminaría el cuarto registro. Para evitar este comportamiento, y que la consulta devuelva también al cuarto cliente utilizamos la

cláusula WITH TIES.

SELECT TOP 3 WITH TIES CLIENTES.NOMBRE, SUM(DETALLE_PEDIDO.PRECIO) FROM DETALLE_PEDIDO INNER JOIN PEDIDOS ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO INNER JOIN CLIENTES ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE GROUP BY CLIENTES.NOMBRE ORDER BY 2 -- SUM(DETALLE_PEDIDO.PRECIO_UNIDAD)

Select FOR XML Cláusula FOR XML. A partir de la versión 2000 SQL Server incluye la cláusula FOR XML para la consultas. Sin embargo, es a partir de la versión 2005 cuando se integra XML como tipo de dato nativo. Cuando especificamos la cláusula FOR XML el resultado de la consulta es devuelto en formato XML. La cláusula FOR XML admite los siguientes modos que representan el formato en el que el XML es devuelto:   

XML AUTO, el modo AUTO emplea los campos en la declaración SELECT para formar una jerarquía simple XML. XML RAW, el modo RAW genera elementos únicos, los cuales se denominan row, por cada fila retornada. EXPLICIT, el modo EXPLICIT requiere un formato específico que puede ser mapeado en casi cualquier forma XML, y al mismo tiempo ser formulado por una sola consulta SQL.

Adicionalmente, disponemos de dos opciones más TYPE y ELEMENTS que determinan el formato del XML resultante. Los vemos con ejemplos. Un ejemplo de XML AUTO.

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS ORDER BY FAMILIA FOR XML AUTO, TYPE Obtendremos el siguiente resultado:

Podemos obtener el resultado como elementos de la siguiente forma:

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS FOR XML AUTO, ELEMENTS Obtendremos el siguiente resultado:

1 FAMILIA 1 2 FAMILIA 2 3 FAMILIA 3 4 FAMILIA 4 Ahora un ejemplo de XML RAW:

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS

ORDER BY FAMILIA FOR XML RAW , TYPE Obtenemos el siguiente resultado:

Podemos obtener el resultado como elementos de la siguiente forma:

1 FAMILIA 1 2 FAMILIA 2 3 FAMILIA 3 4 FAMILIA 4

También es posible especificar el nodo que queremos que muestre:

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS ORDER BY FAMILIA FOR XML RAW ('FamiliasDeProductos') , TYPE Devuelve el siguiente resultado:

Del mismo modo con la opción ELEMENTS:

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS ORDER BY FAMILIA FOR XML RAW ('FamiliasDeProductos') , ELEMENTS Obtendremos el siguiente resultado:

1 FAMILIA 1 2

FAMILIA 2 3 FAMILIA 3 4 FAMILIA 4 Ahora un ejemplo con el formato XML EXPLICIT.

SELECT 1 AS TAG,

-- La primera columna debe tener el alias TAG

NULL AS PARENT, -- La segunda columna debe tener el alias PARENT -- El resto de columnas deben tener el alias en el formato: -- !! CO_FAMILIA as "FamiliaDeProductos!1!CODIGO_FAMILIA", FAMILIA

as "FamiliaDeProductos!1!DESCRIPCION"

FROM FAMILIAS ORDER BY FAMILIA FOR XML EXPLICIT Obtenemos el siguiente resultado:





Campos y variables XML. Dado que XML es un tipo nativo de XML podemos definir tablas con campos de tipo XML, variables... El siguiente ejemplo muestra cómo trabajar con campos y variables XML.

-- Primero creamos una tabla con un campo XML CREATE TABLE tablaXML ( ID int not null identity, DOC xml null, constraint PK_tablaXML PRIMARY KEY (ID) )

GO

DECLARE @xml xml -- Variable de tipo XML

-- Leemos los datos de la tabla FAMILIAS SET @xml = (SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS FOR XML AUTO)

-- y los guardamos en nuestra tabla INSERT INTO tablaXML (DOC) VALUES (@xml)

-- Hacemos lo mismo con los productos SET @xml = (SELECT * FROM PRODUCTOS FOR XML AUTO)

INSERT INTO tablaXML (DOC) VALUES (@xml)

-- Consultamos la tabla y vemos el resultado SELECT * FROM tablaXML Cuando consultemos la tabla tendremos la siguiente información (en mi caso claro!):



Operaciones con conjuntos. SQL Server 2005 permite tres tipos de operaciones con conjuntos:   

UNION, disponible en todas las versiones de SQL Server. EXCEPT, nuevo en SQL Server 2005. INTERSECT, nuevo en SQL Server 2005.

Para utilizar operaciones de conjuntos debemos cumplir una serie de normas.  

Las consultas a unir deben tener el mismo número campos, y además los campos deben ser del mismo tipo. Sólo puede haber una única clausula ORDER BY al final de la sentencia SELECT.

UNION UNION devuelve la suma de dos o más conjuntos de resultados. El conjunto obtenido como resultado de UNION tiene la misma estructura que los conjuntos originales. El siguiente ejemplo muestra el uso de UNION

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento FROM EMPLEADOS UNION SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento FROM CLIENTES Cuando realizamos una consulta con UNION internamente se realiza una operación DISTINCT sobre el conjunto de resultados final. Si queremos obtener todos los valores debemos utiliza UNION ALL.

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento FROM EMPLEADOS UNION ALL SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento FROM CLIENTES

EXCEPT EXCEPT devuelve la diferencia (resta) de dos o más conjuntos de resultados. El conjunto obtenido como resultado de EXCEPT tiene la misma estructura que los conjuntos originales. El siguiente ejemplo muestra el uso de EXCEPT

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento FROM EMPLEADOS EXCEPT SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento FROM CLIENTES El uso de EXCEPT, como norma general, es mucho más rápido que utilizar condiciones NOT IN o EXISTS en la cláusula WHERE.

INTERSECT Devuelve la intersección entre dos o más conjuntos de resultados en uno. El conjunto obtenido como resultado de INTERSECT tiene la misma estructura que los conjuntos originales. El siguiente ejemplo muestra el uso de INTERSECT

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento FROM EMPLEADOS INTERSECT SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento FROM CLIENTES

Insertar datos en Transact SQL Inserción individual de filas. Para realizar la inserción individual de filas SQL posee la instrucción INSERT INTO.La inserción individual de filas es la que más comúnmente utilizaremos. Su sintaxis es la siguiente:

INSERT INTO [([,,...])] values (,,...); El siguiente ejemplo muestra la inserción de un registro en la tabla PRECIOS.

INSERT INTO PRECIOS (PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) VALUES (10, getdate(),getdate()+30, 1)

Inserción múltiple de filas. También es posible insertar en una tabla el resultado de una consulta SELECT. De este modo se insertarán tantas filas como haya devuelto la consulta SELECT. El siguiente ejemplo muestra la inserción múltiple de filas.

INSERT INTO PRECIOS (PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) SELECT

PRECIO_UNIDAD,

getdate(), getdate() + 30, CO_PRODUCTO FROM DETALLE_PEDIDO

Inserción de valores por defecto. También podemos forzar a que la insercción se realice con los datos por defecto establecidos para la tabla (o null si no tienen valores por defecto).

INSERT INTO PRECIOS DEFAULT VALUES En SQL Sever podemos marcar un campo de una tabla como auto numérico (identity), cuando insertamos un registro en dicha tabla el valor del campo se genera automáticamente. Para recuperar el valor generado disponemos de varios métodos: 

Utilizar la función @@identity, que devuelve el último valor identidad insertado por la transacción:

DECLARE @Codigo int

INSERT INTO PRECIOS (PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) VALUES (10, getdate(),getdate()+30, 1)

set @Codigo = @@Identity

PRINT @Codigo

El uso de @@Identity no siempre es válido, ya que al devolver el último valor identidad insertado por la transacción, no nos garantiza que el valor haya sido insertado en la tabla que nos interesa (por ejemplo la tabla podría tener un trigger que insertara datos en otra tabla con campos identidad).



En este tipo de escenarios debemos utilizar la función, SCOPE_IDENTITY.

DECLARE @Codigo int

INSERT INTO PRECIOS (PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) VALUES (10, getdate(),getdate()+30, 1)

SET @Codigo = SCOPE_IDENTITY() PRINT @Codigo

Clausula OUTPUT A partir de la versión de SQL Server 2005 disponemos de la cláusula OUTPUT para recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y DELETED. Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o DELETE. Es decir, son una copia de los datos "antes" del cambio. DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT. Las columnas con prefijo INSERTED reflejan el valor después de que se complete la instrucción UPDATE o INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copia de los datos "después" del cambio. INSERTED no se puede utilizar con la cláusula OUTPUT en la instrucción DELETE.

DECLARE @FILAS_INSERTADAS TABLE ( CO_PRECIO int, PRECIO decimal, FX_INICIO datetime, FX_FIN datetime,

CO_PRODUCTO int )

INSERT INTO PRECIOS (PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) OUTPUT INSERTED.* INTO @FILAS_INSERTADAS VALUES (10, getdate(),getdate()+30, 1)

SELECT * FROM @FILAS_INSERTADAS

II.

Actualizar datos en Transact SQL A.

Update

Para la actualización de datos Transact SQL dispone de la sentencia UPDATE. La sentencia UPDATE permite la actualización de uno o varios registros de una única tabla. La sintaxis de la sentencia UPDATE es la siguiente

UPDATE SET = {[, = ,..., = ]} [ WHERE ]; El siguiente ejemplo muestra el uso de UPDATE.

UPDATE CLIENTES SET NOMBRE = 'Microsoft', APELLIDO1 = 'Herrarte', APELLIDO2 = 'Sánchez' WHERE CO_CLIENTE = 10 Un aspecto a tener en cuenta, sobre todo si has trabajado con ORACLE, es que SQL graba los cambios inmediatamente sin necesidad de hacer COMMIT. Por supuesto podemos gestionar nosotros las transacciones pero es algo que hay que hacer de forma explícita con la instrucción BEGIN TRAN y que se verá en capítulos posteriores de este tutorial.

B. C. D. E. F. G. H.

Update INNER JOIN

En ocasiones queremos actualizar los datos de una tabla con los datos de otra (muy común para des normalizar un modelo de datos). Habitualmente, usamos subconsultas para este propósito, pero Transact SQL permite la utilización de la sentencia UPDATE INNER JOIN.

UPDATE CLIENTES SET NOMBRE = FICHERO_CLIENTES.NOMBRE, APELLIDO1 = FICHERO_CLIENTES.APELLIDO1, APELLIDO2 = FICHERO_CLIENTES.APELLIDO2 FROM CLIENTES INNER JOIN FICHERO_CLIENTES ON FICHERO_CLIENTES.CO_CLIENTE = CLIENTES.CO_CLIENTE

I.

Clausula OUTPUT

A partir de la versión de SQL Server 2005 disponemos de la cláusula OUTPUT para recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y DELETED. Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o DELETE. Es decir, son una copia de los datos "antes" del cambio. DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT.

DECLARE @FILAS_ACTUALIZADAS TABLE ( CO_CLIENTE int , NOMBRE varchar(100), APELLIDO1 varchar(100), APELLIDO2 varchar(100) )

UPDATE CLIENTES SET NOMBRE = 'Microsoft', APELLIDO1 = 'Herrarte',

APELLIDO2 = 'Sánchez' OUTPUT DELETED.* INTO @FILAS_ACTUALIZADAS WHERE CO_CLIENTE IN (10, 11, 12)

SELECT * FROM @FILAS_ACTUALIZADAS Las columnas con prefijo INSERTED reflejan el valor después de que se complete la instrucción UPDATE o INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copia de los datos "después" del cambio. INSERTED no se puede utilizar con la cláusula OUTPUT en la instrucción DELETE. DECLARE @FILAS_ACTUALIZADAS TABLE ( CO_CLIENTE int , NOMBRE varchar(100), APELLIDO1 varchar(100), APELLIDO2 varchar(100) ) UPDATE CLIENTES SET NOMBRE = 'Microsoft', APELLIDO1 = 'Herrarte', APELLIDO2 = 'Sánchez' OUTPUT INSERTED.* INTO @FILAS_ACTUALIZADAS WHERE CO_CLIENTE IN (10, 11, 12)

SELECT * FROM @FILAS_ACTUALIZADAS

Borrar datos en Transact SQL Delete Para borrar datos de una tabla debemos utilizar la sentencia DELETE.

Para ejecutar los ejemplos de este capítulo debemos ejecutar el siguiente script, que crea la tabla "DATOS" y carga registros en ella.

CREATE TABLE DATOS ( Id int identity not null, dato varchar(100), fx_alta datetime, constraint PK_DATOS PRIMARY KEY (Id)) GO DECLARE @i int, @dato varchar(100) set @i = 0 WHILE (@i <100) BEGIN SET @i = @i +1 set @dato = 'Dato:' + cast(@i as varchar) INSERT INTO DATOS (dato, fx_alta) VALUES (@dato, getdate()) END GO SELECT * from DATOS

Para borrar los registros de la tabla "DATOS" ejecutaremos la siguiente instrucción. Nótese que no se especifica ninguna condición WHERE por lo que se borran todos los datos de la tabla.

DELETE

FROM DATOS Lógicamente podemos especificar que registros queremos borrar a través de la cláusula WHERE.

DELETE FROM DATOS WHERE Id=12 Cuando borramos datos de una tabla, podemos obtener el número de filas que han sido afectadas por la instrucción a través de la variable @@RowCount. El siguiente ejemplo ilustra el uso de @@RowCount.

DELETE FROM DATOS WHERE Id=17

SELECT @@ROWCOUNT

Clausula OUTPUT A partir de la versión de SQL Server 2005 disponemos de la cláusula OUTPUT para recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y DELETED. Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o DELETE. Es decir, son una copia de los datos "antes" del cambio.

DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT.

DECLARE @FILAS_BORRADAS TABLE ( Id int, dato varchar(100), fx_alta datetime )

DELETE FROM DATOS OUTPUT DELETED.* INTO @FILAS_BORRADAS WHERE Id=17

SELECT * from @FILAS_BORRADAS

Truncate Table Para borrar datos de forma masiva disponemos de la instrucción TRUNCATE TABLE, que borra todos los datos de una tabla.

TRUNCATE TABLE DATOS Cuando trabajamos con TRUNCATE TABLE debemos tener en cuenta las siguientes consideraciones:  

TRUNCATE TABLE no admite la cláusula WHERE. No podemos ejecutar TRUNCATE TABLE sobre tablas que sean "padres" en foreign keys.

Transacciones en Transact SQL Concepto de transacción Una transacción es un conjunto de operaciones Transact SQL que se ejecutan como un único bloque, es decir, si falla una operación Transact SQL fallan todas. Si una transacción tiene éxito, todas las modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte permanente de la base de datos. Si una transacción encuentra errores y debe cancelarse o revertirse, se borran todas las modificaciones de los datos. El ejemplo clásico de transacción es una transferencia bancaria, en la que quitamos saldo a una cuenta y lo añadimos en otra. Si no somos capaces de abonar el dinero en la cuenta de destino, no debemos quitarlo de la cuenta de origen. SQL Server funciona por defecto con Transacciones de confirmación automática, es decir, cada instrucción individual es una transacción y se confirma automáticamente. Sobre el ejemplo anterior de la transferencia bancaria, un script debería realizar algo parecido a los siguiente:

DECLARE @importe DECIMAL(18,2), @CuentaOrigen VARCHAR(12), @CuentaDestino VARCHAR(12) /* Asignamos el importe de la transferencia * y las cuentas de origen y destino */ SET @importe = 50 SET @CuentaOrigen

= '200700000001'

SET @CuentaDestino = '200700000002'

/* Descontamos el importe de la cuenta origen */ UPDATE CUENTAS SET SALDO = SALDO - @importe WHERE NUMCUENTA = @CuentaOrigen

/* Registramos el movimiento */ INSERT INTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO + @importe, SALDO, @importe, getdate() FROM CUENTAS WHERE NUMCUENTA = @CuentaOrigen

/* Incrementamos el importe de la cuenta destino */ UPDATE CUENTAS SET SALDO = SALDO + @importe WHERE NUMCUENTA = @CuentaDestino

/* Registramos el movimiento */ INSERT INTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO - @importe, SALDO, @importe, getdate() FROM CUENTAS WHERE NUMCUENTA = @CuentaDestino

Esta forma de actuar seria errónea, ya que cada instrucción se ejecutaría y confirmaría de forma independiente, por lo que un error dejaría los datos erróneos en la base de datos ( ¡y ese es el peor error que nos podemos encontrar! )

Transacciones implícitas y explicitas Para agrupar varias sentencias Transact SQL en una única transacción, disponemos de los siguientes métodos: 

Transacciones explícitas



Transacciones implícitas

Cada transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se termina explícitamente con una instrucción COMMIT o ROLLBACK. Se inicia automáticamente una nueva transacción cuando se ejecuta una instrucción que realiza modificaciones en los datos, pero cada transacción se completa explícitamente con una instrucción COMMIT o ROLLBACK.

Para activar-desactivar el modo de transacciones implícitas debemos ejecutar la siguiente instrucción.

--Activamos el modo de transacciones implícitas SET IMPLICIT_TRANSACTIONS ON

--Desactivamos el modo de transacciones implícitas SET IMPLICIT_TRANSACTIONS OFF Cuando la opción ANSI_DEFAULTS está establecida en ON, IMPLICIT_TRANSACTIONS también se establece en ON. El siguiente ejemplo muestra el script anterior haciendo uso de transacciones explicitas.

DECLARE @importe DECIMAL(18,2), @CuentaOrigen VARCHAR(12), @CuentaDestino VARCHAR(12)

/* Asignamos el importe de la transferencia * y las cuentas de origen y destino */ SET @importe = 50

SET @CuentaOrigen = '200700000002' SET @CuentaDestino = '200700000001'

BEGIN TRANSACTION -- O solo BEGIN TRAN BEGIN TRY /* Descontamos el importe de la cuenta origen */ UPDATE CUENTAS SET SALDO = SALDO - @importe WHERE NUMCUENTA = @CuentaOrigen

/* Registramos el movimiento */ INSERT INTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO + @importe, SALDO, @importe, getdate() FROM CUENTAS WHERE NUMCUENTA = @CuentaOrigen

/* Incrementamos el importe de la cuenta destino */ UPDATE CUENTAS SET SALDO = SALDO + @importe WHERE NUMCUENTA = @CuentaDestino

/* Registramos el movimiento */

INSERT INTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO - @importe, SALDO, @importe, getdate() FROM CUENTAS WHERE NUMCUENTA = @CuentaDestino

/* Confirmamos la transaccion*/ COMMIT TRANSACTION -- O solo COMMIT

END TRY BEGIN CATCH /* Hay un error, deshacemos los cambios*/ ROLLBACK TRANSACTION -- O solo ROLLBACK PRINT 'Se ha producido un error!' END CATCH

El siguiente ejemplo muestra el mismo script con transacciones implícitas.

SET IMPLICIT_TRANSACTIONS ON

DECLARE @importe DECIMAL(18,2), @CuentaOrigen VARCHAR(12),

@CuentaDestino VARCHAR(12)

/* Asignamos el importe de la transferencia * y las cuentas de origen y destino */ SET @importe = 50 SET @CuentaOrigen = '200700000002' SET @CuentaDestino = '200700000001'

BEGIN TRY /* Descontamos el importe de la cuenta origen */ UPDATE CUENTAS SET SALDO = SALDO - @importe WHERE NUMCUENTA = @CuentaOrigen

/* Registramos el movimiento */ INSERT INTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO + @importe, SALDO, @importe, getdate() FROM CUENTAS WHERE NUMCUENTA = @CuentaOrigen

/* Incrementamos el importe de la cuenta destino */

UPDATE CUENTAS SET SALDO = SALDO + @importe WHERE NUMCUENTA = @CuentaDestino

/* Registramos el movimiento */ INSERT INTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO) SELECT IDCUENTA, SALDO - @importe, SALDO, @importe, getdate() FROM CUENTAS WHERE NUMCUENTA = @CuentaDestino

/* Confirmamos la transaccion*/ COMMIT TRANSACTION -- O solo COMMIT END TRY BEGIN CATCH /* Hay un error, deshacemos los cambios*/ ROLLBACK TRANSACTION -- O solo ROLLBACK PRINT 'Se ha producido un error!' END CATCH

La transacción sigue activa hasta que emita una instrucción COMMIT o ROLLBACK. Una vez que la primera transacción se ha confirmado o revertido, se inicia automáticamente una nueva transacción la siguiente vez que la conexión ejecuta una instrucción para modificar datos. La conexión continúa generando transacciones implícitas hasta que se desactiva el modo de transacciones implícitas.

Podemos verificar el número de transacciones activas a través de @@TRANCOUNT.

SET IMPLICIT_TRANSACTIONS ON BEGIN TRY UPDATE CUENTAS SET FXALTA = FXALTA - 1 PRINT @@TRANCOUNT COMMIT END TRY BEGIN CATCH ROLLBACK PRINT 'Error' END CATCH Otro punto a tener en cuenta cuando trabajamos con transacciones son los bloqueos y el nivel de aislamiento. Podemos aprender más sobre bloqueos y nivel de aislamiento en este artículo.

Transacciones anidadas. Podemos anidar varias transacciones. Cuando anidamos varias transacciones la instrucción COMMIT afectará a la última transacción abierta, pero ROLLBACK afectará a todas las transacciones abiertas. Un hecho a tener en cuenta, es que, si hacemos ROLLBACK de la transacción superior se desharán también los cambios de todas las transacciones internas, aunque hayamos realizado COMMIT de ellas.

BEGIN TRAN

UPDATE EMPLEADOS SET NOMBRE = 'Microsoft' WHERE ID=101

BEGIN TRAN

UPDATE EMPLEADOS SET APELLIDO1 = 'Microsoft.COM' WHERE ID=101

-- Este COMMIT solo afecta a la segunda transacción. COMMIT

-- Este ROLLBACK afecta a las dos transacciones. ROLLBACK

Una consideración a tener en cuanta cuando trabajamos con transacciones anidadas es la posibilidad de utilizar puntos de guardado o SAVEPOINTs.

Puntos de recuperacion (SavePoint). Los puntos de recuperación (SavePoints) permiten manejar las transacciones por pasos, pudiendo hacer rollbacks hasta un punto marcado por el savepoint y no por toda la transacción. El siguiente ejemplo muestra cómo trabajar con puntos de recuperación.

BEGIN TRAN

UPDATE EMPLEADOS SET NOMBRE = 'Microsoft' WHERE ID=101

UPDATE EMPLEADOS SET APELLIDO1 = 'Microsoft.COM' WHERE ID=101

SAVE TRANSACTION P1 -- Guardamos la transacción (Savepoint)

UPDATE EMPLEADOS SET APELLIDO1 = 'Otra cosa!' WHERE ID=101

-- Este ROLLBACK afecta solo a las instrucciones -- posteriores al savepoint P1. ROLLBACK TRANSACTION P1

-- Confirmamos la transacción COMMIT

III.

Procedimientos almacenados en Transact SQL

Un procedimiento es un programa dentro de la base de datos que ejecuta una acción o conjunto de acciones específicas. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código. En Transact SQL los procedimientos almacenados pueden devolver valores (numérico entero) o conjuntos de resultados. Para crear un procedimiento almacenado debemos emplear la sentencia CREATE PROCEDURE.

CREATE PROCEDURE [@param1 , ...] AS -- Sentencias del procedure Para modificar un procedimiento almacenado debemos emplear la sentencia ALTER PROCEDURE.

ALTER PROCEDURE [@param1 , ...] AS -- Sentencias del procedure

El siguiente ejemplo muestra un procedimiento almacenado, denominado spu_addCliente que inserta un registro en la tabla "CLIENTES".

CREATE PROCEDURE spu_addCliente @nombre varchar(100), @apellido1 varchar(100), @apellido2 varchar(100), @nifCif varchar(20), @fxNaciento datetime

AS INSERT INTO CLIENTES (nombre, apellido1, apellido2, nifcif, fxnacimiento) VALUES (@nombre, @apellido1, @apellido2, @nifCif, @fxNaciento)

Para la ejecutar un procedimiento almacenado debemos utilizar la sentencia EXEC. Cuando la ejecución del procedimiento almacenado es la primera instrucción del lote, podemos omitir el uso de EXEC. El siguiente ejemplo muestra la ejecución del procedimiento almacenado anterior.

DECLARE @fecha_nacimiento datetime set @fecha_nacimiento = convert(datetime, '13/05/1975', 103) EXEC spu_addCliente 'Pedro', 'Herrarte', 'Sanchez', '00000002323', @fecha_nacimiento Siempre es deseable que las instrucciones del procedure esten dentro de un bloque TRY CATCH y controlados por una transacción.

ALTER PROCEDURE spu_addCliente

@nombre varchar(100), @apellido1 varchar(100), @apellido2 varchar(100), @nifCif varchar(20), @fxNaciento datetime

AS BEGIN TRY BEGIN TRAN INSERT INTO CLIENTES (nombre, apellido1, apellido2, nifcif, fxnacimiento) VALUES

(@nombre, @apellido1, @apellido2, @nifCif, @fxNaciento) COMMIT END TRY BEGIN CATCH ROLLBACK PRINT ERROR_MESSAGE() END CATCH Si queremos que los parámetros de un procedimiento almacenado sean de entrada-salida debemos especificarlo a través de la palabra clave OUTPUT , tanto en la definición del procedure como en la ejecución. El siguiente ejemplo muestra la definición de un procedure con parámetros de salida.

CREATE PROCEDURE spu_ObtenerSaldoCuenta @numCuenta varchar(20), @saldo decimal(10,2) output AS BEGIN SELECT @saldo = SALDO FROM CUENTAS WHERE NUMCUENTA = @numCuenta END

Y para ejecutar este procedure:

DECLARE @saldo decimal(10,2) EXEC spu_ObtenerSaldoCuenta '200700000001', @saldo output PRINT @saldo

Un procedimiento almacenado puede devolver valores numéricos enteros a través de la instrucción RETURN. Normalmente debemos utilizar los valores de retorno para determinar si la ejecución del procedimiento ha sido correcta o no. Si queremos obtener valores se recomienda utilizar parámetros de salida o funciones escalares (se verán más adelante en este tutorial). El siguiente ejemplo muestra un procedimiento almacenado que devuelve valores.

CREATE PROCEDURE spu_EstaEnNumerosRojos @numCuenta varchar(20) AS BEGIN IF (SELECT SALDO FROM CUENTAS WHERE NUMCUENTA = @numCuenta) < 0 BEGIN RETURN 1 END ELSE RETURN 0 END El siguiente ejemplo muestra como ejecutar el procedure y obtener el valor devuelto.

DECLARE @rv int EXEC @rv = spu_EstaEnNumerosRojos '200700000001' PRINT @rv

Otra característica muy interesante de los procedimientos almacenados en Transact SQL es que pueden devolver uno o varios conjuntos de resultados. El siguiente ejemplo muestra un procedimiento almacenado que devuelve un conjunto de resultados.

CREATE PROCEDURE spu_MovimientosCuenta @numCuenta varchar(20) AS BEGIN SELECT

@numCuenta, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO

FROM MOVIMIENTOS INNER JOIN CUENTAS ON MOVIMIENTOS.IDCUENTA = CUENTAS.IDCUENTA WHERE NUMCUENTA = @numCuenta ORDER BY FXMOVIMIENTO DESC END La ejecución del procedimiento se realiza normalmente.

EXEC spu_MovimientosCuenta '200700000001' El resultado de la ejecución...

NUMCUENTA ---------------------200700000001 16:18:36.490 200700000001 16:20:41.183 200700000001

SALDO_ANTERIOR SALDO_POSTERIOR IMPORTE FXMOVIMIENTO -------------- ---------------- ------- -----------50.99

100.99

50.00

2007-08-25

0.99

50.99

50.00

2007-08-23

50.99

0.99

50.00

2007-08-23

Funciones en Transact SQL SQL Server proporciona al usuario la posibilidad de definir sus propias funciones, conocidas como UDF (user defined functions). Exisiten tres tipos de funciones. Estas son:   

Funciones escalares. Funciones en línea. Funciones en línea de multiples sentencias

Funciones escalares Las funciones escalares devuelven un único valor de cualquier tipo de los datos tal como int, money, varchar, real, etc. La sintaxis para una función escalar es la siguiente:

CREATE FUNCTION ( -- Lista de parámetros <@Param1, sysname, @p1> , ... ) -- Tipo de datos que devuelve la función. RETURNS AS BEGIN ... END

El siguiente ejemplo muestra cómo crear una función escalar.

CREATE FUNCTION fn_MultiplicaSaldo ( @NumCuenta VARCHAR(20), @Multiplicador DECIMAL(10,2) ) RETURNS DECIMAL(10,2) AS BEGIN DECLARE @Saldo DECIMAL(10,2), @Return DECIMAL(10,2)

SELECT @Saldo = SALDO FROM CUENTAS WHERE NUMCUENTA = @NumCuenta

SET @Return = @Saldo * @Multiplicador

RETURN @Return END Pueden ser utilizadas en cualquier sentencia Transact SQL. Un aspecto a tener en cuenta, es que para utilizar una función escalar debemos identificar el nombre de la función con el propietario de la misma.

El siguiente ejemplo muestra cómo utilizar la función anteriormente creada en una sentencia Transact SQL. Un aspecto muy a tener en cuenta es que la función ejecutará sus sentencias SELECT una vez por cada fila del conjunto de resultados devuelto por la consulta SELECT principal.

SELECT

IDCUENTA, NUMCUENTA, SALDO, FXALTA, -- Ejecucion de la función:

dbo.fn_MultiplicaSaldo( NUMCUENTA, IDCUENTA) AS RESULTADO FROM CUENTAS El siguiente ejemplo muestra cómo utilizar una función escalar en un script Transact SQL.

DECLARE @NumCuenta VARCHAR(20), @Resultado DECIMAL(10,2)

SET @NumCuenta = '200700000001' SET @Resultado = dbo.fn_MultiplicaSaldo(@NumCuenta, 30.5)

PRINT @Resultado Las funciones escalares son muy similares a procedimientos almacenados con parámetros de salida, pero estas pueden ser utilizadas en consultas de selección y en la cláusula where de las mismas. Las funciones no pueden ejecutar sentencias INSERT o UPDATE.

Funciones en línea Las funciones en línea son las funciones que devuelven un conjunto de resultados correspondientes a la ejecución de una sentencia SELECT. La sintaxis para una función de tabla en línea es la siguiente:

CREATE FUNCTION ( -- Lista de parámetros <@param1, sysname, @p1> ,... ) RETURNS TABLE AS RETURN ( -- Sentencia Transact SQL ) El siguiente ejemplo muestra cómo crear una función en línea.

CREATE FUNCTION fn_MovimientosCuenta ( @NumCuenta VARCHAR(20) ) RETURNS TABLE AS RETURN (

SELECT MOVIMIENTOS.* FROM MOVIMIENTOS INNER JOIN CUENTAS ON MOVIMIENTOS.IDCUENTA = CUENTAS.IDCUENTA WHERE CUENTAS.NUMCUENTA = @NumCuenta ) No podemos utilizar la cláusula ORDER BY en la sentencia de una función el línea. Las funciones en línea pueden utilizarse dentro de joins o querys como si fueran una tabla normal.

SELECT * FROM fn_MovimientosCuenta('200700000001') SELECT * FROM CUENTAS INNER JOIN CUENTAS_CLIENTE ON CUENTAS_CLIENTE.IDCUENTA = CUENTAS.IDCUENTA INNER JOIN CLIENTES ON CLIENTES.id = CUENTAS_CLIENTE.IDCLIENTE INNER JOIN fn_MovimientosCuenta('200700000001') A ON A.IDCUENTA= CUENTAS.IDCUENTA

Funciones en línea de múltiples sentencias Las funciones en línea de múltiples sentencias son similares a las funciones en línea excepto que el conjunto de resultados que devuelven puede estar compuesto por la ejecución de varias consultas SELECT. Este tipo de función se usa en situaciones donde se requiere una mayor lógica de proceso. La sintaxis para una funciones de tabla de multi sentencias es la siguiente:

CREATE FUNCTION ( -- Lista de parámetros <@param1, sysname, @p1> , ... ) RETURNS -- variable de tipo tabla y su estructura <@Table_Variable_Name, sysname, @Table_Var> TABLE ( , ) AS BEGIN -- Sentencias que cargan de datos la tabla declarada RETURN END

El siguiente ejemplo muestra el uso de una función de tabla de multi sentencias.

/* Esta función busca la tres cuentas con mayor saldo * y obtiene los tres últimos movimientos de cada una * de estas cuentas */

CREATE FUNCTION fn_CuentaMovimietos() RETURNS @datos TABLE ( -- Estructura de la tabla que devuelve la funcion. NumCuenta varchar(20), Saldo decimal(10,2), Saldo_anterior decimal(10,2), Saldo_posterior decimal(10,2), Importe_Movimiento decimal(10,2), FxMovimiento datetime ) AS BEGIN -- Variables necesarias para la lógica de la función. DECLARE @idcuenta int, @numcuenta varchar(20), @saldo decimal(10,2)

-- Cursor con las 3 cuentas de mayor saldo

DECLARE CDATOS CURSOR FOR SELECT TOP 3 IDCUENTA, NUMCUENTA, SALDO FROM CUENTAS ORDER BY SALDO DESC

OPEN CDATOS FETCH CDATOS INTO @idcuenta, @numcuenta, @saldo

-- Recorremos el cursor WHILE (@@FETCH_STATUS = 0) BEGIN -- Insertamos la cuenta en la variable de salida INSERT INTO @datos (NumCuenta, Saldo) VALUES (@numcuenta, @saldo) -- Insertamos los tres últimos movimientos de la cuenta INSERT INTO @datos (Saldo_anterior, Saldo_posterior, Importe_Movimiento, FxMovimiento ) SELECT TOP 3 SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO FROM MOVIMIENTOS WHERE IDCUENTA = @idcuenta ORDER BY FXMOVIMIENTO DESC

-- Vamos a la siguiente cuenta FETCH CDATOS INTO @idcuenta, @numcuenta, @saldo END

CLOSE CDATOS; DEALLOCATE CDATOS;

RETURN END Para ejecutar la función:

select * from fn_CuentaMovimietos() Y el resultado obtenido ... NumCuenta -----------200700000002 NULL NULL NULL 200700000001 NULL NULL NULL

Saldo -----500.00 NULL NULL NULL 100.99 NULL NULL NULL

Saldo_anterior --------------NULL 550.00 600.00 600.00 NULL 50.99 0.99 50.99

Saldo_posterior ---------------NULL 500.00 550.00 550.00 NULL 100.99 50.99 0.99

Importe_Movimiento ------------------NULL 50.00 50.00 50.00 NULL 50.00 50.00 50.00

FxMovimiento ----------------------NULL 2007-08-25 16:18:36.490 2007-08-23 16:20:41.183 2007-08-23 16:14:05.900 NULL 2007-08-25 16:18:36.490 2007-08-23 16:20:41.183 2007-08-23 16:16:29.840

Funciones integradas de Transact SQL (I) SQL Server pone a nuestra disposición multitud de funciones predefinidas que proporcionan un amplio abanico de posibilidades. Mostramos aquí algunas de las frecuentes. Podemos acceder al listado completo a través del siguiente enlace: http://technet.microsoft.com/es-es/library/ms187786.aspx

Cast y Convert Convierten una expresión de un tipo de datos en otro de forma explícita. CAST y CONVERT proporcionan funciones similares. CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) Dónde:   

data_type, es el tipo de destino al que queremos convertir la expresión expresión, la expresión que queremos convertir style, parametro opcional que especifica el formato que tiene expresión. Por ejemplo, si queremos convertir un varchar a datetime, aquí debemos especificar el formato de la fecha (el tipo varchar).

DECLARE @fecha varchar(20) -- Convertimos un valor varchar a datetime -- El 103 indica el formato en el que está escrita la fecha -- 103 => dd/mm/aa SET @fecha = CONVERT(datetime, '19/03/2008',103)

SELECT @fecha

DECLARE @fecha datetime, @fechaFormateada varchar(20) -- Convertimos ahora una fecha a varchar y la formateamos -- 3 => dd/mm/aa

SET @fecha = GETDATE() SET @fechaFormateada = CONVERT(varchar(20), @fecha, 3)

SELECT @fechaFormateada

-- Un ejemplo utilizando CAST DECLARE @dato varchar(2), @dato2 int SET @dato = '27' SET @dato2 = cast(@dato AS int)

SELECT @dato2 A continuación mostramos la tabla de códigos de estilo (obtenida de MicroSoft). Sin el siglo (aa) (1)

Con el siglo ?(aaaa)

Estándar

Entrada/salida (3)

-

0 o 100 (1, 2)

Valor predeterminado

mes dd aaaa hh:mia.m. (o p. m.)

1 2 3 4 5 6

101 102 103 104 105 106

EE.UU. ANSI Británico/Francés Alemán Italiano -

mm/dd/aaaa aa.mm.dd dd/mm/aa dd.mm.aa dd-mm-aa dd mes aa

7 8

107 108

-

Mes dd, aa hh:mi:ss

-

9 o 109 (1, 2)

10

110

Valor predeterminado + milisegundos EE.UU.

mes dd aaaa hh:mi:ss:mmma.m. (o p. m.) mm-dd-aa

11 12

111 112

JAPÓN ISO

aa/mm/dd aammdd

-

13 o 113 (1, 2)

14 -

114 20 o 120 (2) 21 o 121 (2)

Europeo predeterminado + milisegundos ODBC canónico ODBC canónico (con

(1) (1)

dd mes aaaa hh:mi:ss:mmm(24h) hh:mi:ss:mmm(24h) aaaa-mm-dd hh:mi:ss(24h) aaaa-mm-dd hh:mi:ss.mmm(24h)

milisegundos) -

126 (4)

ISO8601

127(6, 7)

ISO8601 con zona horaria Z.

1, 2

-

5

130 ( ) 131 (2)

Hijri ( ) Hijri (5)

aaaa-mm-ddThh:mi:ss.mmm (sin espacios) aaaa-mm-ddThh:mi:ss.mmmZ (sin espacios) dd mes aaaa hh:mi:ss:mmma.m. dd/mm/aa hh:mi:ss:mmma.m.

Isnull Evalua una expresion de entrado y si esta es NULL, reemplaza NULL con el valor de reemplazo especificado. El valor de reemplazo debe ser del mismo tipo de datos que la expresion a evaluar. ISNULL ( expression , replacement_value )

DECLARE @datoInt int, @datoVarchar varchar(100)

SET @datoInt = NULL SET @datoVarchar = NULL

SELECT

ISNULL(@dato, -1), ISNULL(@datoVarchar, 'No hay dato')

COALESCE Devuelve la primera expresión distinta de NULL entre sus argumentos. Un aspecto a tener en cuenta es que todos los argumentos deben ser del mismo tipo. COALESCE ( expression [ ,...n ] )

DECLARE @dato1 int, @dato2 int,

@dato3 int, @dato4 int, @dato5 int

SET @dato1 = null SET @dato2 = NULL SET @dato3 = NULL SET @dato4 = 100 SET @dato5 = 125

-- Devuelve 100 SELECT COALESCE(@dato1,@dato2,@dato3,@dato4,@dato5)

GetDate y GetUTCDate GetDate devuelve la fecha y hora actuales del sistema en el formato interno estándar de SQL Server 2005 para los valores datetime. GetUTCDate devuelve el valor datetime que representa la hora UTC (hora universal coordinada u hora del meridiano de Greenwich) actual.

DECLARE @fechaLocal datetime, @fechaUTC datetime

SET @fechaLocal = getdate() SET @fechaUTC = GETUTCDATE()

SELECT @fechaLocal, @fechaUTC

Triggers en Transact SQL Un trigger( o desencadenador) es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento en el servidor de bases de datos. SQL Server proporciona los siguientes tipos de triggers:  

Trigger DML, se ejecutan cuando un usuario intenta modificar datos mediante un evento de

lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista. Trigger DDL, se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL.

Trigger DML. Los trigger DML se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista. La sintaxis general de un trigger es la siguiente.

CREATE TRIGGER ON AFTER AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here END Antes de ver un ejemplo es necesario conocer las tablas inserted y deleted. Las instrucciones de triggers DML utilizan dos tablas especiales denominadas inserted y deleted. SQL Server 2005 crea y administra automáticamente ambas tablas. La estructura de las tablas inserted y

deleted es la misma que tiene la tabla que ha desencadenado la ejecución del trigger. La primera tabla (inserted) solo está disponible en las operaciones INSERT y UPDATE y en ella están los valores resultantes despues de la inserción o actualización. Es decir, los datos insertados. Inserted estará vacia en una operación DELETE. En la segunda (deleted), disponible en las operaciones UPDATE y DELETE, están los valores anteriores a la ejecución de la actualización o borrado. Es decir, los datos que serán borrados. Deleted estará vacia en una operacion INSERT. ¿No existe una tabla UPDATED? No, hacer una actualización es lo mismo que borrar (deleted) e insertar los nuevos (inserted). La sentencia UPDATE es la única en la que inserted y deleted tienen datos simultaneamente. No pueden se modificar directamente los datos de estas tablas. El siguiente ejemplo, graba un histórico de saldos cada vez que se modifica un saldo de la tabla cuentas.

CREATE TRIGGER TR_CUENTAS ON CUENTAS AFTER UPDATE AS BEGIN -- SET NOCOUNT ON impide que se generen mensajes de texto -- con cada instrucción SET NOCOUNT ON; INSERT INTO HCO_SALDOS (IDCUENTA, SALDO, FXSALDO) SELECT IDCUENTA, SALDO, getdate() FROM INSERTED END

La siguiente instrucción provocará que el trigger se ejecute:

UPDATE CUENTAS SET SALDO = SALDO + 10 WHERE IDCUENTA = 1 Una consideración a tener en cuenta es que el trigger se ejecutará aunque la instruccion DML (UPDATE, INSERT o DELETE ) no haya afectado a ninguna fila. En este caso inserted y deleted devolveran un conjunto de datos vacio. Podemos especificar a qué columnas de la tabla debe afectar el trigger.

ALTER TRIGGER TR_CUENTAS ON CUENTAS AFTER UPDATE AS BEGIN -- SET NOCOUNT ON impide que se generen mensajes de texto -- con cada instrucción SET NOCOUNT ON;

IF UPDATE(SALDO) -- Solo si se actualiza SALDO BEGIN INSERT INTO HCO_SALDOS (IDCUENTA, SALDO, FXSALDO) SELECT IDCUENTA, SALDO, getdate() FROM INSERTED END END

Los trigger están dentro de la transacción original (Insert, Delete o Update) por lo cual si dentro de nuestro trigger hacemos un RollBack Tran, no solo estaremos echando atrás nuestro trigger sino también toda la transacción; en otras palabras si en un trigger ponemos un RollBack Tran, la transacción de Insert, Delete o Update volverá toda hacia atrás.

ALTER TRIGGER TR_CUENTAS ON CUENTAS AFTER UPDATE AS BEGIN -- SET NOCOUNT ON impide que se generen mensajes de texto -- con cada instrucción SET NOCOUNT ON; INSERT INTO HCO_SALDOS (IDCUENTA, SALDO, FXSALDO) SELECT IDCUENTA, SALDO, getdate() FROM INSERTED

ROLLBACK END

En este caso obtendremos el siguiente mensaje de error: La transacción terminó en el desencadenador. Se anuló el lote. Podemos activar y desactivar Triggers a través de las siguientes instrucciones.

-- Desactiva el trigger TR_CUENTAS DISABLE TRIGGER TR_CUENTAS ON CUENTAS GO -- activa el trigger TR_CUENTAS ENABLE TRIGGER TR_CUENTAS ON CUENTAS GO -- Desactiva todos los trigger de la tabla CUENTAS ALTER TABLE CUENTAS DISABLE TRIGGER ALL GO -- Activa todos los trigger de la tabla CUENTAS ALTER TABLE CUENTAS ENABLE TRIGGER ALL

Trigger DDL Los trigger DDL se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de TransactSQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL. La sintaxis general de un trigger es la siguiente.

CREATE TRIGGER ON DATABASE FOR AS BEGIN ... END La siguiente instrucción impide que se ejecuten sentencias DROP TABLE y ALTER TABLE en la base de datos.

CREATE TRIGGER TR_SEGURIDAD ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN RAISERROR ('No está permitido borrar ni modificar tablas !' , 16, 1) ROLLBACK TRANSACTION END

Cursores en Transact SQL Un cursor es una variable que nos permite recorrer con un conjunto de resultados obtenido a través de una sentencia SELECT fila a fila. Cuando trabajemos con cursores debemos seguir los siguientes pasos.     

Declarar el cursor, utilizando DECLARE Abrir el cursor, utilizando OPEN Leer los datos del cursor, utilizando FETCH ... INTO Cerrar el cursor, utilizando CLOSE Liberar el cursor, utilizando DEALLOCATE

La sintaxis general para trabajar con un cursor es la siguiente.

-- Declaración del cursor DECLARE CURSOR FOR

-- apertura del cursor OPEN

-- Lectura de la primera fila del cursor FETCH INTO

WHILE (@@FETCH_STATUS = 0) BEGIN -- Lectura de la siguiente fila de un cursor FETCH INTO ...

END -- Fin del bucle WHILE

-- Cierra el cursor CLOSE -- Libera los recursos del cursor DEALLOCATE

El siguiente ejemplo muestra el uso de un cursor.

-- Declaracion de variables para el cursor DECLARE @Id int, @Nombre varchar(255), @Apellido1 varchar(255), @Apellido2 varchar(255), @NifCif varchar(20), @FxNacimiento datetime

-- Declaración del cursor DECLARE cClientes CURSOR FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES -- Apertura del cursor OPEN cClientes -- Lectura de la primera fila del cursor FETCH cClientes INTO

@id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento

WHILE (@@FETCH_STATUS = 0 ) BEGIN PRINT @Nombre + ' ' + @Apellido1 + ' ' + @Apellido2 -- Lectura de la siguiente fila del cursor FETCH cClientes INTO

@id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento

END

-- Cierre del cursor CLOSE cClientes -- Liberar los recursos DEALLOCATE cClientes

Cuando trabajamos con cursores, la función @@FETCH_STATUS nos indica el estado de la última instrucción FETCH emitida, los valores posibles son: Valor devuelto

0 -1 -2

Descripción

La instrucción FETCH se ejecutó correctamente. La instrucción FETCH no se ejecutó correctamente o la fila estaba más allá del conjunto de resultados. Falta la fila recuperada.

En la apertura del cursor, podemos especificar los siguientes parámetros:

DECLARE CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR El primer conjunto de parámetros que podemos especificar es [ LOCAL | GLOBAL ]. A continuación mostramos el significado de cada una de estas opciones. 

LOCAL

Específica que el ámbito del cursor es local para el proceso por lotes, procedimiento almacenado o desencadenador en que se creó el cursor.

DECLARE cClientes CURSOR LOCAL FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES 

GLOBAL

Especifica que el ámbito del cursor es global para la conexión. Puede hacerse referencia al nombre del cursor en cualquier procedimiento almacenado o proceso por lotes que se ejecute en la conexión.

DECLARE cClientes CURSOR GLOBAL FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES Si no se especifica GLOBAL ni LOCAL, el valor predeterminado se controla mediante la configuración de la opción de base de datos default to local cursor.

El siguiente conjunto de parámetros que podemos especificar es [ FORWARD_ONLY | SCROLL ]. A continuación mostramos el significado de cada una de estas opciones. 

FORWARD_ONLY Especifica que el cursor sólo se puede desplazar de la primera a la última fila. FETCH NEXT es la única opción de recuperación admitida.

DECLARE cClientes CURSOR FORWARD_ONLY FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES



SCROLL Especifica que están disponibles todas las opciones de recuperación (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Si no se especifica SCROLL en una instrucción DECLARE CURSOR la única opción de recuperación que se admite es NEXT. No es posible especificar SCROLL si se incluye también FAST_FORWARD. Si se incluye la opción SCROLL, la forma en la realizamos la lectura del cursor varia, debiendo utilizar la siguiente sintaxis: FETCH [ NEXT | PRIOR | FIRST | LAST | RELATIVE | ABSOLUTE ] FROM < INTO

-- Declaracion de variables para el cursor DECLARE @Id int, @Nombre varchar(255), @Apellido1 varchar(255), @Apellido2 varchar(255), @NifCif varchar(20), @FxNacimiento datetime

-- Declaración del cursor DECLARE cClientes CURSOR SCROLL FOR

SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES -- Apertura del cursor OPEN cClientes -- Lectura de la primera fila del cursor FETCH NEXT FROM cClientes INTO @id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento

WHILE (@@FETCH_STATUS = 0 ) BEGIN PRINT @Nombre + ' ' + @Apellido1 + ' ' + @Apellido2 -- Lectura de la siguiente fila del cursor FETCH NEXT FROM cClientes INTO @id,@Nombre,@Apellido1,@Apellido2,@NifCif,@FxNacimiento END -- Lectura de la fila anterior FETCH PRIOR FROM cClientes INTO @id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento PRINT @Nombre + ' ' + @Apellido1 + ' ' + @Apellido2 -- Cierre del cursor CLOSE cClientes -- Liberar los recursos DEALLOCATE cClientes El siguiente conjunto de parámetros que podemos especificar es [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]. A continuación mostramos el significado de cada una de estas opciones.



STATIC Define un cursor que hace una copia temporal de los datos que va a utilizar. Todas las solicitudes que se realizan al cursor se responden desde esta tabla temporal de tempdb; por tanto, las modificaciones realizadas en las tablas base no se reflejan en los datos devueltos por las operaciones de recuperación realizadas en el cursor y además este cursor no admite modificaciones.

DECLARE cClientes CURSOR STATIC FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES 

KEYSET Especifica que la pertenencia y el orden de las filas del cursor se fijan cuando se abre el cursor. El conjunto de claves que identifica las filas de forma única está integrado en la tabla denominada keyset de tempdb.

DECLARE cClientes CURSOR KEYSET FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES 

DYNAMIC Define un cursor que, al desplazarse por él, refleja en su conjunto de resultados todos los cambios realizados en los datos de las filas. Los valores de los datos, el orden y la pertenencia de las filas pueden cambiar en cada operación de recuperación. La opción de recuperación ABSOLUTE no se puede utilizar en los cursores dinámicos.

DECLARE cClientes CURSOR DYNAMIC FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES



FAST_FORWARD Especifica un cursor FORWARD_ONLY, READ_ONLY con las optimizaciones de rendimiento habilitadas. No se puede especificar FAST_FORWARD si se especifica también SCROLL o FOR_UPDATE.

DECLARE cClientes CURSOR FAST_FORWARD FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES En SQL Server 2000, las opciones de cursor FAST_FORWARD y FORWARD_ONLY se excluyen mutuamente. Si se especifican ambas, se genera un error. En SQL Server 2005, las dos palabras clave se pueden utilizar en la misma instrucción DECLARE CURSOR. El siguiente conjunto de parámetros que podemos especificar es [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]. A continuación mostramos el significado de cada una de estas opciones. 

READ_ONLY Evita que se efectúen actualizaciones a través de este cursor. No es posible hacer referencia al cursor en una cláusula WHERE CURRENT OF de una instrucción UPDATE o DELETE. Esta opción reemplaza la capacidad de actualizar el cursor.

DECLARE cClientes CURSOR READ_ONLY FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES 

SCROLL_LOCKS Especifica que se garantiza que las actualizaciones o eliminaciones posicionadas realizadas a través del cursor serán correctas. Microsoft SQL Server bloquea las filas cuando se leen en el cursor para garantizar que estarán disponibles para futuras modificaciones. No es posible especificar SCROLL_LOCKS si se especifica también FAST_FORWARD o STATIC.

DECLARE cClientes CURSOR SCROLL_LOCKS FOR SELECT

Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento FROM CLIENTES 

OPTIMISTIC Especifica que las actualizaciones o eliminaciones posicionadas realizadas a través del cursor no se realizarán correctamente si la fila se ha actualizado después de ser leída en el cursor. SQL Server no bloquea las filas al leerlas en el cursor. En su lugar, utiliza comparaciones de valores de columna timestamp o un valor de suma de comprobación si la tabla no tiene columnas timestamp, para determinar si la fila se ha modificado después de leerla en el cursor. Si la fila se ha modificado, el intento de actualización o eliminación posicionada genera un error. No es posible especificar OPTIMISTIC si se especifica también FAST_FORWARD.

DECLARE cClientes CURSOR OPTIMISTIC FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES Por último, queda la opción TYPE_WARNING 

TYPE_WARNING

Especifica que se envía un mensaje de advertencia al cliente si el cursor se convierte implícitamente del tipo solicitado a otro.

DECLARE cClientes CURSOR TYPE_WARNING FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES Podemos especificar multiples parámetros en la apertura de cursor, pero unicamente un parámetro de cada grupo. Por ejemplo:

DECLARE cClientes CURSOR LOCAL STATIC TYPE_WARNING FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES Para actualizar los datos de un cursor debemos especificar FOR UPDATE despues de la sentencia SELECT en la declaración del cursor, y WHERE CURRENT OF en la sentencia UPDATE tal y como muestra el siguiente ejemplo. -- Declaracion de variables para el cursor DECLARE @Id int, @Nombre varchar(255), @Apellido1 varchar(255), @Apellido2 varchar(255), @NifCif varchar(20), @FxNacimiento datetime -- Declaración del cursor DECLARE cClientes CURSOR FOR SELECT

Id, Nombre, Apellido1, Apellido2, NifCif, FxNacimiento

FROM CLIENTES FOR UPDATE

-- Apertura del cursor OPEN cClientes -- Lectura de la primera fila del cursor FETCH cClientes INTO @id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento

WHILE (@@FETCH_STATUS = 0 ) BEGIN UPDATE Clientes

SET APELLIDO2 = isnull(@Apellido2,'') + ' - Modificado' WHERE CURRENT OF cClientes -- Lectura de la siguiente fila del cursor FETCH cClientes INTO @id, @Nombre, @Apellido1, @Apellido2, @NifCif, @FxNacimiento END -- Cierre del cursor CLOSE cClientes -- Liberar los recursos DEALLOCATE cClientes

SQL dinámico en Transact SQL Transact SQL permite dos formas de ejecutar SQL dinámico (construir sentencias SQL dinámicamente para ejecutarlas en la base de datos):  

La instrucción EXECUTE - o simplemente EXEC El procedimiento almacenado sp_executesql

Desde aquí recomendamos la utilización de sp_executesql si bien vamos a mostrar la forma de trabajar con ambos métodos.

La instrucción EXECUTE La instrucción EXECUTE - o simplemente EXEC - permite ejecutar una cadena de caracteres que representa una sentencia SQL. La cadena de caracteres debe ser de tipo nvarchar . El siguiente ejemplo muestra como ejecutar una cadena de caracteres con la instrucción EXEC.

DECLARE @sql nvarchar(1000)

SET @sql = 'SELECT COD_PAIS, NOMBRE_PAIS, ACTIVO, FX_ALTA FROM PAISES' EXEC (@sql)

También con SQL dinámico podemos ejecutar sentencias de tipo DDL (Data Definition Languaje), como CREATE TABLE.

DECLARE @sql nvarchar(1000) SET @sql='CREATE TABLE TEMPORAL ( ID int IDENTITY, DATO varchar(100))' EXEC (@sql)

SET @sql = 'SELECT * FROM TEMPORAL' EXEC (@sql) El principal inconveniente de trabajar con la instrucción EXEC es que no permite el uso de parámetros abriendo la puerta a potenciales ataques de Sql Injections – Además el uso de la instrucción EXEC es menos eficiente, en términos de rendimiento, que sp_executesql. Para solventar el problema debemos trabajar siempre con sq_executesql, que permite el uso de parametros y con el que obtendremos un mejor rendimiento de nuestras consultas.

El procedimiento almacenado sp_executesql Para ejecutar sql dinamico, se recomienda utilizar el procedimiento almacenado sp_executesql, en lugar de una instrucción EXECUTE.   

sp_executesql admite la sustitución de parámetros sp_executesql es más seguro y versátil que EXECUTE sp_executesql genera planes de ejecución con más probabilidades de que SQL Server los vuelva a utilizar, es más eficaz que EXECUTE.

El siguiente ejemplo muestra el uso (muy simple) de sp_executesql.

DECLARE @sql nvarchar(1000)

SET @sql = 'SELECT COD_PAIS, NOMBRE_PAIS, ACTIVO, FX_ALTA FROM PAISES'

EXEC sp_executesql @sql sp_executesql admite la sustitución de valores de parámetros para cualquier parámetro especificado en la cadena Transact-SQL a ejecutar. El siguiente ejemplo muestra el uso de sp_executesql con parámetros:

DECLARE @sql nvarchar(1000), @paramDefinition nvarchar(255), @paramValue char(3)

SET @paramDefinition = '@codPais char(3)' SET @paramValue = 'ESP' SET @sql = 'SELECT COD_PAIS,

NOMBRE_PAIS, ACTIVO, FX_ALTA FROM PAISES WHERE COD_PAIS = @codPais'

EXEC sp_executesql @sql, @paramDefinition, @paramValue

Características Resaltantes de SQL Server 2005 Numerando Registros (RowId): En muchas ocasiones es necesario poder obtener una columna con el número de registro o también poder generar un ranking. Hasta SQL2000 este tipo de operaciones no eran tan simples de realizar y no disponíamos de instrucciones directas. En TSQL 2005 disponemos de una serie de instrucciones las cuales nos hacen el trabajo mucho más simple y eficiente. Veamos de qué se trata ello. Row_Number: Esta nueva función de TSQL nos permitirá numerar los resultados de una query. El siguiente ejemplo muestra el uso de esta función: Use AdventureWorks Go

Select row_number() over(order by name) as rowid, name,productNumber,productId from Production.product where productnumber like 'be%' order by name El resultado obtenido de esta query es el siguiente:

rowid name

productNumber

productId

-------- -------------------------------------------------- ------------------------- --------1

BB Ball Bearing

2

Headset Ball Bearings

BE-2349 BE-2908

3 4

A las funciones de numeración como Row_number() le podemos agregar la cláusula Partition la cual nos permitirá numerar pero haciendo un corte y reiniciando el numerador a partir de ese corte. En el siguiente ejemplo hacemos uso de ello: Use AdventureWorks Go

Select row_number() over(partition by color order by name) as rowid, name,productNumber,productId,color from Production.product where color in ('Blue','Silver') order by color,name

Este ejemplo nos retornara un listado de productos donde el color sea Blue o Silver y los numerara con rowid pero al cambiar el color el numerador volverá a comenzar.

rowid name

productNumber

productId color

------- --------------------------------------------- ------------------------- ----------- --------------… 25

Touring-3000 Blue, 58

BK-T18U-58

959

Blue

26

Touring-3000 Blue, 62

BK-T18U-62

960

Blue

1

Chain

CH-0234

952

Silver

2

Chainring Bolts

CB-2903

320

Silver

3

Chainring Nut

CN-6137

321

Silver

4

Freewheel

FH-2981

332

Silver

…

Conclusiones: La instrucción Row_number() nos da la habilidad de poder numerar los resultados de nuestras querys, este tipo de tareas eran muy solicitadas por los usuarios en la versión 2000 y no

existía una solución directa como la que podemos tener ahora con Sql2005. De todas maneras vale aclarar un concepto: Los motores de base de datos están pensados para trabajar en conjunto de registros y no registró a registro, por lo cual les recomiendo que el uso de numeradores lo utilicen con criterio y que no se lo utilice para recorrer registró a registro (cursores).

TOP @n: Tsql2005 incorpora una nueva funcionalidad a la instrucción TOP, hasta la versión 2000 no se le podía pasar un parámetro variable a la misma, en la versión 2005 esta funcionalidad se ha agregado. El siguiente ejemplo nos muestra cómo se implementa: USE ADVENTUREWORKS GO DECLARE @NUM INT SET @NUM = 3 SELECT TOP(@NUM) * FROM PRODUCTION.PRODUCT

En el siguiente ejercicio veremos cómo podemos utilizar TOP @n para eliminar registros de a grupos:

Use AdventureWorks Go

IF OBJECT_ID('GRANDE') IS NOT NULL DROP TABLE GRANDE GO

CREATE TABLE GRANDE (ID INT IDENTITY,NOMBRE VARCHAR(100), DIRECCION VARCHAR(100)) GO

DECLARE @N INT

SET @N = 1

WHILE

@N <= 50000

BEGIN INSERT INTO GRANDE (NOMBRE,DIRECCION) VALUES ('SQL' + CONVERT(VARCHAR(10),@N),'DIR' + CONVERT(VARCHAR(10),@N)) SET @N = @N+1 END GO

/* BORRAMOS POR PARTES (DE A 500) USANDO ROWCOUNT Y TOP */

SET ROWCOUNT 500

DELETE TOP(500) FROM GRANDE WHILE @@ROWCOUNT > 0 DELETE TOP(500) FROM GRANDE SET ROWCOUNT

0

Conclusiones: Sin lugar a dudas que muchos de los desarrolladores y DBA que venimos trabajando con Sql2000 estábamos reclamando esta funcionalidad del TOP, con la misma como se podrá observar se pueden lograr muchas funcionalidades que antes se debían resolver quizás con SQLDinámico.

Control de errores:

Tsql 2005 incorpora al control de errores los bloques TRY..CATH. La administración de errores de esta manera es conocida por los desarrolladores de la actualidad ya que .NET administra de la misma manera. En esta sección les mostrare varios ejemplos de su utilización. Para poder realizar estos ejercicios he creado una tabla de prueba

USE ADVENTUREWORKS GO

IF OBJECT_ID('EMPLEADOS') IS NOT NULL DROP TABLE EMPLEADOS GO

CREATE TABLE EMPLEADOS (ID INT PRIMARY KEY, NOMBRE VARCHAR(30) NOT NULL) GO

En nuestro primer ejemplo veremos como poder capturar un error al intentar realizar una operación de insert sobre nuestra tabla donde el campo Nombre no admite valores nulos. BEGIN TRY

INSERT INTO EMPLEADOS VALUES (1,NULL)

PRINT 'PASO EL INSERT' END TRY

BEGIN CATCH PRINT 'TENEMOS EL ERROR NUMERO:' + CONVERT(VARCHAR(10),ERROR_NUMBER()) END CATCH

Como podemos observar al suceder un error SQL sale a la sección CATCH donde capturamos el mismo y podemos informar al usuario con algún mensaje. En este ejemplo se hace uso de la función ERROR_NUMBER() las cual nos retorna el número de error, pero también podríamos hacer uso de las siguientes funciones:

ERROR_LINE() : Retorna el número de línea donde se generó el error. El resultado es NULL cuando el error se generó fuera del bloque Try.

ERROR_MESSAGE(): Retorna el texto del error.

ERROR_PROCEDURE(): Retorna el nombre del Procedimiento Almacenado o el Trigger desde donde se ha generado el error en el bloque Try Catch

ERROR_SEVERITY(): Retorna la severidad del error

El siguiente ejemplo genera un error en el primer insert y automáticamente se pasa a la sección Catch sin ejecutar la segunda instrucción, además en el modulo Catch evaluamos el número de error y personificamos el mensaje emitido al usuario.

BEGIN TRY

INSERT INTO EMPLEADOS VALUES ('1',NULL) INSERT INTO EMPLEADOS VALUES ('1','MAXI')

PRINT 'PASO EL INSERT' END TRY

BEGIN CATCH IF ERROR_NUMBER() = 515 BEGIN PRINT ' HAY ALGUNAS COLUMNAS QUE NO ADMITEN NULOS: ' + ERROR_MESSAGE() END ELSE IF ERROR_NUMBER() = 8152 BEGIN PRINT ' SE HA SOBREPASADO EL MAXIMO DE UN CAMPO: ' + ERROR_MESSAGE() END

ELSE BEGIN PRINT 'TENEMOS EL ERROR NUMERO:' + CONVERT(VARCHAR(10),ERROR_NUMBER()) + ' ' + ERROR_MESSAGE() END END CATCH

Conclusiones: El manejo de los errores en sql2005 ha mejorado considerablemente comparado con su antecesor, ahora como se podrá observar no es necesario ir controlando la variable @@error por cada instrucción que realizamos, con el uso de los bloques Try.. Cacth es mucho más simple y además mantiene la metodología de .NET. En este artículo no hemos tratado como manejar errores cuando existen transacciones de por medio, pero el manejo de las mismas en sql2005 es mucho más simple y amigable que en la versión 2000. Si se desea ampliar sobre este tema les recomiendo que lean sobre la función XACT_STATE en sus libros online (BOL).

MAX para columnas dinámicas: En sql2005 disponemos de la característica MAX para los tipos de datos VARCHAR, NVARCHAR y VARBINARY. La idea de esta característica es poder ampliar la capacidad de estos tipos de datos. En sql2000 estos tipos de datos disponían un máximo de 8000 y 4000 (para los Nvarchar), con MAX se puede almacenar hasta 2GB de información en estos tipos de datos. La idea seria reemplazar donde se pueda este tipo de datos por los viejos IMAGE , TEXT y NTEXT.

El siguiente ejemplo nos muestra la utilización de esta característica:

Use AdventureWorks Go

Create table emp (id int identity, nombre varchar(50), descripcion varchar(max)) go

Insert into emp (nombre,descripcion) values ('A1','esto es una prueba de Max') Go

Update emp set descripcion='nuestro primer update' Go

Conclusiones: Esta nueva capacidad en los tipos de datos Varchar, Nvarchar y Narbinary es mucho más simple que los viejos Image, Text y Ntext. De todas maneras habrá que analizar bien cuando es conveniente migrar a este tipo de datos, si la aplicación es nueva yo recomendaría utilizarlos en lugar de sus pares de la versión 2000.

SET Default y SET NULL (DRI Actions):

SQL Server 2005 Beta2 incluye soporte para dos (2) nuevas DRI actions (Declarative Referential Integrity). Estas nuevas DRI Actions se utilizaran en las relaciones Foreign Key tanto para UPDATE como para DELETE.

El siguiente ejemplo nos muestra como usar SET DEFAULT y SET NULL:

/*DRI (Declarative Referencial Constraint) SET DEFAULT and SET NULL */

Use AdventureWorks Go

if object_id('pedidos') is not null drop table pedidos Go

If object_id('clientes') is not null drop table clientes Go

Create table Clientes (id varchar(10) Primary Key, nombre varchar(100)) Go

INSERT INTO CLIENTES VALUES ('MICROSOFT','MICROSOFT') INSERT INTO CLIENTES VALUES ('IBM','IBM') INSERT INTO CLIENTES VALUES ('SAP','SAP') INSERT INTO CLIENTES VALUES ('HP','HP') Go

Create Table Pedidos (numero int Primary Key,cliente_id varchar(10) default('MICROSOFT'), fecha datetime, constraint fk_1 Foreign Key (Cliente_id) References Clientes(id) ON DELETE SET NULL ON UPDATE SET DEFAULT)

Go

Hasta aquí hemos creados dos(2) tablas, una llamada Clientes y la otra Pedidos, en esta ultima hemos agregado una relación con la primera y se ha definido que ante una acción de DELETE (en clientes) se introduzca Null en el campo Cliente_id de Pedidos y ante una acción de UPDATE se introduzca el valor Default de dicho campo.

INSERT INTO PEDIDOS VALUES (1,'IBM',GETDATE()) INSERT INTO PEDIDOS VALUES (2,'HP',GETDATE()+10) INSERT INTO PEDIDOS VALUES (3,'SAP',GETDATE()-65) INSERT INTO PEDIDOS VALUES (4,'IBM',GETDATE()+365) INSERT INTO PEDIDOS VALUES (5,'MICROSOFT',GETDATE()+95) INSERT INTO PEDIDOS VALUES (6,'SAP',GETDATE()+110) GO

Ahora que tenemos los registros en nuestras dos (2) tablas, haremos una acción Delete en Clientes y observaremos que ha sucedido con esos registros en la tabla Pedidos

DELETE FROM CLIENTES WHERE ID ='IBM' GO

SELECT numero,cliente_id FROM PEDIDOS Go

numero

cliente_id

----------- ---------1

NULL

2

. . .

.. Como podemos observar el registro (1) que contenía el cliente IBM , ahora el campo de referencia tiene un valor Null ya que hemos eliminado su maestro de la tabla Clientes y nuestro DRI actions indicaba que se debía introducir Null.

Ahora veremos que sucede al realizar un Update sobre nuestra tabla Clientes

UPDATE CLIENTES SET ID='CITRIX' WHERE ID ='HP' Go SELECT * FROM PEDIDOS

numero

cliente_id

----------- ---------1

NULL

2

MICROSOFT

...

...

Como podemos observar el realizar un Update sobre el campo llave de la tabla Clientes, en nuestra tabla hija (Pedidos) se ha cambiado al valor Default del campo (‘Microsoft’)

Conclusiones: Sin ninguna duda que el agregado de estas dos (2) nuevas funciones de DRI nos ayudaran considerablemente al momento de realizar nuestras relaciones padres – hijos, que hasta la versión 2000 realizar este mismo tipo de operatoria como hemos visto en los ejemplos era una tarea muy incómoda.

DDL Triggers:

En la versión 2000 de MS SQL-Server solo disponíamos de triggers (Desencadenadores) DML para las instrucciones Insert – Update y Delete. SQL 2005 incorpora un nuevo tipo de triggers llamados DDL Triggers, los cuales pueden tener efecto sobre las instrucciones DDL como por ej: CREATE TABLE. En esta sección veremos dos (2) ejemplos sobre estos nuevos tipos de triggers, uno de ellos hará una auditoria de instrucciones DDL y el otro no permitirá la ejecución de una instrucción ALTER TABLE

USE ADVENTUREWORKS GO

CREATE TRIGGER DDL_1 ON DATABASE FOR ALTER_TABLE AS RAISERROR('NO SE PUEDE ALTERAR TABLAS EN ESTA BASE DE DATOS.',16,1) ROLLBACK GO

En este ejemplo hemos creado un trigger DDL en la base de datos AdventureWorks el cual impedirá que se realicen operaciones ALTER TABLE sobre dicha base de datos. Si deseamos realizar la siguiente operación recibiremos un error y no se realizara la misma.

USE ADVENTUREWORKS GO

ALTER TABLE PRODUCTION.PRODUCT ADD COLUMNA VARCHAR(10) GO

Ahora veremos un ejemplo donde usaremos un DDL trigger para realizar auditorías DDL

USE ADVENTUREWORKS GO

CREATE TABLE DDL_AUDIT (LSN INT NOT NULL IDENTITY,POSTTIME DATETIME NOT NULL, EVENTTYPE SYSNAME NOT NULL, LOGINNAME SYSNAME NOT NULL, OBJECTNAME SYSNAME NOT NULL,TARGETOBJECTNAME SYSNAME NOT NULL) GO

El primer paso ha sido crear una tabla donde registraremos las auditorias DDL.

CREATE TRIGGER DDL_2 ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @V AS XML SET @V = EVENTDATA() INSERT INTO DDL_AUDIT (POSTTIME,EVENTTYPE,LOGINNAME,OBJECTNAME, TARGETOBJECTNAME) VALUES ( CAST(@V.query('data(//PostTime)')

as

varchar(23)),

CAST(@V.query('data(//EventType)')

as

sysname),

CAST(@V.query('data(//LoginName)')

as

sysname),

CAST(@V.query('data(//ObjectName)')

as

sysname),

CAST(@V.query('data(//TargetObjectName)') as sysname)) GO

Este segundo paso es el que genera el trigger DDL el cual registrara en nuestra tabla cada acontecimiento que suceda.

Ahora solo nos resta por probar el funcionamiento del trigger

CREATE TABLE PP (ID INT) GO

SELECT * FROM DDL_AUDIT

Conclusiones: Los DDL triggers son una nueva herramienta muy poderosa de verdad, la cual nos permitirá desde ahora poder tener control sobre las sentencias DDL como así también poder realizar auditorías a las mismas.

Conclusiones Generales:

En este artículo hemos visto algunas de las novedades de Sql2005 en lo que respecta a T-SQL. Aún quedan muchas otras funcionalidades por mostrar pero debo indicar que esta nueva versión del popular motor de base de datos es muy distinta a su antecesora incorporando muchas mejoras tanto para el desarrollador como así también para los DBA.