Transact SQL

5/16/2018 Transact SQL - slidepdf.com

http://slidepdf.com/reader/full/transact-sql-55ab5183d364a 1/136

Introducción a Transact SQL

Programación con Transact SQL

Tipos de datos en Transact SQL 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 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 Estructura IFEstructura CASEBucle WHILEEstructura GOTO

Control de errores en Transact SQL Uso de TRY CATCHFunciones especiales de Error

La variable de sistema @@ERROR Generar un error con RAISERROR

Consultar datos en Transact SQL La sentencia SELECTLa cláusula WHERELa cláusula ORDER BY

Consultas agregadas La cláusula GROUP BY La cláusula HAVING AVGCountMax, MinSumUso de Select TOP con consultas agregadas

Select FOR XML Clausula FOR XML.Campos y variables XML.

Operaciones con conjuntos.

http://www.devjoker.com/contenidos/catss/228/Introducci%c3%b3n-a-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/229/Programaci%c3%b3n-con-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/232/Tipos-de-datos-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/293/Variables-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/243/Equivalencia-de-datos-de-SQL-Server-y-NET.aspx


http://www.devjoker.com/contenidos/catss/233/Operadores-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/234/Estructuras-de-control-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/255/Control-de-errores-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/235/Consultar-datos-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/272/Consultas-agregadas.aspx


http://www.devjoker.com/contenidos/catss/244/Select-FOR-XML.aspx


http://www.devjoker.com/contenidos/catss/273/Operaciones-con-conjuntos.aspx
















UNIONEXCEPTINTERSECT

Insertar datos en Transact SQL Inserción individual de filas.

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

Actualizar datos en Transact SQL UpdateUpdate INNER JOINClausula OUTPUT

Borrar datos en Transact SQL DeleteClausula OUTPUT

Truncate TableTransacciones en Transact SQL Concepto de transaccionTransacciones implicitas y explicitasTransacciones anidadas.Puntos de recuperacion

Procedimientos almacenados en Transact SQL

Funciones en Transact SQL Funciones escalaresFunciones en lineaFunciones en línea de multiples sentencias

Funciones integradas de Transact SQL (I) Cast y ConvertIsnullCOALESCEGetDate y GetUTCDate

Triggers en Transact SQL Trigger DMLTrigger DDL

Cursores en Transact SQL

SQL dinámico en Transact SQL La instrucción comando EXECUTEEl procedimiento almacenado sp_executesql




http://www.devjoker.com/contenidos/catss/236/Insertar-datos-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/237/Actualizar-datos-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/277/Borrar-datos-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/292/Transacciones-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/238/Procedimientos-almacenados-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/239/Funciones-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/301/Funciones-integradas-de-Transact-SQL-I.aspx


http://www.devjoker.com/contenidos/catss/278/Triggers-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/240/Cursores-en-Transact-SQL.aspx


http://www.devjoker.com/contenidos/catss/308/SQL-din%c3%a1mico-en-Transact-SQL.aspx














Introducción a Transact SQL

SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, pero que noposee la potencia de los lenguajes de programación.

Para abordar el presente curso con mínimo de garantias es necesario conocer previamente SQL.Transact SQL es el lenguaje de programación que proporciona SQL Server para ampliar SQL conlos elementos caracteristicos de los lenguajes de programación: variables, sentencias de control deflujo, 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 laversatilidad de los lenguajes de programación tradicionales. Transact SQL es el lenguaje deprogramación que proporciona SQL Server para extender el SQL estándar con otro tipo deinstrucciones.

Transact SQL existe desde las primeras versiones de SQL Server, si bien a lo largo de estecurso nos centraremos en la versión SQL Server 2005.

¿Qué vamos a necesitar?

Para poder seguir este curso correctamente necesitaremos tener los siguientes elementos:

Un servidor SQL Server 2005. Podemos descargar gratuitamente la versión SQL Server Expressdesde el siguiente enlace.SQL Server 2005 Express.

Herramientas cliente de SQL Server. Recomendamos:o Microsoft SQL Server Management Studio o Toad para SQL Server

http://www.microsoft.com/downloads/details.aspx?familyid=220549B5-0B07-4448-8848-DCC397514B41&displaylang=es



http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=es


http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm







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 decontrol 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 desearealizar una aplicación completa para el manejo de una base de datos relacional, resulta necesarioutilizar alguna herramienta que soporte la capacidad de consulta del SQL y la versatilidad de loslenguajes de programación tradicionales. Transact SQL es el lenguaje de programación queproporciona Microsoft SQL Server para extender el SQL estándar con otro tipo de instrucciones yelementos propios de los lenguajes de programación.

Con Transact SQL vamos a poder programar las unidades de programa de la base de datos SQLServer, están son:

Procedimientos almacenados Funciones Triggers Scripts

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

Service Broker



Tipos de datos en Transact SQL

Cuando definimos una tabla, variable o constante debemos asignar un tipo de dato que indica losposibles valores. El tipo de datos define el formato de almacenamiento, espacio que de disco-memoria 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 dedatos 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 puedanalmacenar mayor será en consecuencia el espacio utilizado para almacenarlo. Como regla generalse recomienda usar el tipo de dato mínimo posible. Todos los datos numéricos admiten el valorNULL.

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 a32767.

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 sinredondear. 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,79x-10308, , 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 diezmilésimas de la unidad monetaria.

SmallMoney. Almacena valores numéricos monetarios de -214.748,3647 a 214.748,3647, con unaprecisión de hasta diez milésimas de la unidad monetaria.

Todos los tipos de datos enteros pueden marcarse con la propiedad identity para hacerlos autonumé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 @smallintset @int = 642325

print @intset @decimal = 56565.234 -- Punto como separador decimal

print @decimalset @money = 12.34 print @money

Tipos de datos de caracter.

Char(n). Almacena n caracteres en formato ASCII, un byte por cada letra. Cuando almacenamosdatos en el tipo char, siempre se utilizan los n caracteres indicados, incluso si la entrada de datoses inferior. Por ejemplo, si en un char(5), guardamos el valor 'A', se almacena 'A ', ocupando loscinco bytes.

Varchar(n).Almacena n caracteres en formato ASCII, un byte por cada letra. Cuandoalmacenamos datos en el tipo varchar, únicamente se utilizan los caracteres necesarios, Porejemplo, si en un varchar(255), guardamos el valor 'A', se almacena 'A', ocupando solo un bytebytes.

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 recomendableutilizar este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer adiferente idiomas.

Nvarchar(n).Almacena n caracteres en formato UNICODE, dos bytes por cada letra. Esrecomendable utilizar este tipo de datos cuando los valores que vayamos a almacenar puedanpertenecer a diferente 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 muyespecíficas.



SmallDatetime. Almacena fechas con una precisión de minuto, por lo que ocupa la mitad deespacio de que el tipo datetime, para tablas que puedan llegar a tener muchos datos es un factor atener muy en cuenta.

TimeStamp.Se utiliza para marcar un registro con la fecha de inserción - actualización. El tipotimestamp 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 8000bytes.

Varbinary. Se utiliza para almacenar datos binarios de longitud variable, con una longitud máximade 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: <row nombre="SVR01"/>

Otros tipos de datos.

UniqueIdentifier. Se utiliza para identificadores únicos. Para generar identificadores únicosdebemos 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 almacenavarchar(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ónCREATE TYPE. Personalmente, desaconsejo el uso de tipos de datos personalizados.

CREATE TYPE MD5 FROM CHAR (32) NULLGO

DECLARE @miMD5 MD5 set @miMD5 = '0000000000000000000000000000000A'

print @miMD5



Variables en Transact SQL

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 caracter @, es decir, elnombre de una variable debe comenzar por @. Para declarar variables en Transact SQL debemosutilizar la palabra clave declare, seguido del identificador y tipo de datos de la variable.

-- 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.devjoker.com' -- El signo = es un operador

-- www.devjoker.com es un literal print @Nombre -- Imprime por pantalla el valor de @nombre.

-- No diferencia mayúsculas ni minúsculas

Asignar variables en Transact SQL

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

A través de la instrucció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 consultaSELECT devuelve más de un registro, las variables quedarán asignadas con los valores de la últimafila 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 curso.



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, parael namespace System.Data.SqlTypes y los tipos nativos de CRL .NET FrameWork

SQL Server CLR data type (SQL Server) CLR data type (.NETFramework)

varbinary SqlBytes, SqlBinary Byte[]

binary SqlBytes, SqlBinary Byte[] varbinary(1),

binary(1) SqlBytes, SqlBinary byte, Byte[]

image ninguno ningunovarchar ninguno ninguno

char ninguno ningunonvarchar(1),

nchar(1) SqlChars, SqlString Char, String, Char[]

nvarchar

SqlChars, SqlString

SQLChars es mejor para la transferencia de datos ySQLString obtiene mejor rendimiento para

operaciones con Strings.

String, Char[]

nchar SqlChars, SqlString String, Char[] text ninguno ninguno

ntext ninguno ningunouniqueidentifier SqlGuid Guid

rowversion ninguno Byte[] bit SqlBoolean Boolean

tinyint SqlByte Byte smallint SqlInt16 Int16

int SqlInt32 Int32 bigint SqlInt64 Int64

smallmoney SqlMoney Decimal money SqlMoney Decimal

numeric SqlDecimal Decimal decimal SqlDecimal Decimal

real SqlSingle Single float SqlDouble Double

smalldatetime SqlDateTime DateTime datetime SqlDateTime DateTime

sql_variant ninguno Object

User-definedtype(UDT)

ninguno Misma clase que ladefinida en el assemblie.

table ninguno ningunocursor ninguno ninguno



timestamp ninguno ninguno

xml SqlXml 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 relacionaleso de comparación

= (igual a)<> (distinto de)!= (distinto de)

< (menor que)> (mayor que)>= (mayor o igual a)<= (menor o igual a)

!> (no mayor a)!< (no menor a)

Operadores lógicos 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)

Operador de

concatenación +

Otros

ALL (Devuelve TRUE si el conjunto completo de comparaciones esTRUE)

ANY (Devuelve TRUE si cualquier elemento del conjunto decomparaciones es TRUE)

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 (<expresion>)BEGIN... END

ELSE IF (<expresion>)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.devjoker.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)

BEGINUPDATE 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 <expresion> WHEN <valor_expresion> THEN <valor_devuelto> WHEN <valor_expresion> THEN <valor_devuelto>ELSE <valor_devuelto> -- 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.devjoker.com' WHEN 'ALM' THEN 'www.aleamedia.com'

ELSE 'www.devjoker.com' END)

PRINT @Web

Otra sintaxis de CASE nos permite evaluar diferentes expresiones:

CASE WHEN <expresion> = <valor_expresion> THEN <valor_devuelto> WHEN <expresion> = <valor_expresion> THEN <valor_devuelto>

ELSE <valor_devuelto> -- 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.devjoker.com' WHEN @diminutivo = 'ALM' THEN 'www.aleamedia.com'

ELSE 'www.devjoker.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 webFROM WEBS

WHERE id=2)ELSE 'www.devjoker.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 <expresion>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 + 1IF (@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_RECIBOFROM RECIBOS WHERE PENDIENTE = 'S')

UPDATE RECIBOSSET PENDIENTE = 'N'

WHERE CO_RECIBO = @coReciboEND

Estructura GOTO



La sentencia goto nos permite desviar el flujo de ejecución hacia una etiqueta. Fué muy utilizada enversiones anteriores de SQL Server conjuntamente con la variable de sistema @@ERROR para elcontrol de errores.

Actualmente, se desaconseja el uso GOTO, recomendandose el uso de TRY - CATCH para la gestionde errores.

DECLARE @divisor int,@dividendo int,@resultado int

SET @dividendo = 100SET @divisor = 0

SET @resultado = @dividendo/@divisor

IF @@ERROR > 0GOTO 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 SQLServer, 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 linea provoca un error de division por 0SET @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 laobtenció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 0SET @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 labase 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




-- 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áshabituales es sin duda, incluir una nueva sentencia Transact SQL entre la línea que provoco elerror y la que lo controla. Esa nueva instrucción restaura el valor de @@ERROR y no controlaremosel error.

El siguiente ejemplo ilustra esta situación:

DECLARE @divisor int ,@dividendo int ,@resultado int


-- Esta linea provoca un error de division por 0 SET @resultado = @dividendo/@divisor

PRINT 'Controlando el error ...' -- Esta linea estable @@ERROR a cero

IF @@ERROR = 0BEGIN

-- 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 quese 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 = 3IF (@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 podemosasignar valores del 0 al 18. Los errores el 20 al 25 son considerados fatales por el sistema, ycerraran la conexion que ejecuta el comando RAISERROR . Para asignar valores del 19 al 25necesitares 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 diferentespartes 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 ] ]<nombre_campos >

FROM <nombre_tabla >[ INNER | LEFT [OUTER]| RIGHT [OUTER] | CROSS]

[JOIN ] <nombre_tabla > ON <condicion_join>[ AND|OR <condicion>][WHERE <condicion > [ AND|OR <condicion>]]

[GROUP BY <nombre_campos >][HAVING <condicion >[ AND|OR <condicion>]][ORDER BY <nombre_campo > [ ASC | DESC]

El siguiente ejemplo muestra una consulta sencilla que obtiene el código y la "familia" de una tablallamada 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 enla tabla.

SELECT



*

FROM

FAMILIAS

Ahora vamos a realizar una consulta obteniendo además de los datos de familias, los datos de lascategorias 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 clausula INNER JOIN, que es una clasula exclusiva, esdecir las familias que no tengan categorias y productos asociados no se devolveran.

Si queremos realizar la consulta para que no sea exclusiva, tenemos que utilizar LEFT JOIN. El usode 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 devolveran en valornull 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 varias valores para una condicion en la clausula IN:

SELECT

*

FROM

FAMILIAS

WHERE

CO_FAMILIA IN ( 1 , 2)

La clausula 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 alfanumerico para un campo en la condición WHERE este debe irentre comillas simples:

SELECT

*

FROM

FAMILIAS

WHERE

FAMILIA = 'FAMILIA 1'

Para consultar campos alfanumericos, 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 caracter. [a-d], representa cualquier caracter del intervalo a-d. [abcd], representa cualquier caracter del grupo abcd. [^a-d], representa cualquier caracter diferente del intervalo a-d. [^abcd], representa cualquier caracter 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 clausula TOP. Laclausula TOP admite como parámetros un valor numérico entero o un porcentaje (sólo a partir dela version 2005)

SELECT

TOP 10 * -- Devuelve 10 registros

FROM

FAMILIAS

SELECT

TOP 50 PERCENT * -- Devuelve el 50% de los registros

FROM

FAMILIAS

La clausulaTOP

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 .

http://www.devjoker.com/asp/ver_contenidos.aspx?co_contenido=272#a6






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 clausula 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, comopor ejemplo Sum o Count, en la instrucción SELECT. Su sintaxis es:

SELECT [ ALL | DISTINCT ] [TOP <n> [ WITH TIES]]<nombre_campo> [{,<nombre_campo>}]

[{,<funcion_agregado> }]FROM <nombre_tabla>|<nombre_vista>

[{,<nombre_tabla>|<nombre_vista>}][ WHERE <condicion> [{ AND|OR <condicion>}]]

[GROUP BY <nombre_campo> [{,<nombre_campo >}]][HAVING <condicion>[{ AND|OR <condicion>}]]

[ORDER BY <nombre_campo>|<indice_campo> [ ASC | DESC][{,<nombre_campo>|<indice_campo> [ 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 loscampos GROUP BY se agrupan y no se omiten. No obstante, los valores Null no se evalúan enninguna de las funciones SQL agregadas.

Todos los campos de la lista de campos de SELECT deben incluirse en la cláusula GROUP BY ocomo 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 unpedido, para calcular el total del pedido agrupados 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 decalcular el valor agregado. Es decir, si sumamos el valor de las ventas por producto, la suma secalcula despues de haber aplicado el filtro impuesto por la clausula WHERE.

SELECT



CLIENTES.NOMBRE,

CLIENTES

. APELLIDO1,

CLIENTES

. APELLIDO2,


FROM

DETALLE_PEDIDO

INNER

JOIN PEDIDOS


INNER

JOIN CLIENTES


-- La clausula 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 losdatos, solo los que cumplan una condición del agregado. Por ejemplo, podemos calcular el valor delas ventas por producto, pero que solo queramos ver los datos de los producto que hayan vendidomás o menos de una determinada cantidad. En estos casos debemos utilizar la clausula 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. Seutiliza 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 despues de calcular elagregado. Una vez que los registros se han agrupado utilizando GROUP BY , HAVING determinacuales de ellos se van a mostrar. HAVING permite el uso de funciones agregadas.

SELECT

CLIENTES.NOMBRE,

CLIENTES

. APELLIDO1,

CLIENTES

. APELLIDO2,


FROM

DETALLE_PEDIDO

INNER

JOIN PEDIDOS


INNER

JOIN CLIENTES


-- La clausula 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 multiples 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 unaconsulta. Su sintaxis es la siguiente

AVG(<expr> )

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. Lamedia calculada por Avg es la media aritmética (la suma de los valores dividido por el número devalores). 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


INNER

JOIN CLIENTES


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(<expr> )



En donde expr contiene el nombre del campo que desea contar. Los operandos de expr puedenincluir el nombre de un campo de una tabla, una constante o una función (la cual puede serintrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL). Puede contarcualquier 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 cuentalos registros que tienen campos null a menos que expr sea el carácter comodín asterisco (*). Siutiliza un asterisco, Count calcula el número total de registros, incluyendo aquellos que contienencampos null. Count(*) es considerablemente más rápida que Count(Campo).

SELECT

COUNT(*)

FROM

PEDIDOS

SELECT

CLIENTES.NOMBRE, COUNT(*)

FROM

PEDIDOS

INNER

JOIN CLIENTES


GROUP

BY CLIENTES.NOMBRE

Max, Min

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

MIN(<expr> ) MAX(<expr> )



En donde expr es el campo sobre el que se desea realizar el cálculo. Expr pueden incluir el nombrede un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida porel 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


GROUP

BY CLIENTES.NOMBRE

Sum

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

SUM (<expr> )

En donde expr respresenta el nombre del campo que contiene los datos que desean sumarse o unaexpresió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 puedeser 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


GROUP

BY CLIENTES.NOMBRE

Uso de Select TOP con consultas agregadas.

Podemos utilizar SELECT TOP con consultas agregadas como con cualquier otra instruccionTransact SQL.

En estos casos, la clausula TOP se aplica despues de calcular el agregado, devolviendo las N filasindicadas.

En este escenario es posible que queramos obtener los N valores que satisfagan una condicion. Porejemplo, queremos si queremos obtener los tres primeros clientes con mayores pedidos, usariamos

una consulta parecida a esta:

SELECT TOP 3CLIENTES.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 valoragragado identico al tercero, (es decir, estan empatados). El uso de TOP 3 discriminaría el cuartoregistro. Para evitar este comportamiento, y que la consulta devuelva también al cuarto clienteutilizamos la clausula WITH TIES.

SELECT

TOP 3 WITH TIES CLIENTES.NOMBRE,

SUM(DETALLE_PEDIDO.PRECIO)

FROM

DETALLE_PEDIDO

INNER

JOIN PEDIDOS


INNER

JOIN CLIENTES




GROUP

BY CLIENTES.NOMBRE

ORDER

BY 2 -- SUM(DETALLE_PEDIDO.PRECIO_UNIDAD)



Select FOR XML

Clausula FOR XML.

A partir de la version 2000 SQL Server incluye la clausula 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 clausula FOR XML el resultado de la consulta es devuelto en formato XML.

La clausula FOR XML admite los siguientes modos que representan el formato en el que el XML esdevuelto:

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 filaretornada.

EXPLICIT, el modo EXPLICIT requiere un formato específico que puede ser mapeado en casicualquier 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 formatodel 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:

<

FAMILIAS CO_FAMILIA="1" FAMILIA="FAMILIA 1" />



<


<


<


Podemos obtener el resultado como elementos de la siguiente forma:

SELECT

CO_FAMILIA, FAMILIA

FROM

FAMILIAS

FOR

XML AUTO, ELEMENTS


<

FAMILIAS>

<

CO_FAMILIA>1</CO_FAMILIA>

<FAMILIA>FAMILIA 1</FAMILIA>

</

FAMILIAS>



<

FAMILIAS>

<


<

FAMILIA>FAMILIA 2</FAMILIA>

</

FAMILIAS>

<

FAMILIAS>

<


<


</

FAMILIAS>

<

FAMILIAS>

<


<


</

FAMILIAS>



Ahora un ejemplo de XML RAW:

SELECT

CO_FAMILIA, FAMILIA

FROM

FAMILIAS

ORDER

BY FAMILIA

FOR

XML RAW , TYPE

Obtenemos el siguiente resultado:

<

row CO_FAMILIA="1" FAMILIA="FAMILIA 1" />

<


<


<


Podemos obtener el resultado como elementos de la siguiente forma:

<

row>

<




<


</

row>

<

row>

<


<


</

row>

<

row>

<


<


</

row>

<

row>

<




<


</

row>

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:

<

FamiliasDeProductos CO_FAMILIA="1" FAMILIA="FAMILIA 1" />

<

FamiliasDeProductos CO_FAMILIA="2" FAMILIA="FAMILIA 2" /> <


<




Del mismo modo con la opción ELEMENTS:

SELECT

CO_FAMILIA, FAMILIA

FROM

FAMILIAS

ORDER

BY FAMILIA

FOR

XML RAW ('FamiliasDeProductos') , ELEMENTS


<

FamiliasDeProductos>

<


<


</


<


<




<


</


<


<


<


</


<


<


<


</


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:

-- <NombreNodo>!<nodo>!<atributo>

CO_FAMILIA

as "FamiliaDeProductos!1!CODIGO_FAMILIA",

FAMILIA

as "FamiliaDeProductos!1!DESCRIPCION"

FROM

FAMILIAS

ORDER

BY FAMILIA

FOR

XML EXPLICIT

Obtenemos el siguiente resultado:

<

FamiliaDeProductos CODIGO_FAMILIA="1" DESCRIPCION="FAMILIA 1" />

<


<


<




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 como 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!):



<


<




<


<




<

PRODUCTOS CO_PRODUCTO="1" CO_CATEGORIA="1" PRODUCTO="PRODUCTO 1" />

<


<




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 delmismo 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


FROM

CLIENTES

Cuando realizamos una consulta con UNION internamente se realiza una operacion DISTINCT sobre el conjunto de resultados final. Si queremos obtener todos los valores debemos utilizaUNION ALL.



SELECT


FROM

EMPLEADOS

UNION

ALL

SELECT


FROM

CLIENTES

EXCEPT

EXCEPT devuelve la diferencia (resta) de dos o más conjuntos de resultados. El conjunto obtenidocomo resultado de EXCEPT tiene la misma estructura que los conjuntos originales.

El siguiente ejemplo muestra el uso de EXCEPT

SELECT


FROM

EMPLEADOS

EXCEPT

SELECT


FROM

CLIENTES

El uso de EXCEPT, como norma general, es mucho más rápido que utilizar condiciones NOT IN oEXISTS en la clausula WHERE.



INTERSECT

Devuelve la intersección entre dos o más conjuntos de resultados en uno. El conjunto obtenidocomo resultado de INTERSECT tiene la misma estructura que los conjuntos originales.

El siguiente ejemplo muestra el uso de INTERSECT

SELECT


FROM

EMPLEADOS

INTERSECT

SELECT


FROM

CLIENTES



Insertar datos en Transact SQL

Inserción individual de filas.

Para realizar la insercción individual de filas SQL posee la instrucción INSERT INTO.La insercción

individual de filas es la que más comunmente utilizaremos. Su sintaxis es la siguiente:

INSERT INTO <nombre_tabla>[(<campo1>[,<campo2>,...])]

values(<valor1>,<valor2>,...);

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)

Insertción múltiple de filas.

También es posible insertar en una tabla el resultado de una consulta SELECT. De este modo seinsertarán tantas filas como haya devuelto la consulta SELECT.

El siguiente ejemplo muestra la inserción multiple 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 parala 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 autonumérico (identity), cuandoinsertamos un registro en dicha tabla el valor del campo se genera automaticamente. Pararecuperar el valor generado disponemos de varios métodos:

Utilizar la funcion @@identity, que devuelve el último valor identidad insertado por la

transaccion:

DECLARE

@Codigo int



INSERT

INTO PRECIOS

(


VALUES

(


set

@Codigo = @@Identity

PRINT

@Codigo

El uso de @@Identity no siempre es válido, ya que al devolver el úlitmo valor identidad insertadopor 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 camposidentidad).

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

DECLARE

@Codigo int

INSERT

INTO PRECIOS

(


VALUES



(


SET

@Codigo = SCOPE_IDENTITY ()

PRINT @Codigo

Clausula OUTPUT

A partir de la version de SQL Server 2005 disponemos de la clausula OUTPUT para recuperar losvalores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicasINSERTED y DELETED.

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucciónUPDATE 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ónUPDATE o INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copiade los datos "despues" 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



Actualizar datos en Transact SQL

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 lasentencia UPDATE es la siguiente

UPDATE <nombre_tabla>

SET <campo1> = <valor1>

{[,<campo2> = <valor2>,...,<campoN> = <valorN>]}

[ WHERE <condicion>];

El siguiente ejemplo muestra el uso de UPDATE.

UPDATE

CLIENTES

SET

NOMBRE

= 'Devjoker',

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 loscambios inmediatamente sin necesidad de hacer COMMIT. Por supuesto podemos gestionarnosostros las transacciones pero es algo que hay que hacer de forma explicita con la instruccionBEGIN TRAN y que se verá en capitulos posteriores de este curso.

Update INNER JOIN



En ocasiones queremos actaualizar los datos de una tabla con los datos de otra (muy común paradesnormalizar un modelo de datos).

Habitualmente, usamos subconsultas para este proposito, pero Transact SQL permite la utilizaciónde 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

Clausula OUTPUT

A partir de la version de SQL Server 2005 disponemos de la clausula OUTPUT para recuperar los

valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicasINSERTED y DELETED.





DECLARE

@FILAS_ACTUALIZADAS TABLE

(

CO_CLIENTE int ,

NOMBRE

varchar(100),

APELLIDO1

varchar(100),

APELLIDO2

varchar(100)

)

UPDATE

CLIENTES

SET

NOMBRE

= 'Devjoker',

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ónUPDATE o INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copiade los datos "despues" 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

= 'Devjoker',

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 capitulo 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. Notese que nose especifica ninguna condición WHERE por lo que se borran todos los datos de la tabla.

DELETE

FROM

DATOS

Lógicamente podemos especicar que registros queremos borrar a través de la clausula WHERE.

DELETE



FROM

DATOS

WHERE

Id=12

Cuando borramos datos de una tabla, podemos obtener el número de filas que han sido afectadaspor 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 version de SQL Server 2005 disponemos de la clausula OUTPUT para recuperar losvalores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicasINSERTED y DELETED.





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 borratodos los datos de una tabla.

TRUNCATE

TABLE DATOS

Cuando trabajamos con TRUNCATE TABLE debemos tener en cuenta las siguientesconsideraciones:

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



Transacciones en Transact SQL

Concepto de transaccion

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 conviertenen una parte permanente de la base de datos. Si una transacción encuentra errores y debecancelarse 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 unacuenta y lo añadimos en otra. Si no somo capaces de abonar el dinero en la cuenta de destino, nodebemos 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 lossiguiente:

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


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 erronea, ya que cada instrucción se ejecutaria y confirmaría de formaindependiente, por lo que un error dejaría los datos erroneos en la base de datos ( ¡y ese es el peor

error que nos podemos encontrar! )

Transacciones implicitas y explicitas

Para agrupar varias sentencias Transact SQL en una única transacción, disponemos de lossiguientes métodos:



Transacciones explícitas Cada transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se terminaexplícitamente con una instrucción COMMIT o ROLLBACK .

Transacciones implícitas Se inicia automátivamente una nueva transacción cuando se ejecuta una instrucción que realizamodificaciones 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 implicitas debemos ejecutar la siguienteinstrucción.

--Activamos el modo de transacciones implicitas

SET

IMPLICIT_TRANSACTIONS ON

--Desactivamos el modo de transacciones implicitas

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


@CuentaOrigen

VARCHAR (12),

@CuentaDestino

VARCHAR (12)





*/

SET

@importe = 50

SET


SET


BEGIN

TRANSACTION -- O solo BEGIN TRAN

BEGIN

TRY


UPDATE CUENTAS

SET SALDO = SALDO - @importe

WHERE NUMCUENTA = @CuentaOrigen


INSERT INTO MOVIMIENTOS

(IDCUENTA, SALDO_ANTERIOR , SALDO_POSTERIOR , IMPORTE, FXMOVIMIENTO)

SELECT

IDCUENTA




FROM CUENTAS



UPDATE CUENTAS

SET SALDO = SALDO + @importe

WHERE NUMCUENTA = @CuentaDestino



(IDCUENTA, SALDO_ANTERIOR , SALDO_POSTERIOR ,IMPORTE, FXMOVIMIENTO)

SELECT

IDCUENTA


FROM CUENTAS


/* 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 implicitas .

SET IMPLICIT_TRANSACTIONS ON

DECLARE


@CuentaOrigen

VARCHAR (12),

@CuentaDestino

VARCHAR (12)



*/

SET



@importe = 50

SET


SET


BEGIN

TRY


UPDATE CUENTAS

SET SALDO = SALDO - @importe





SELECT

IDCUENTA


FROM CUENTAS





UPDATE CUENTAS

SET SALDO = SALDO + @importe





SELECT

IDCUENTA


FROM CUENTAS


/* 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 quela primera transacción se ha confirmado o revertido, se inicia automáticamente una nuevatransacción la siguiente vez que la conexión ejecuta una instruccion para modificar datos.

La conexión continúa generando transacciones implícitas hasta que se desactiva el modo detransacciones 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 deaislamiento. Podemos aprender más sobre bloqueos y nivel de aislamiento en este articulo .

http://www.devjoker.com/contenidos/Articulos/210/Bloqueos-y-nivel-de-aislamiento-en-SQL-Server-2005.aspx






Transacciones anidadas.

Podemos anidar varias transacciones. Cuando anidamos varias transacciones la instrucción COMMITafectará 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 desharan

también los cambios de todas las transacciones internas, aunque hayamos realizado COMMIT deellas.

BEGIN

TRAN

UPDATE

EMPLEADOS

SET

NOMBRE = 'Devjoker'

WHERE

ID=101

BEGIN

TRAN

UPDATE

EMPLEADOS

SET

APELLIDO1 = 'Devjoker.COM'

WHERE

ID=101



-- Este COMMIT solo afecta a la segunda transaccion.

COMMIT

-- Este ROLLBACK afecta a las dos transacciones.

ROLLBACK

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

Puntos de recuperacion (SavePoint).

Los puntos de recuperación (SavePoints) permiten manejar las transacciones por pasos, pudiendohacer rollbacks hasta un punto marcado por el savepoint y no por toda la transacción.

El siguiente ejemplo muestra como trabajar con puntos de recuperación.

BEGIN

TRAN

UPDATE

EMPLEADOS

SET

NOMBRE = 'Devjoker'

WHERE



ID=101

UPDATE

EMPLEADOS

SET

APELLIDO1 = 'Devjoker.COM'

WHERE

ID=101

SAVE

TRANSACTION P1 -- Guardamos la transaccion (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 transaccion

COMMIT



Procedimientos almacenados en Transact SQL

Un procedimiento es un programa dentro de la base de datos que ejecuta una acción o conjunto deacciones especificas.

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 (numerico entero) oconjuntos de resultados.

Para crear un procedimiento almacenado debemos emplear la sentencia CREATE PROCEDURE.

CREATE

PROCEDURE <nombre_procedure> [@param1 <tipo>, ...]

AS

-- Sentencias del procedure

Para modificar un procedimiento almacenado debemos emplear la sentencia ALTER PROCEDURE.

ALTER

PROCEDURE <nombre_procedure> [@param1 <tipo>, ...]

AS

-- Sentencias del procedure

El siguiente ejemplo muestra un procedimiento almacenado, denominado spu_addCliente queinserta 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 laejecución del procedimiento almacenado es la primera instrucción del lote, podemos omitir el usode 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 ycontrolados 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 debemosespecificarlo a través de la palabra clave OUTPUT , tanto en la definición del procedure como en laejecució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 numericos enteros a través de la instrucciónRETURN. Normalmente debemos utilizar los valores de retorno para determinar si la ejecución delprocedimiento ha sido correcta o no. Si queremos obtener valores se recomienda utilizarparámetros de salida o funciones escalares (se verán mas adelante en este curso).

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 caracteristica muy interesante de los procedimientos almacenados en Transact SQL es quepueden devolver uno o varios conjuntos de resultados.

El siguiente ejemplo muestra un procedimiento almacenado que devuelve un conjunto deresultados.

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 ejecucion ...

NUMCUENTA SALDO_ANTERIOR SALDO_POSTERIOR IMPORTE FXMOVIMIENTO------------ -------------- ---------------- ------- -----------------------200700000001 50.99 100.99 50.00 2007-08-25 16:18:36.490200700000001 0.99 50.99 50.00 2007-08-23 16:20:41.183200700000001 50.99 0.99 50.00 2007-08-23 16:16:29.840200700000001 0.99 50.99 50.00 2007-08-23 16:14:05.900



Funciones en Transact SQL

SQL Server proporciona al usuario la posibilidad de definir sus propias funciones, conocidad comoUDF (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 <Scalar_Function_Name, sysname, FunctionName>

(

-- Lista de parámetros

<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>, ...

)-- Tipo de datos que devuelve la función.

RETURNS

<Function_Data_Type, ,int>

AS

BEGIN

...

END

El siguiente ejemplo muestra como 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 quepara utilizar una función escalar debemos identificar el nombre de la función con el propietario dela misma.

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

SELECT

IDCUENTA,

NUMCUENTA

,

SALDO

,

FXALTA

,

-- Ejecucion de la funcion:

dbo.fn_MultiplicaSaldo( NUMCUENTA, IDCUENTA) AS RESULTADO

FROM

CUENTAS

El siguiente ejemplo muestra como 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 seleccion y en la clausula where de las mismas.

Las funciones no pueden ejecutar sentencias INSERT o UPDATE.

Funciones en linea

Las funciones en linea son las funciones que devuelven un conjunto de resultados correspondientesa la eecución de una sentencia SELECT.

La sintaxis para una función de tabla en linea es la siguiente:

CREATE

FUNCTION <Inline_Function_Name, sysname, FunctionName>

(


<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,...

)

RETURNS

http://www.devjoker.com/contenidos/Tutorial-de-Transact-SQL/238/Procedimientos-almacenados-en-Transact-SQL.aspx






TABLE

AS

RETURN

(

-- Sentencia Transact SQL

)

El siguiente ejemplo muestra como crear una función en linea.

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 clausula ORDER BY en la sentencia de una función el línea.

Las funciones en linea pueden utilizarse dentro de joins o querys como si fueran una tabla normal.

SELECT

* FROM fn_MovimientosCuenta('200700000001')

SELECT

*

FROM

CUENTAS

INNER

JOIN CUENTAS_CLIENTEON CUENTAS_CLIENTE.IDCUENTA = CUENTAS.IDCUENTA

INNER

JOIN CLIENTESON CLIENTES.id = CUENTAS_CLIENTE.IDCLIENTE

INNER

JOIN fn_MovimientosCuenta('200700000001') AON A.IDCUENTA= CUENTAS.IDCUENTA

Funciones en línea de multiples sentencias

Las funciones en línea de multiples sentencias son similares a las funciones en línea excepto que elconjunto de resultados que devuelven puede estar compuesto por la ejecución de varios consultasSELECT.

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 <Table_Function_Name, sysname, FunctionName>

(


<@param1, sysname, @p1> <data_type_for_param1, , int>, ...

)

RETURNS

-- variable de tipo tabla y su estructura

<

@Table_Variable_Name, sysname, @Table_Var> TABLE

(

<Column_1, sysname, c1> <Data_Type_For_Column1, , int>,

<Column_2, sysname, c2> <Data_Type_For_Column2, , int>

)

AS

BEGIN

-- Sentencias que cargan de datos la tabla declarada

RETURN

END

El siguiente ejemplo muestra el uso de una funcion de tabla de multi sentencias.



/* Esta funcion 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 funcion.

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 Saldo Saldo_anterior Saldo_posterior Importe_Movimiento FxMovimiento------------ ------ --------------- ---------------- ------------------- -----------------------200700000002 500.00 NULL NULL NULL NULLNULL NULL 550.00 500.00 50.00 2007-08-25 16:18:36.490NULL NULL 600.00 550.00 50.00 2007-08-23 16:20:41.183NULL NULL 600.00 550.00 50.00 2007-08-23 16:14:05.900

200700000001 100.99 NULL NULL NULL NULLNULL NULL 50.99 100.99 50.00 2007-08-25 16:18:36.490NULL NULL 0.99 50.99 50.00 2007-08-23 16:20:41.183NULL NULL 50.99 0.99 50.00 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 unamplio abanico de posibilidades. Mostramos aqui algunas de las frecuentes. Podemos acceder allistado 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 ] )

Donde:

data_type, es el tipo de destino al que queremos convertir la expresion expresion, la expresion que queremos convertir

style, parametro opcional que especifica el formato que tiene expresion. Por ejemplo, si queremosconvertir un varchar a datetime, aqui 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 esta escrita la fecha

-- 103 => dd/mm/aa

SET

@fecha = CONVERT(datetime, '19/03/2008',103)

SELECT

@fecha

DECLARE

@fecha datetime,

http://technet.microsoft.com/es-es/library/ms187786.aspx






@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 101 EE.UU. mm/dd/aaaa2 102 ANSI aa.mm.dd3 103 Británico/Francés dd/mm/aa4 104 Alemán dd.mm.aa5 105 Italiano dd-mm-aa6 106 (1) - dd mes aa7 107 (1) - Mes dd, aa

8 108 - hh:mi:ss

- 9 o 109 (1, 2) Valor predeterminado +

milisegundosmes dd aaaa hh:mi:ss:mmma.m.

(o p. m.)10 110 EE.UU. mm-dd-aa

11 111 JAPÓN aa/mm/dd12 112 ISO aammdd

- 13 o 113 (1, 2)Europeo predeterminado +

milisegundosdd mes aaaa hh:mi:ss:mmm(24h)

14 114 - hh:mi:ss:mmm(24h)

- 20 o 120 (2) ODBC canónico aaaa-mm-dd hh:mi:ss(24h)

- 21 o 121 (2) ODBC canónico (conmilisegundos)

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

- 126 (4) ISO8601 aaaa-mm-ddThh:mi:ss.mmm (sinespacios)

127(6, 7) ISO8601 con zona horaria Z. aaaa-mm-ddThh:mi:ss.mmmZ(sin espacios)

- 130 (1, 2) Hijri (5) dd mes aaaa hh:mi:ss:mmma.m.- 131 (2) Hijri (5) 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 reemplazoespecificado. 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 encuenta 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 SQLServer 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 ejecutaautomá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 oDELETE de una tabla o vista.

Trigger DDL , se ejecutan en respuesta a una variedad de eventos de lenguaje de definición dedatos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROPde Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutanoperaciones de tipo DDL.

Trigger DML.

Los trigger DML se ejecutan cuando un usuario intenta modificar datos mediante un evento delenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE oDELETE de una tabla o vista.

La sintaxis general de un trigger es la siguiente.

CREATE

TRIGGER <Trigger_Name, sysname, Trigger_Name>

ON <Table_Name, sysname, Table_Name>

AFTER

<Data_Modification_Statements, , INSERT,DELETE,UPDATE>

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 tablasinserted 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 ellaestá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 valoresanteriores 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) einsertar los nuevos (inserted). La sentencia UPDATE es la única en la que inserted y deleted tienen datos simultaneamente.

No puede se modificar directamente los datos de estas tablas.

El siguiente ejemplo, graba un historico de saldos cada vez que se modifica un saldo de la tablacuentas.

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 que columnas de la tabla debe afectar el trigger.

ALTER

TRIGGER TR_CUENTAS

ON CUENTAS

AFTER

UPDATE

AS

BEGIN





SET NOCOUNT ON;

IF UPDATE(SALDO) -- Solo si se actualiza SALDO

BEGIN




FROM INSERTED

END

END

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

ALTER

TRIGGER TR_CUENTAS

ON CUENTAS

AFTER

UPDATE

AS

BEGIN





SET NOCOUNT ON;




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 tarvé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 dedatos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROPde Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutanoperaciones de tipo DDL.

La sintaxis general de un trigger es la siguiente.

CREATE

TRIGGER <trigger_name, sysname, table_alter_drop_safety>

ON

DATABASE

FOR <data_definition_statements, , DROP_TABLE, ALTER_TABLE>

AS

BEGIN

...

END

La siguiente instrucción impide que se ejecuten sentencias DROP TABLE y ALTER TABLE en la basede 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 atravé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

<nombre_cursor> CURSOR

FOR

<

sentencia_sql>

-- apertura del cursor

OPEN

<nombre_cursor>

-- Lectura de la primera fila del cursor

FETCH

<nombre_cursor> INTO <lista_variables>

WHILE



(@@FETCH_STATUS = 0)

BEGIN

-- Lectura de la siguiente fila de un cursor

FETCH <nombre_cursor> INTO <lista_variables>

...

END

-- Fin del bucle WHILE

-- Cierra el cursor

CLOSE

<nombre_cursor>

-- Libera los recursos del cursor

DEALLOCATE

<nombre_cursor>

El siguente 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


DECLARE

cClientes CURSOR FOR

SELECT

Id, Nombre, Apellido1, Apellido2, NifCif , FxNacimiento

FROM

CLIENTES

-- Apertura del cursor

OPEN

cClientes


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 funcion @@FETCH_STATUS nos indica el estado de la últimainstrucción FETCH emitida, los valores posibles son:

Valordevuelto

Descripción

0 La instrucción FETCH se ejecutó correctamente.

-1La instrucción FETCH no se ejecutó correctamente o la fila estaba más allá del

conjunto de resultados.-2 Falta la fila recuperada.

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

DECLARE

<nombre_cursor> CURSOR

[ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]



[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR <sentencia_sql>

El primer conjunto de parámetros que podemos especificar es [ LOCAL | GLOBAL ]. A continuaciónmostramos el significado de cada una de estas opciones.

LOCAL Especifica que el ámbito del cursor es local para el proceso por lotes, procedimiento almacenadoo 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 alnombre del cursor en cualquier procedimiento almacenado o proceso por lotes que se ejecute enla conexión.

DECLARE

cClientes CURSOR GLOBAL FOR

SELECT




Apellido2


FROM

CLIENTES

Si no se especifica GLOBAL ni LOCAL, el valor predeterminado se controla mediante laconfiguració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 ]. Acontinuació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


Apellido2


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 únicaopción de recuperación que se admite es NEXT. No es posible especificar SCROLL si se incluyetambién FAST_FORWARD.Si se incluye la opción SCROLL, la forma en la realizamos la lectura del cursor varia, debiendoutilizar la siguiente sintaxis: FETCH [ NEXT | PRIOR | FIRST | LAST | RELATIVE | ABSOLUTE ]FROM < INTO




DECLARE

@Id int,

@Nombre

varchar(255),

@Apellido1

varchar(255),

@Apellido2

varchar(255),

@NifCif

varchar(20),

@FxNacimiento

datetime


DECLARE

cClientes CURSOR SCROLL FOR

SELECT


Apellido2


FROM

CLIENTES




OPEN

cClientes


FETCH

NEXT FROM cClientes

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

WHILE


BEGIN

PRINT @Nombre + ' ' + @Apellido1 + ' ' + @Apellido2


FETCH NEXT FROM cClientes

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

END

-- Lectura de la fila anterior

FETCH

PRIOR FROM cClientesINTO @id, @Nombre, @Apellido1, @Apellido2, @NifCif , @FxNacimiento

PRINT

@Nombre + ' ' + @Apellido1 + ' ' + @Apellido2


CLOSE



cClientes


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 solicitudesque se realizan al cursor se responden desde esta tabla temporal de tempdb; por tanto, lasmodificaciones realizadas en las tablas base no se reflejan en los datos devueltos por lasoperaciones de recuperación realizadas en el cursor y además este cursor no admitemodificaciones.

DECLARE

cClientes CURSOR STATIC FOR

SELECT


Apellido2


FROM

CLIENTES

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

keyset de tempdb.

DECLARE

cClientes CURSOR KEYSET FOR



SELECT


Apellido2


FROM

CLIENTES

DYNAMIC Define un cursor que, al desplazarse por él, refleja en su conjunto de resultados todos loscambios realizados en los datos de las filas. Los valores de los datos, el orden y la pertenencia delas 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


Apellido2


FROM

CLIENTES

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

FOR_UPDATE.

DECLARE

cClientes CURSOR FAST_FORWARD FOR



SELECT


Apellido2


FROM

CLIENTES

En SQL Server 2000, las opciones de cursor FAST_FORWARD y FORWARD_ONLY se excluyenmutuamente. Si se especifican ambas, se genera un error. En SQL Server 2005, las dos palabrasclave 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 alcursor en una cláusula WHERE CURRENT OF de una instrucción UPDATE o DELETE. Esta opciónreemplaza la capacidad de actualizar el cursor.

DECLARE

cClientes CURSOR READ_ONLY FOR

SELECT


Apellido2


FROM

CLIENTES

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



DECLARE

cClientes CURSOR SCROLL_LOCKS FOR

SELECT


Apellido2


FROM

CLIENTES

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

DECLARE

cClientes CURSOR OPTIMISTIC FOR

SELECT


Apellido2


FROM

CLIENTES

Por último, queda la opción TYPE_WARNING



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

DECLARE

cClientes CURSOR TYPE_WARNING FOR

SELECT


Apellido2


FROM

CLIENTES

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

DECLARE

cClientes CURSOR LOCAL STATIC TYPE_WARNING FOR

SELECT


Apellido2


FROM

CLIENTES

Para actualizar los datos de un cursor debemos especificar FOR UPDATE despues de la sentenciaSELECT en la declaración del cursor, y WHERE CURRENT OF <nombre_cursor> en la sentenciaUPDATE tal y como muestra el siguiente ejemplo.




DECLARE

@Id int,

@Nombre

varchar(255),

@Apellido1

varchar(255),

@Apellido2

varchar(255),

@NifCif

varchar(20),

@FxNacimiento

datetime


DECLARE

cClientes CURSOR FOR

SELECT


Apellido2


FROM

CLIENTES

FOR

UPDATE




OPEN

cClientes


FETCH

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

WHILE


BEGIN

UPDATE Clientes

SET APELLIDO2 = isnull(@Apellido2,'') + ' - Modificado'

WHERE CURRENT OF cClientes


FETCH cClientesINTO @id, @Nombre, @Apellido1, @Apellido2,

@NifCif , @FxNacimiento

END


CLOSE

cClientes


DEALLOCATE

cClientes



SQL dinámico en Transact SQL

Transact SQL permite dos formas de ejecutar SQL dinamico(construir sentencias SQLdinamicamente 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 detrabajar con ambos métodos.

La instrucción EXECUTE

La instrucción EXECUTE - o simplemente EXEC - permite ejecutar una cadena de caracteres querepresenta 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 dinamico 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 incoveniente de trabajar con la instrucción EXEC es que no permite el uso deparametros abriendo la puerta a potenciales ataques de Sql Injections -http://www.devjoker.com/contenidos/Articulos/45/Seguridad-en-Internet--SQL-Injections.aspx

Además el uso de la instrucción EXEC es menos eficiente, en terminos de rendimiento, quesp_executesql.

Para solventar el problema debemos trabajar siempre con sq_executesql, que permite el uso deparametros 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.

http://www.devjoker.com/contenidos/Articulos/45/Seguridad-en-Internet--SQL-Injections.aspx





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ámetroespecificado 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

Documents

Transact SQL