Introducción a Transact SQL

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.  Para abordar el presente tutorial con mínimo de garantias
es necesario conocer previamente SQL.

Podemos acceder a un completo tutorial de SQL desde AQUI.

Transact SQL es el lenguaje de programación que proporciona SQL Server para ampliar SQL con los
elementos característicos de los lenguajes de programación: variables, sentencias de control de flujo,
bucles ...

Cuando se desea realizar una aplicación completa para el manejo de una base de datos relacional,
resulta necesario utilizar alguna herramienta que soporte la capacidad de consulta del SQL y la
versatilidad de los lenguajes de programación tradicionales. Transact SQL es el lenguaje de
programación que proporciona SQL Server para extender el SQL estándar con otro tipo de instrucciones.

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

¿Que vamos a necesitar?

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

 Un servidor SQL Server 2005. Podemos descargar gratuitamente la versión SQL Server Express
desde el siguiente enlace.SQL Server 2005 Express.
 Herramientas cliente de SQL Server. Recomendamos:
o Microsoft SQL Server Management Studio
o Toad para SQL Server

La instalación y configuración de SQL Server está fuera del alcance de este tutorial. Si bien podemos
acceder a una guía básica de instalación desde este enlace.

Fundamentos de Transact SQL

Primeros pasos con Transact SQL 

Para programar en Transact SQL es necesario conocer sus fundamentos.

Como introducción vamos a ver algunos elementos y conceptos básicos del lenguaje.

 Transact SQL no es CASE-SENSITIVE, es decir, no diferencia mayúsculas de minúsculas como

otros lenguajes de programación como C o Java.
 Un comentario es una aclaración que el programador incluye en el código. Son soportados 2
estilos de comentarios, el de línea simple y de multilínea, para lo cual son empleados ciertos
caracters especiales como son:
o -- Para un comentario de línea simple
o /* ... */ Para un comentario de varias líneas
  Un literal es un valor fijo de tipo numérico, caracter, cadena o lógico no representado por un
identificador (es un valor explícito).
 Una variable es un valor identificado por un nombre (identificador) sobre el que podemos
realizar modificaciones. En Transact SQL los identificadores de variables deben comenzar por el
caracter @, es decir, el nombre de una variable debe comenzar por @.Para declarar variables en
Transact SQL debemos utilizar la palabra clave declare, seguido del identificador y tipo de datos
de la variable.

    Veamos algunos ejemplos:

-- Esto es un comentario de linea simple

/*
Este es un comentario con varias líneas.
Conjunto de Lineas.
*/
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

Scripts y lotes.

Un script de Transact SQL es un conjunto de sentencias de Transact SQL en formato de texto

plano que se ejecutan en un servidor de SQL Server.

Un script está compuesto por uno o varios lotes. Un lote delimita el alcance de las variables y sentencias
del script. Dentro de un mismo script se diferencian los diferentes lotes a través de las instrucción GO.

-- Este es el primer lote del script

SELECT * FROM COMENTARIOS
GO -- GO es el separador de lotes
-- Este es el segundo lote del script
SELECT getdate() -- getdate() es una función integrada que devuelve
-- la fecha
 Componentes del lenguaje SQL.

Tipos de datos.

SQL admite una variada gama de tipos de datos para el tratamiento de la información contenida en las
tablas, los tipos de datos pueden ser numéricos (con o sin decimales), alfanuméricos, de fecha o
booleanos (si o no). Según el gestor de base de datos que estemos utilizando los tipos de datos varían,
pero se reducen básicamente a los expuestos anteriormente, aunque en la actualidad casi todos los
gestores de bases de datos soportan un nuevo tipo, el BLOB (Binary Large Object), que es un tipo de
datos especial destinado a almacenar archivos, imágenes...

Dependiendo de cada gestor de bases de datos el nombre que se da a cada uno de estos tipos puede
variar. Básicamente tenemos los siguientes tipos de datos.

Númericos Alfanúmericos Fecha Lógico BLOB

Integer char(n) Date Bit Image
Numeric(n.m) varchar(n,m) DateTime   Text
Decimal(n,m)         
Float        

Más detalladamente tenemos:

Tipos de datos númericos

Tipo Definción Bytes
Integer Valores enteros con signo. 4
Numeric(n,m) Números reales de hasta 18 digitos (con decimales), donde n representa 5-17
el total de dígitos admitidos (normalmente denominado precisión) y m el
número de posiciones decimales (escala).
Decimal(n,m) Igual que el tipo numeric. 5-17
Float Número de coma flotante, este tipo de datos se suele utilizar para los 4-8
valores en notación cientifica.
Tipos de datos alfanúmericos
Tipo Definción Bytes
char(n) Almacena de 1 a 255 caracteres alfanúmericos. Este valor viene dado por 0-255
n, y es el tamaño utilizado en disco para almacenar dato. Es decir si defino
un campo como char(255), el tamaño real del campo será de 255, aunque
el valor solo contenga 100. 
varchar(n) Igual que el tipo char, con la salvedad que varchar almacena únicamente 0-255
los bytes que contenga el valor del campo.
Nota:El tamaño del campo varía en función de cada base de datos, siendo 255 el valor
standart. En realidad el tamaño viene delimitado por el tamaño de las páginas de datos, para
SQL Server el límite esta en 8000 bytes (8000 caracteres), siempre y cuando tengamos definido
el tamaño de la página de datos a 8K
Tipos de datos fecha
Tipo Definción Bytes
Date Almacena fechas, con día, mes y año. 8
Datetime Almacena fechas con fecha y hora 4
 Nota:La aparición de los tipos de datos de fecha supuso una atentica revolución el mundo de la
bases de datos, en realidad, la base de datos almacena internamente números enteros, de hay
que el tamaño sea de 4 bytes y 8 bytes (2 enteros), pero aporta la validación del dato
introducido.
Tipos de datos lógicos
Tipo Definición Bytes
Bit Tipo bit. Almacena un 0 ó no cero, según las bases de datos será 1 ó -1. Se 1 bit
aplica la lógica booleana, 0 es falso y no cero verdadero.
Tipos de datos BLOB
Tipo Definición Bytes
Image Almacena imágenes en formato binario, hasta un máximo de 2 Gb de 0-2Gb
tamaño.
Text Almacena texto en formato binario, hasta un máximo de 2 Gb de tamaño. 0-2Gb

Operadores

Los operadores se pueden definir como combinaciones de caracteres que se utilizan tanto para realizar
asignaciones como comparaciones entre datos.

Los operadores se dividen en aritméticos, relacionales, lógicos, y concatenación.

Operadores SQL
Aritméticos + Suma
- Resta
* Producto
/ División
**   ^ Exponenciación
Relacionales <        Menor que
<= Menor o igual que
> Mayor que
>= Mayor o igual que
<>   !=  Distinto
!< No menor que
!> No mayor que
Lógicos AND Los operadores lógicos permiten comparar expresiones lógicas devolviendo
OR siempre un valor verdadero o falso.Los operadores lógicos se evaluan de
NOT izquierda a derecha.
Concatenación + Se emplea para unir  datos de tipo alfanúmerico.

Palabras Clave

Las palabras clave son identificadoras con un significado especial para SQL, por lo que no pueden ser
utilizadas para otro propósito distinto al que han sido pensadas.
SQL dispone de muy pocas órdenes, pero de múltiples palabras clave, lo que le convierten en un lenguaje
sencillo pero tremendamente potente para llevar a cabo su función.

PALABRAS CLAVE
ALL AND ANY ASC
AVG BEGIN BY CHAR
CHECK CLOSE COUNT COMMIT
CREATE CURSOR DECIMAL DECLARE
DELETE DESC DISTINCT DEFAULT
EXISTS FETCH FLOAT FOR
FROM GRANT GROUP HAVING
IN INDEX INSERT INTEGER
INTO LIKE MAX MIN
NOT NUMERIC ON OPEN
OR ORDER REVOKE ROLLBACK
SELECT SET SUM TABLE
UNION UNIQUE UPDATE USER
VALUES VIEW WHERE WITH

Funciones Agregadas

Las funciones agregadas proporcionan a SQL utilidades de cálculo sobre los datos de las tablas.

Estas funciones se incorporan en las consultas SELECT y retornan un único valor al operar sobre un grupo
de registros.

Las funciones agregadas son.

Funciones Agregadas
MAX() Devuelve el valor máximo.
MIN() Devuelve el valor mínimo.
SUM() Devuelve el valor de la suma de los valores del campo.
COUNT() Devuelve el número de filas que cumplen la condición
AVG() Devuelve el promedia de los valores del campo

Predicados

Los predicados son condiciones que se indican en clausula WHERE de una consulta SQL. La siguiente
tabla ilustra los predicados de SQL.

Predicados SQL
BETWEEN...AND Comprueba que al valor esta dentro de un intervalo
LIKE Compara un campo con una cadena alfanumérica. LIKE admite el uso de caracteres
comodines
ALL Señala a todos los elementos de la selección de la consulta
ANY Indica que la condición se cumplirá si la comparación es cierta para al menos un
elemento del conjunto.
 EXISTS Devuelve un valor verdadero si el resultado de una subconsulta devuelve resultados.
IN Comprueba si un campo se encuentra dentro de un determinado rango. El rango puede
ser una sentencia SELECT.

No se preocupe si no entiende el significado de alguno de los términos que hemos presentado aquí,
pronto veremos ejemplos que nos aclararán las cosas, de momento nos vale con saber que existen.

Operadores en Transact SQL

La siguiente tabla ilustra los operadores de Transact SQL .

Tipo de operador Operadores

Operador de asignación =
Operadores aritméticos + (suma)
- (resta)
* (multiplicación)
/ (división)
** (exponente)
% (modulo)
Operadores relacionales o de = (igual a)
comparación <> (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 +
ALL (Devuelve TRUE si el conjunto completo de comparaciones es TRUE)
ANY(Devuelve TRUE si cualquier elemento del conjunto de comparaciones es TRUE)
BETWEEN (Devuelve TRUE si el operando está dentro del intervalo)
EXISTS (TRUE si una subconsulta contiene filas)
Otros
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)

Equivalencia de datos de SQL Server y .NET

La siguiente lista muestra los tipos de datos de SQL Server 2005 y sus equivalentes con CRL, para el
namespace System.Data.SqlTypes y los tipos nativos de CRL .NET FrameWork
 CLR data type (.NET
SQL Server CLR data type (SQL Server)
Framework)
varbinary SqlBytes, SqlBinary Byte[]
binary SqlBytes, SqlBinary Byte[]
varbinary(1),
SqlBytes, SqlBinary byte, Byte[]
binary(1)
image ninguno ninguno
varchar ninguno ninguno
char ninguno ninguno
nvarchar(1),
SqlChars, SqlString Char, String, Char[]
nchar(1)
SqlChars, SqlString
nvarchar String, Char[]
SQLChars es mejor para la transferencia de datos y SQLString
obtiene mejor rendimiento para operaciones con Strings.
nchar SqlChars, SqlString String, Char[]
text ninguno ninguno
ntext ninguno ninguno
uniqueidentifier 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-defined Misma clase que la definida
ninguno
type(UDT) en el assemblie.
table ninguno ninguno
cursor ninguno ninguno
timestamp ninguno ninguno
xml SqlXml ninguno

Tipos de datos en Transact SQL

Cuando definimos una tabla, variable o constante debemos asignar un tipo de dato que indica los
posibles valores. El tipo de datos define el formato de almacenamiento, espacio que de 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 de datos
manejados por Transact SQL son similares a los soportados por SQL.
Tipos de datos numéricos.

SQL Server dispone de varios tipos de datos numéricas. Cuanto mayor sea el número que puedan
almacenar mayor será en consecuencia el espacio utilizado para almacenarlo. Como regla general se
recomienda usar el tipo de dato mínimo posible. Todos los datos numéricos admiten el valor NULL.

Bit. Una columna o variable de tipo bit puede almacenar el rango de valores de  1 a 0.

Tinyint. Una columna o variable de tipo tinyint puede almacenar el rango de valores de 0 a 255.

SmallInt. Una columna o variable de tipo smallint puede almacenar el rango de valores -32768 a 32767.

Int. Una columna o variable de tipo int puede almacenar el rango de valores -231 a 231-1 .

BigInt. Una columna o variable de tipo bigint puede almacenar el rango de valores -2 63 a 263-1 .

Decimal(p,s). Una columna de tipo decimal puede almacenar datos númericos decimales sin redondear.
Donde p es la precision (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-10 308 a 1,79x-10308, , si la
definimos con el valor máxmo 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-10 38 a 3,4x-1038, 

Money. Almacena valores númericos monetarios de -2 63  a 263-1, con una precisión de hasta diexz
milesimas de la unidad monetaria. 

SmallMoney. Almacena valores númericos monetarios de -214.748,3647 a 214.748,3647, con una

precisión de hasta diez milesimas 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 @smallint
set @int = 642325
print @int
set @decimal = 56565.234 -- Punto como separador decimal
print @decimal
set @money = 12.34
print @money

Tipos de datos de caracter.

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

Varchar(n).Almacena n caracteres en formato ASCII, un byte por cada letra. Cuando almacenamos datos
en el tipo varchar, unicamente se utilizan los caracteres necesarios,Por ejemplo, si en un varchar(255),
guardamos el valor 'A', se almacena 'A', ocupando solo un byte bytes.

Varchar(max). Igual que varchar, pero al declararse como max puede almacenar 2 31-1 bytes.

Nchar(n).Almacena n caracteres en formato UNICODE, dos bytes por cada letra. Es recomendable utilizar
este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer a diferente idomas.

Nvarchar(n).Almacena n caracteres en formato UNICODE, dos bytes por cada letra. Es recomendable

utilizar este tipo de datos cuando los valores que vayamos a almacenar puedan pertenecer a diferente
idomas.

Nvarchar(max).Igual que varchar, pero al declararse como max puede almacenar 2 31-1 bytes.  

Tipos de datos de fecha.

Datetime. Almacena fechas con una precision de milisegundo. Debe usarse para fechas muy específicas.

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

TimeStamp.Se utiliza para marcar un registro con la fecha de inserción - actualización. El tipo timestamp
se actualiza automáticamente cada vez que insertamos o modificamos los datos.

Tipos de datos binarios.

Binary. Se utiliza para almacenar datos binarios de longitud fija, con una longitud máxima de 8000 bytes.

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

Tipo de datos XML.

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

 DECLARE @myxml XML

 
 set @myxml = (SELECT @@SERVERNAME NOMBRE FOR XML RAW, TYPE)
 
 print cast(@myxml as varchar(max))

Obtendremos la siguiente salida:    <row nombre="SVR01"/>

Otros tipos de datos.

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

 DECLARE @myuniqueid UNIQUEIDENTIFIER

 
 set @myuniqueid = NEWID()
 
 print cast(@myuniqueid as varchar(36))

Obtendremos la siguiente salida: 46141D79-102C-4C29-A620-792EA0208637

Sql_Variant.Permite almacenar valores de diferentes tipos de datos. No puede almacena varchar(max),

xml, timestamp y tipos de datos definidos por el usuario. 

Tipos de datos personalizados.

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

CREATE TYPE MD5 FROM CHAR(32) NULL

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

Operaciones con conjuntos.

    SQL Server 2005 permite tres tipos de operaciones con conjuntos:

 UNION, disponible en todas las versiones de SQL Server.

 EXCEPT, nuevo en SQL Server 2005.
 INTERSECT, nuevo en SQL Server 2005.

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

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

UNION

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

El siguiente ejemplo muestra el uso de UNION

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento

FROM EMPLEADOS

UNION

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento

FROM CLIENTES

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

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento

FROM EMPLEADOS

UNION ALL

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento

FROM CLIENTES

EXCEPT

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

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento

FROM EMPLEADOS

EXCEPT

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento

FROM CLIENTES

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

INTERSECT

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

El siguiente ejemplo muestra el uso de INTERSECT

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento

FROM EMPLEADOS

INTERSECT

SELECT Nombre, Apellido1 , Apellido2, NifCif, FxNacimiento

FROM CLIENTES

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 tabla
llamada familias (representaría familias de productos por ejemplo).

SELECT CO_FAMILIA, FAMILIA

FROM FAMILIAS

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

SELECT *

FROM FAMILIAS

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

SELECT *
FROM FAMILIAS

INNER JOIN CATEGORIAS

ON CATEGORIAS.CO_FAMILIA = FAMILIAS.CO_FAMILIA

INNER JOIN PRODUCTOS

ON PRODUCTOS.CO_CATEGORIA = CATEGORIAS.CO_CATEGORIA

La combinación se realiza a través de la clausula INNER JOIN, que es una cláusula exclusiva, es decir las
familias que no tengan categorías y productos asociados no se devolverán.

Si queremos realizar la consulta para que no sea exclusiva, tenemos que utilizar LEFT JOIN. El uso de la
palabra reservada OUTER es opcional.

SELECT *

FROM FAMILIAS

LEFT OUTER JOIN CATEGORIAS

ON CATEGORIAS.CO_FAMILIA = FAMILIAS.CO_FAMILIA

LEFT OUTER JOIN PRODUCTOS

ON PRODUCTOS.CO_CATEGORIA = CATEGORIAS.CO_CATEGORIA
Los registros que no tengan datos relacionados en una consulta LEFT JOIN devolverán en valor null en los
campos que correspondan a las tablas en las que no tienen dato.

 También podemos forzar un producto cartesiano (todos con todos) a través de CROSS JOIN.

SELECT * FROM FAMILIAS

CROSS JOIN CATEGORIAS

La cláusula WHERE

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

SELECT CO_FAMILIA, FAMILIA

FROM FAMILIAS

WHERE CO_FAMILIA = 1

Por supuesto, podemos especificar varias condiciones para el WHERE:

SELECT *

FROM FAMILIAS

WHERE CO_FAMILIA = 1

OR CO_FAMILIA = 2

    Podemos agrupar varios valores para una condición en la 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 ir entre
comillas simples:

SELECT *

FROM FAMILIAS

WHERE FAMILIA = 'FAMILIA 1'

Para consultar campos alfanuméricos, es decir, campos de texto podemos utilizar el operador LIKE
conjuntamente con comodines.

SELECT *

FROM FAMILIAS

WHERE FAMILIA LIKE 'FAM%'

    Los comodines que podemos utilizar en son los siguientes:

 % , representa cualquier cadena de texto de cero o más caracteres de cualquier longitud.

 _ , representa un 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. La clausula
TOP admite como parámetros un valor numérico entero o un porcentaje (sólo a partir de la version
2005)

SELECT TOP 10 * -- Devuelve 10 registros

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

FROM FAMILIAS

 La clausula TOP se puede combinar con WITH TIES en consultas agregadas.

La cláusula ORDER BY

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

FROM FAMILIAS

ORDER BY FAMILIA DESC

    También podemos indicar el índice del campo en la lista de selección en lugar de su nombre :

 SELECT CO_FAMILIA, FAMILIA

FROM FAMILIAS

ORDER BY 2 DESC -- Ordena por FAMILIA

Consultas combinadas. JOINS

Consultas combinadas.

Habitualmente cuando necesitamos recuperar la información de una base de datos nos encontramos
con que dicha información se encuentra repartida en varias tablas, referenciadas a través de varios
códigos. De este modo si tuviéramos una tabla de ventas con un campo cliente, dicho campo contendría
el código del cliente de la tabla de cliente. 

 Sin embargo está forma de almacenar la información no resulta muy útil a la hora de consultar los datos.
SQL nos proporciona una forma fácil de mostrar la información repartida en varias tablas, las consultas
combinadas o JOINS.

    Las consultas combinadas pueden ser de tres tipos:

 Combinación interna
 Combinación externa
 Uniones

Combinación interna.

La combinación interna nos permite mostrar los datos de dos o más tablas a través de una condición
WHERE.

Si recordamos los ejemplos de los capitulos anteriores tenemos una tabla de coches, en la que tenemos
referenciada la marca a través del código de marca. Para realizar la consulta combinada entre estas
dos tablas debemos escribir una consulta SELECT  en cuya claúsula FROM escribiremos el nombre de las
dos tablas, separados por comas, y una condición WHERE que obligue a que el código de marca de la
tabla de coches sea igual al código de la tabla de marcas.

Lo más sencillo es ver un ejemplo directamente:

 SELECT tCoches.matricula,
              tMarcas.marca, 
              tCoches.modelo,
              tCoches.color, 
              tCoches.numero_kilometros, 
              tCoches.num_plazas
FROM tCoches, tMarcas
WHERE tCoches.marca = tMarcas.codigo

    La misma consulta de forma "visual" ...

Démonos cuenta que hemos antepuesto el nombre de cada tabla a el nombre del campo, esto no es
obligatorio si los nombres de campos no se repiten en las tablas, pero es aconsejable para evitar
conflictos de nombres entre campos. Por ejemplo, si para referirnos al campo marca no anteponemos el
nombre del campo la base de datos no sabe si queremos el campo marca de la tabla tCoches, que
contiene el código de la marca, o el campo marca de la tabla tMarcas, que contiene el nombre de la
marca.

Otra opción es utilizar la cláusula INNER JOIN. Su sintaxis es idéntica a la de una consulta SELECT
habitual, con la particularidad de que en la cláusula FROM sólo aparece una tabla o vista, añadiéndose el
resto de tablas a través de cláusulas INNER JOIN.

SELECT [ALL | DISTINCT ]

             <nombre_campo> [{,<nombre_campo>}]
FROM <nombre_tabla> 
[{INNER JOIN  <nombre_tabla> ON <condicion_combinacion>}]
[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 ]}]]

    El ejemplo anterior escrito utilizando la clausula INNER JOIN  quedaría de la siguiente manera:

SELECT tCoches.matricula,
       tMarcas.marca, 
       tCoches.modelo,
       tCoches.color, 
       tCoches.numero_kilometros, 
        tCoches.num_plazas
FROM tCoches
INNER JOIN tMarcas ON tCoches.marca = tMarcas.codigo

La cláusula INNER JOIN permite separar completamente las condiciones de combinación con otros
criterios, cuando tenemos consultas que combinan nueve o diez tablas esto realmente se agradece. Sin
embargo muchos programadores no son amigos de la cláusula INNER JOIN, la razón es que uno de los
principales gestores de bases de datos, ORACLE, no la soportaba. Si nuestro programa debía trabajar
sobre bases de datos ORACLE no podíamos utilizar INNER JOIN. A partir de la versión ORACLE 9i oracle
soporta la cláusula INNER JOIN.

Combinación Externa

La combinación interna es excluyente. Esto quiere decir que si un registro no cumple la condición de
combinación no se incluye en los resultados. De este modo en el ejemplo anterior si un coche no tiene
grabada la marca no se devuelve en mi consulta.

 Según la naturaleza de nuestra consulta esto puede ser una ventaja, pero en otros casos significa un
serio problema. Para modificar este comportamiento SQL pone a nuestra disposición la combinación
externa. La combinación externa no es excluyente.

 La sintaxis es muy parecida a la combinación interna,

SELECT [ALL | DISTINCT ]

             <nombre_campo> [{,<nombre_campo>}]
FROM <nombre_tabla> 
[{LEFT|RIGHT OUTER JOIN  <nombre_tabla> ON <condicion_combinacion>}]
[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 ]}]]

La combinación externa puede ser diestra o siniestra, LEFT OUTER JOIN o RIGHT OUTER JOIN.  Con LEFT 
OUTER JOIN obtenemos todos los registros de la tabla que situemos a la izquierda de la clausula JOIN,
mientras que con RIGHT OUTER JOIN obtenemos el efecto contrario.

Como mejor se ve la combinación externa es con un ejemplo.

SELECT tCoches.matricula,
       tMarcas.marca, 
       tCoches.modelo,
       tCoches.color, 
       tCoches.numero_kilometros, 
       tCoches.num_plazas
FROM tCoches
LEFT OUTER JOIN tMarcas ON tCoches.marca = tMarcas.codigo
Esta consulta devolverá todos los registros de la tabla tCoches, independientemente de que tengan
marca o no. En el caso de que el coche no tenga marca se devolverá el valor null para los campos de la
tabla tMarcas.

 Visualmente (la consulta devuelve los datos en azul) ...

El mismo ejemplo con RIGHT OUTER JOIN.

SELECT tCoches.matricula,
       tMarcas.marca, 
       tCoches.modelo,
       tCoches.color, 
       tCoches.numero_kilometros, 
       tCoches.num_plazas
FROM tCoches
RIGHT OUTER JOIN tMarcas ON tCoches.marca = tMarcas.codigo

Esta consulta devolverá los registros de la tabla tCoches que tengan marca relacionada y todos los
registros de la tabla tMarcas, tengan algún registro en tCoches o no.

 Visualmente (la consulta devuelve los datos en azul) ...

Union

La cláusula UNION permite unir dos o más conjuntos de resultados en uno detrás del otro como si se
tratase de una única tabla. De este modo podemos obtener los registros de más de una tabla "unidos".

La sintaxis corresponde a la de varias SELECT unidas a través de UNION, como se muestra a

continuación:

SELECT [ALL | DISTINCT ]

             <nombre_campo> [{,<nombre_campo>}]
FROM <nombre_tabla> 
 [{LEFT|RIGHT OUTER JOIN  <nombre_tabla> ON <condicion_combinacion>}]
[WHERE <condicion> [{ AND|OR <condicion>}]]
[GROUP BY <nombre_campo> [{,<nombre_campo >}]]
[HAVING <condicion>[{ AND|OR <condicion>}]]
{
UNION [ALL | DISTINCT ]
SELECT [ALL | DISTINCT ]
             <nombre_campo> [{,<nombre_campo>}]
FROM <nombre_tabla> 
[{LEFT|RIGHT OUTER JOIN  <nombre_tabla> ON <condicion_combinacion>}]
[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 ]}]]

    Para utilizar la clausula UNION debemos cumplir una serie de normas.

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

    El siguiente ejemplo muestra el uso de UNION

SELECT tCoches.matricula,
       tMarcas.marca, 
       tCoches.modelo,
       tCoches.color, 
       tCoches.numero_kilometros, 
       tCoches.num_plazas
FROM tCoches
INNER JOIN tMarcas ON tCoches.marca = tMarcas.codigo
UNION
SELECT tMotos.matricula,
       tMarcas.marca, 
       tMotos.modelo,
       tMotos.color, 
       tMotos.numero_kilometros,  0
FROM tMotos
INNER JOIN tMarcas ON tMotos.marca = tMarcas.codigo;
Puede observarse el uso de la constante cero en la segunda lista de selección para hacer coincidir el número y tipo de
campos que devuelve la consulta UNION.

Consultas agregadas
La cláusula GROUP BY

La clausula GROUP BY combina los registros con valores idénticos en un único registro. Para cada
registro se puede crear un valor agregado si se incluye una función SQL agregada, como por ejemplo Sum
o Count, en la instrucción SELECT. Su sintaxis es: 
 SELECT [ALL | DISTINCT ]
             <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 ]}]]

 GROUP BY es opcional. Si se utiliza GROUP BY pero no existe una función SQL agregada en la instrucción
SELECT se obtiene el mismo resultado que con una consulta SELECT DISTINCT. Los valores Null en los
campos GROUP BY se agrupan y no se omiten. No obstante, los valores Null no se evalúan en ninguna de
las funciones SQL agregadas.

Todos los campos de la lista de campos de SELECT deben incluirse en la cláusula GROUP BY o como
argumentos de una función SQL agregada.

SELECT marca, modelo, SUM(numero_kilometros)

FROM tCoches
GROUP BY marca, modelo

La cláusula HAVING

Una vez que GROUP BY ha combinado los registros, HAVING muestra cualquier registro agrupado por la
cláusula GROUP BY que satisfaga las condiciones de la cláusula HAVING. Se utiliza la cláusula WHERE
para excluir aquellas filas que no desea agrupar, y la cláusula HAVING para filtrar los registros una vez
agrupados.  

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

SELECT marca, modelo, SUM(numero_kilometros)

FROM tCoches
WHERE marca <> 'BMW'
GROUP BY marca, modelo
HAVING SUM(numero_kilometros)>100000

En el ejemplo anterior, no se cuentan los datos para todas las marcas menos "BMW", una vez que se han
contado, se evalua HAVING, y el conjunto de resultados devuelve solo aquellos modelos con más de
100.000 km.

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

AVG(<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. La media calculada
por Avg es la media aritmética (la suma de los valores dividido por el número de valores). La función Avg
no incluye ningún campo Null en el cálculo.

SELECT marca, modelo, AVG(numero_kilometros)

FROM tCoches

GROUP BY marca, modelo

    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 pueden incluir el
nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida
por el usuario pero no otras de las funciones agregadas de SQL). Puede contar cualquier tipo de datos
incluso texto.

Aunque expr puede realizar un cálculo sobre un campo, Count simplemente cuenta el número de
registros sin tener en cuenta qué valores se almacenan en los registros. La función Count no cuenta los
registros que tienen campos null a menos que expr sea el carácter comodín asterisco (*). Si utiliza un
asterisco, Count calcula el número total de registros, incluyendo aquellos que contienen campos null.
Count(*) es considerablemente más rápida que Count(Campo). No se debe poner el asterisco entre
dobles comillas ('*').

SELECT COUNT(*) FROM tCoches;

SELECT marca, COUNT(modelo)
FROM tCoches
GROUP BY marca;
SELECT marca, COUNT(DISTINCT modelo)
FROM tCoches
GROUP BY marca;

Max, Min

Devuelven el mínimo o el máximo de un conjunto de valores contenidos en un campo especifico de una

consulta. Su sintaxis es:

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

SELECT marca, modelo, MIN(numero_kilometros)

, MAX(numero_kilometros)
FROM tCoches
GROUP BY marca, modelo

Sum

Devuelve la suma del conjunto de valores contenido en un campo específico de una consulta. Su sintaxis
es:

SUM(<expr>)

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

SELECT marca, modelo, SUM(numero_kilometros)

FROM tCoches
GROUP BY marca, modelo

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, como por
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 los campos GROUP BY se
agrupan y no se omiten. No obstante, los valores Null no se evalúan en ninguna de las funciones SQL
agregadas.

Todos los campos de la lista de campos de SELECT deben incluirse en la cláusula GROUP BY o como
argumentos de una función SQL agregada.

El siguiente ejemplo realiza una "cuenta" de los datos que hay en la tabla PRODUCTOS.

SELECT COUNT(*)

FROM PRODUCTOS

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

SELECT CLIENTES.NOMBRE,

CLIENTES.APELLIDO1,

CLIENTES.APELLIDO2,

SUM(PRECIO) -- Total del pedido

FROM DETALLE_PEDIDO

INNER JOIN PEDIDOS

ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

GROUP BY CLIENTES.NOMBRE,

CLIENTES.APELLIDO1,

CLIENTES.APELLIDO2

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

SELECT CLIENTES.NOMBRE,

CLIENTES.APELLIDO1,

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

FROM DETALLE_PEDIDO

INNER JOIN PEDIDOS

ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

-- La 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 los datos, solo
los que cumplan una condición del agregado. Por ejemplo, podemos calcular el valor de las ventas por
producto, pero que solo queramos ver los datos de los producto que hayan vendido má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. Se utiliza la cláusula WHERE
para excluir aquellas filas que no desea agrupar, y la cláusula HAVING para filtrar los registros una vez
agrupados.  

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

SELECT CLIENTES.NOMBRE,

CLIENTES.APELLIDO1,

CLIENTES.APELLIDO2,

SUM(PRECIO) -- Total del pedido

FROM DETALLE_PEDIDO

INNER JOIN PEDIDOS

 ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

-- La 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 una

consulta. 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. La media calculada
por Avg es la media aritmética (la suma de los valores dividido por el número de valores). La función Avg
no incluye ningún campo Null en el cálculo.

SELECT CLIENTES.NOMBRE,

CLIENTES.APELLIDO1,

CLIENTES.APELLIDO2,

AVG(PRECIO) -- Promedio del pedido

 FROM DETALLE_PEDIDO

INNER JOIN PEDIDOS

ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

GROUP BY CLIENTES.NOMBRE,

CLIENTES.APELLIDO1,

CLIENTES.APELLIDO2

Count

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

COUNT(<expr>)

En donde expr contiene el nombre del campo que desea contar. Los operandos de expr pueden incluir el
nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida
por el usuario pero no otras de las funciones agregadas de SQL). Puede contar cualquier tipo de datos
incluso texto.

Aunque expr puede realizar un cálculo sobre un campo, Count simplemente cuenta el número de
registros sin tener en cuenta qué valores se almacenan en los registros. La función Count no cuenta los
registros que tienen campos null a menos que expr sea el carácter comodín asterisco (*). Si utiliza un
asterisco, Count calcula el número total de registros, incluyendo aquellos que contienen campos null.
Count(*) es considerablemente más rápida que Count(Campo).

SELECT COUNT(*)

FROM PEDIDOS
SELECT CLIENTES.NOMBRE, COUNT(*)

FROM PEDIDOS

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

GROUP BY CLIENTES.NOMBRE

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

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

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

SELECT CLIENTES.NOMBRE,

MIN(PEDIDOS.FX_ALTA),

MAX(PEDIDOS.FX_ALTA)

FROM PEDIDOS

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

GROUP BY CLIENTES.NOMBRE

Sum

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

SUM(<expr>)

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

SELECT CLIENTES.NOMBRE,

SUM(PEDIDOS.TOTAL_PEDIDO)

FROM PEDIDOS

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

GROUP BY CLIENTES.NOMBRE

Uso de Select TOP con consultas agregadas.

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

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

En este escenario es posible que queramos obtener los N valores que satisfagan una condicion. Por
ejemplo, queremos si queremos obtener los tres primeros clientes con mayores pedidos, usariamos una
consulta parecida a esta: 

SELECT TOP 3
CLIENTES.NOMBRE,
SUM(DETALLE_PEDIDO.PRECIO)

FROM DETALLE_PEDIDO

INNER JOIN PEDIDOS

ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

GROUP BY CLIENTES.NOMBRE

ORDER BY 2 -- SUM(DETALLE_PEDIDO.PRECIO_UNIDAD)

Sin embargo, puede darse el caso, de que el cuarto cliente devuelto por la consulta tenga un valor
agragado identico al tercero, (es decir, estan empatados). El uso de TOP 3 discriminaría el cuarto
registro. Para evitar este comportamiento, y que la consulta devuelva también al cuarto cliente
utilizamos la clausula WITH TIES.

SELECT TOP 3 WITH TIES

CLIENTES.NOMBRE,
SUM(DETALLE_PEDIDO.PRECIO)

FROM DETALLE_PEDIDO

INNER JOIN PEDIDOS

ON DETALLE_PEDIDO.CO_PEDIDO = PEDIDOS.CO_PEDIDO

INNER JOIN CLIENTES

ON PEDIDOS.CO_CLIENTE = CLIENTES.CO_CLIENTE

GROUP BY CLIENTES.NOMBRE

ORDER BY 2 -- SUM(DETALLE_PEDIDO.PRECIO_UNIDAD)
 Subconsultas
Definición de subconsultas.

Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia SELECT. Normalmente
se utilizan para filtrar una clausula WHERE o HAVING con el conjunto de resultados de la subconsulta,
aunque también pueden utilizarse en la lista de selección.

 Por ejemplo podriamos consultar el alquirer último de un cliente.

 SELECT CO_CLIENTE, NOMBRE, MARCA, MODDELO

 FROM ALQUILERES
 WHERE CO_CLIENTE = 1
 AND   FECHA_ALQUILER = (SELECT MAX(FECHA_ALQUILER)
           FROM ALQUILERES
           WHERE CO_CLIENTE = 1)

En este caso, la subconsulta se ejecuta en primer lugar, obteniendo el valor de la máxima fecha de
alquier, y posteriormente se obtienen los datos de la consulta principal.

Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que aparece
encerrada entre paréntesis.

La subconsulta se puede encontrar en la lista de selección, en la cláusula WHERE o en la cláusula HAVING

de la consulta principal.

Tiene las siguientes restricciones:

 No puede contener la cláusula ORDER BY

 No puede ser la UNION de varias sentencias SELECT
 Si la subconsulta aparece en la lista de selección, o está asociada a un operador igual "=" solo puede
devolver un único registro.

Referencias externas 
 
A menudo, es necesario, dentro del cuerpo de una subconsulta, hacer referencia al valor de una columna
de la fila actual en la consulta principal, ese nombre de columna se denomina referencia externa.

Una referencia externa es un campo que aparece en la subconsulta pero se refiere a la una de las tablas
designadas en la consulta principal.

Cuando se ejecuta una consulta que contiene una subconsulta con referencias externas, la subconsulta
se ejecuta por cada fila de la consulta principal.

En este ejemplo la subconsulta aparece en la lista de selección, ejecutandose una vez por cada fila que
devuelve la consulta principal.

 SELECT CO_EMPLEADO,
     NOMBRE,
     (SELECT MIN(FECHA_NOMINA)
 FROM NOMINAS
WHERE CO_EMPLEADO = EMPLEADOS.CO_EMPLEADO) PRIMERA_NOMINA
 FROM EMPLEADOS;

Anidar subconsultas 
 
Las subconsultas pueden anidarse de forma que una subconsulta aparezca en la cláusula WHERE (por
ejemplo) de otra subconsulta que a su vez forma parte de otra consulta principal. 

 SELECT  CO_EMPLEADO,
   EMPLEADOS
 FROM EMPLEADOS
 WHERE CO_EMPLEADO IN (SELECT CO_EMPLEADO
           FROM NOMINAS
           WHERE ESTADO  IN ( SELECT ESTADO
                 FROM ESTADOS_NOMINAS
                 WHERE EMITIDO = 'S' AND PAGADO = 'N') )

Los resultados que se obtienen con subconsultas normalmente pueden conseguirse a través de consultas
combinadas ( JOIN ).

 SELECT  CO_EMPLEADO,
    NOMBRE
 FROM EMPLEADOS
 WHERE ESTADO IN (SELECT ESTADO
        FROM ESTADOS
        WHERE ACTIVO = 'S')

Podrá escribirse como:

 SELECT  CO_EMPLEADO,
    NOMBRE
 FROM EMPLEADOS, ESTADOS
 WHERE EMPLEADOS.ESTADO = ESTADOS.ESTADO
   AND ESTADOS.ACTIVO = 'S' 

Normalmente es más rápido utilizar un JOIN en lugar de una subconsulta, aunque esto depende sobre
todo del diseño de la base de datos y del volumen de datos que tenga.

 Utilización de subconsultas con UPDATE

 
Podemos utilizar subconsultas también en consultas de actualización conjuntamente con UPDATE.
Normalmente se utilizan para "copiar" el valor de otra tabla.

 UPDATE  EMPLEADOS
  SET SALARIO_BRUTO = (SELECT SUM(SALIRO_BRUTO)
        FROM NOMINAS
        WHERE NOMINAS.CO_EMPLEADO = EMPLEADOS.CO_EMPLEADO)
 WHERE SALARIO_BRUTO IS NULL

La función EXISTS
 EXISTS es una función SQL que devuelve verdadero cuando una subconsulta retorna al menos una fila.

 SELECT  CO_CLIENTE,
    NOMBRE
 FROM CLIENTES
 WHERE EXISTS ( SELECT *
        FROM MOROSOS
        WHERE CO_CLIENTE = CLIENTES.CO_CLIENTE
           AND PAGADO = 'N') 

La función EXISTS puede ser utilizada en cualquier sentencia SQL válida, SELECT, UPDATE, INSERT o DELETE. 

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)
BEGIN
UPDATE PAISES
SET DESCRIPCION = @descripcion
WHERE CO_PAIS = @coPais
END

ELSE
BEGIN
INSERT INTO PAISES
(CO_PAIS, DESCRIPCION) VALUES
(@coPais, @descripcion)
END

Estructura condicional CASE

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

    CASE <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  web
                                                                           FROM 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 + 1
IF (@contador % 2 = 0)
       CONTINUE
    PRINT 'Iteracion del bucle ' + cast(@contador AS varchar)
    END

El bucle se dejará de repetir con la instrucción BREAK.

    DECLARE @contador int

    SET @contador = 0
    WHILE (1 = 1)
    BEGIN
      SET @contador = @contador + 1
IF (@contador % 50 = 0)
       BREAK
    PRINT 'Iteracion del bucle ' + cast(@contador AS varchar)
    END

También podemos utilizar el bucle WHILE conuntamente con subconsultas.

    DECLARE @coRecibo int   

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

Estructura GOTO

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

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

    DECLARE @divisor int,

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

Control de errores en Transact SQL

Uso de TRY CATCH

A partIr de la versión 2005, SQL Server proporciona el control de errores a través de las instrucciones
TRY y CATCH.

Estas nuevas instrucciones suponen un gran paso adelante en el control de errores en SQL Server, un
tanto precario en las versiones anteriores.
La sintaxis de TRY CATCH es la siguiente:

    BEGIN TRY

        ...
    END TRY
    BEGIN CATCH
        ...
    END CATCH

El siguiente ejemplo ilustra el uso de TRY - CATCH.

 BEGIN TRY

   DECLARE @divisor int ,

   @dividendo int,

   @resultado int

   SET @dividendo = 100

   SET @divisor = 0

   -- Esta linea provoca un error de division por 0

   SET @resultado = @dividendo/@divisor
   PRINT 'No hay error'
 END TRY
 BEGIN CATCH
   PRINT 'Se ha producido un error'
 END CATCH
Funciones especiales de Error

Las funciones especiales de error, están disponibles únicamente en el bloque CATCH para la obtención

de información detallada del error.

    Son:

 ERROR_NUMBER(), devuelve el número de error.

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

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

 BEGIN TRY

   DECLARE @divisor int ,

   @dividendo int,

   @resultado int

   SET @dividendo = 100

   SET @divisor = 0

   -- Esta linea provoca un error de division por 0

   SET @resultado = @dividendo/@divisor
   PRINT 'No hay error'
 END TRY
 BEGIN CATCH
   PRINT ERROR_NUMBER()
     PRINT ERROR_SEVERITY()   
     PRINT ERROR_STATE() 
     PRINT ERROR_PROCEDURE()  
     PRINT ERROR_LINE()  
     PRINT ERROR_MESSAGE() 
 END CATCH

Lógicamente, podemos utilizar estas funciones para almacenar esta información en una tabla de la base
de datos y registrar todos los errores que se produzcan.

La variable de sistema @@ERROR

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

 DECLARE @divisor int ,

   @dividendo int ,
    @resultado int

 SET @dividendo = 100

 SET @divisor = 0
 -- Esta linea provoca un error de division por 0
 SET @resultado = @dividendo/@divisor
 
 IF @@ERROR = 0
     BEGIN
         PRINT 'No hay error'
     END
 ELSE
     BEGIN        
      PRINT 'Hay error'  
  END

El uso de @@ERROR para controlar errores puede provocar multitud de problemas. Uno de los más

habituales es sin duda, incluir una nueva sentencia Transact SQL entre la línea que provoco el error y la
que lo controla. Esa nueva instrucción restaura el valor de @@ERROR y no controlaremos el error.

El siguiente ejemplo ilustra esta situación:

 DECLARE @divisor int ,

   @dividendo int ,
    @resultado int

 SET @dividendo = 100

 SET @divisor = 0
 -- Esta linea provoca un error de division por 0
 SET @resultado = @dividendo/@divisor
 PRINT 'Controlando el error ...' -- Esta linea estable @@ERROR a cero
 IF @@ERROR = 0
     BEGIN
-- Se ejecuta esta parte!
         PRINT 'No hay error'
     END
 ELSE
     BEGIN        
      PRINT 'Hay error'  
  END

Generar un error con RAISERROR

En ocasiones es necesario provocar voluntariamente un error, por ejemplo nos puede interesas que se
genere un error cuando los datos incumplen una regla de negocio.
Podemos provocar un error en tiempo de ejecución a través de la función RAISERROR.
  DECLARE @tipo int,
         @clasificacion int
 
 SET @tipo = 1
 SET @clasificacion = 3
 IF (@tipo = 1 AND @clasificacion = 3)
 BEGIN
    RAISERROR ('El tipo no puede valer uno y la clasificacion 3',
16, -- Severidad
1 -- Estado
)
 END

La función RAISERROR recibe tres parámetros, el mensaje del error (o código de error predefinido), la
severidad y el estado.

La severidad indica el grado de criticidad del error. Admite valores de 0 al 25, pero solo podemos
asignar valores del 0 al 18. Los errores el 20 al 25 son considerados fatales por el sistema, y cerraran la
conexión que ejecuta el comando RAISERROR. Para asignar valores del 19 al 25 necesitares ser
miembros de la función de SQL Server sysadmin.

El estado es un valor para permitir que el programador identifique el mismo error desde

diferentes partes del código. Admite valores entre 1 y 127, permite tratar.

Lenguaje de definición de datos (II)

Definición de Índices

Un índice es una estructura de datos que permite acceder a diferentes filas de una misma tabla a través
de un campo (o campos clave).

Un índice permite un acceso mucho más rápido a los datos.

Introducción a los índices.

Para entender lo que es un índice debemos saber primero como se almacena la

información internamente en las tablas de una base de datos. Cada tabla se divide en páginas de datos,
imaginemos un libro, podríamos escribirlo en "una sola hoja enorme" al estilo pergamino egipcio, o bien
en páginas a las que podemos acceder rápidamente a través de un índice. Está idea es la que se aplica en
el mundo de las bases de datos, la información está guardada en una tabla (el libro) que tiene muchas
hojas de datos (las páginas del libro), con un índice en el que podemos buscar la información que nos
interesa.

Si queremos buscar la palabra zapato en un diccionario, ¿qué hacemos?

 Leemos todo el diccionario hasta encontrar la palabra, con lo que nos habremos leído el
diccionario enterito (¡seguro que aprenderíamos un montón!) 
 Buscamos en el índice en que página está la letra z, y es en esa página donde buscamos.
Ni que decir tiene que la opción dos es la correcta, y es de este modo como se utiliza un índice en las
bases de datos, se define el índice a través de un campo (o campos) y es a partir de este punto desde
donde de busca.

Los índices se actualizan automáticamente cuando realizamos operaciones de escritura en la base de

datos.

Creación de índices

La creación de índices, como ya hemos visto, permite acelerar las consultas que se realizan en la base de
datos. Las sentencias de SQL para manipular índices son:

CREATE INDEX;
DROP INDEX;

La sintaxis para la creación de índices es la siguiente:

CREATE [UNIQUE] INDEX <nombre_indice>

ON <nombre_tabla>(
<nombre_campo> [ASC | DESC]
                  {,<nombre_campo> [ASC | DESC]})
                  );

La palabra clave UNIQUE específica que no pueden existir claves duplicadas en el índice.

ASC | DESC especifican el criterio de ordenación elegido, ascendente o descendente, por defecto es
ascendente.

Ejemplo: En el apartado dedicado a la definición de tablas creamos la tabla tClientes, este ejemplo crea
un índice único en el campo NIF. Esto nos permitirá buscar mucho más rápido por el campo NIF y nos
asegurará que no tengamos dos NIF iguales.

CREATE UNIQUE INDEX UIX_CLIENTES_NIF

ON tCLIENTES (NIF);

Las claves primarias son índices.

Los nombres de los índices deben ser únicos.
Para eliminar un índice debemos emplear la sentencia DROP INDEX.

DROP INDEX <nombre_tabla>.<nombre_indice>;

Ejemplo: Para eliminar el índice creado anteriormente.

DROP INDEX tCLIENTES.UIX_CLIENTES_NIF;

Lenguaje de manipulación de datos (II)

Insertar datos.

Hasta ahora hemos visto como se almacenan los datos en una base de datos y como consultar esos datos
almacenados, pero no hemos visto como almacenar dichos datos.

Para almacenar datos en una base de datos debemos insertar filas en las tablas. Para ellos SQL pone a
nuestra disposición la sentencia INSERT.

Inserción de filas

El proceso de inserción de filas consiste en añadir a una tabla una o más filas y en cada fila todos o parte
de sus campos.

Podemos distinguir dos formas de insertar filas:

 Inserción individual de filas.

 Inserción múltiple de filas.

 La sintaxis de la sentencia INSERT es diferente según cuál sea nuestro propósito. Sólo podremos omitir
un campo al efectuar una inserción cuando este acepte valores nulos.

Inserción individual de filas

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

INSERT INTO <nombre_tabla>

[(<campo1>[,<campo2>,...])]
values
(<valor1>,<valor2>,...);

Como se puede observar la sentencia tiene dos partes claramente diferenciadas, por un lado la propia
INSERT INTO seguida de la lista de campos en los que queremos insertar los datos, y por otro la lista de
valores que queremos insertar en los campos. La mejor forma de ver esto es a través de un ejemplo.

INSERT INTO tCoches

 (matricula, marca  , modelo, color      , numero_kilometros)
values
('M1111CA',
'RENAULT',
'MEGANE TR100',
'NEGRO DIAMANTE',
78000);
Nota: Hemos utilizado el color rojo para los datos de tipo texto, entrecomillados con la comilla simple, y el
azul para los numéricos.

Con esta sentencia INSERT creamos un registro en la tabla tCoches con los valores especificados, es
decir, la matricula tendrá el valor M-1111-CA, la marca será RENAULT y así sucesivamente.

¿Qué ocurriría si ya existiera un coche con la matrícula M-1111-CA? Se producirá un error, porque hemos
definido la clave primaria en el campo matricula, y como hemos visto la clave primaria debe ser única.

 Si omitimos algún par " campo-valor " en la sentencia INSERT, pueden ocurrir varias cosas:

 Que se produzca un error, si el campo  no acepta valores nulos.

 Que se grave el registro y se deje nulo el campo, cuando el campo acepte valores nulos.
 Que se grave el registro y se tome el valor por defecto, cuando el campo tenga definido un valor
por defecto.

Que hacer en cada momento dependerá del programa.

Por ejemplo, la siguiente sentencia creará un registro en la tabla tCoches con el campo
numero_kilometros cero, ya que este es su valor por defecto.

INSERT INTO tCoches

(matricula,
marca      ,
modelo     ,
color)
values
('M1111CA',
'RENAULT',
'MEGANE TR100',
'NEGRO DIAMANTE');

Inserción multiple de filas

La sentencia INSERT permite tambien insertar varios registros en una tabla. Pare ello se utiliza una
combinación de la sentencia INSERT junto a una sentencia SELECT. El resultado es que se insertan todos
los registros devueltos por la consulta.

INSERT INTO <nombre_tabla>

[(<campo1>[,<campo2>,...])]
SELECT
[(<campo1>[,<campo2>,...])]
FROM
 <nombre_tabla_origen>;

Para poder utilizar la inserción múltiple de filas se deben cumplir las siguientes normas:

 La lista de campos de las sentencias insert y select deben coincidir en número y tipo de datos.
 Ninguna de las filas devueltas por la consulta debe infringir las reglas de integridad de la tabla en
la que vayamos a realizar la inserción.

Pongamos un ejemplo, vamos a crear una tabla con las diferentes marcas que tenemos en la base de
datos. La sentencia SQL para crear la tabla es la siguiente:

CREATE TABLE tMarcas
(
codigo    integer not null identity(1,1),
marca     varchar(255),
constraint PK_Marcas primary key (codigo) 
);
Nota: Hemos incluido la función identity para el campo codigo, esta función es propia de SQL Server e indica que
el código se genera automáticamente cada vez que se inserta un registro con un valor autonumérico. Praticamente
todos los gestores de bases de datos dan la opción del campo autonumerico o incremental, si bien el modo varias.
Para SQL Server utilizaremos la funcion identity, para ORACLE las secuencias ...

Una vez que tenemos creada la tabla de marcas vamos a insertar otro par de registros en la tabla de
coches, para ello utilizamos una sentencia insert into para una única fila.

INSERT INTO tCoches

(matricula,
marca      ,
modelo     ,
color)
values
('M2233FH',
'SEAT',
'LEON FR',
'ROJO');

INSERT INTO tCoches

(matricula,
marca      ,
modelo     ,
color)
values
('M1332FY',
'FORD',
'FIESTA',
'GRIS PLATA');

Ahora tenemos tres marcas diferentes en la tabla tCoches, y queremos insertarlas en la tabla de marcas,
para ello podemos realizar tres inserciones individuales, pero ¿que pasaría si no supiéramos de
antemano el número de marcas?¿y si fueran unas cincuenta marcas? Nos podríamos pasar el día entero
escribiendo sentencias insert into.
Afortunadamente podemos realizar una inserción múltiple del siguiente modo:

INSERT INTO tMarcas

(marca)
SELECT DISTINCT marca FROM tCoches; 

Como resultado obtenemos un registro en la tabla tMarcas por cada marca de la tabla tCoches. El campo
código se ha generado automáticamente ya que está definido como identidad.

CODIGO MARCA
1 FORD
2 RENAULT
3 SEAT

Démonos cuenta de que el orden de generación no ha sido el mismo que el de inserción, sino que se ha
aplicado el orden en el que han sido devueltos los datos por la sentencia SELECT.

Ahora deberíamos cambiar los datos de la tabla tCoches, para guardar el código de la marca en lugar de
su descripción, pero para ello necesitamos saber cómo modificar un dato grabado... Es momento de
pasar al siguiente punto, la actualización de datos.

Insertar datos en Transact SQL

Inserción individual de filas.

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

INSERT INTO <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 se
insertará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 para la
tabla (o null si no tienen valores por defecto).

INSERT INTO PRECIOS DEFAULT VALUES

En SQL Sever podemos marcar un campo de una tabla como autonumérico (identity), cuando insertamos
un registro en dicha tabla el valor del campo se genera automaticamente. Para recuperar el valor
generado disponemos de varios métodos:

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

DECLARE @Codigo int

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

VALUES (10, getdate(),getdate()+30, 1)

set @Codigo = @@Identity PRINT @Codigo

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

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

DECLARE @Codigo int

 INSERT INTO PRECIOS

(PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO)

VALUES

(10, getdate(),getdate()+30, 1)

SET @Codigo = SCOPE_IDENTITY()

PRINT @Codigo

Clausula OUTPUT

A partir de la versión de SQL Server 2005 disponemos de la clausula OUTPUT para recuperar los valores
que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y DELETED.

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o
DELETE. Es decir, son una copia de los datos "antes" del cambio.

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

Las columnas con prefijo INSERTED reflejan el valor después de que se complete la instrucción UPDATE o
INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copia de los datos
"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

Actualización de datos.
La sentencia UPDATE.

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

UPDATE <nombre_tabla>
SET  <campo1> = <valor1>
        {[,<campo2> = <valor2>,...,<campoN> = <valorN>]}
[ WHERE <condicion>];

Las siguientes sentencias actualizan los datos de la tabla tCoches con los valores de la tabla tMarca
obtenidos anteriormente en la página dedicada a la inserción de datos. 

UPDATE tCoches
SET  marca = '1'
WHERE marca = 'FORD';

UPDATE tCoches
SET  marca = '2'
WHERE marca = 'RENAULT';

UPDATE tCoches
SET  marca = '3'
WHERE marca = 'SEAT';

Nótese que los valores para el campo marca aparecen entrecomillados, ya que es un campo de tipo
varchar. Los valores con los que actualicemos los datos deben ser del tipo del campo.

Un aspecto a tener en cuenta es que los campos que forman la primary key de una tabla sólo se
podrán modificar si los registros no están referenciados en ninguna otra tabla. En nuestro caso
sólo podremos modificar la matrícula de un coche si no tiene registros asociados en la tabla
tAlquileres.

Esto puede causar poblemas, ya que podríamos habernos equivocado al dar de alta el coche en la tabla tCoches y detectar el
error despues de alquilar el coche. En tal caso tendríamos dar de alta un nuevo coche con la matrícula correcta, actualizar los
registros de la tabla alquileres y por último borrar el registro erroneo de la tabla tCoches. Este proceso puede ser bastante
complicado en el caso de que existiran más relaciones con la tabla. Se podría considerar que la clave primaria de la tabla esta
mal definida y que la matrícula no debe ser el elemento que identifique el coche. Una alternativa seria crear un código
autonumérico para la tabla tCoches que realizará las veces de clave primaria y crear un índice único para la matrícula, este
diseño tambien tiene sus "pegas", por lo que debemos decidir que modelo utilizar, y seleccionar las claves primarias con
sumo cuidado.  

Uso de subconsultas con UPDATE

El uso de subconsultas es una técnica avanzada de consulta que veremos con detalle más adelante, pero
que tratamos aquí de forma introductoria.

Hasta ahora hemos actualizado los datos con valores que conocemos de antemano, ¿pero qué ocurre
cuando esos datos deben tomarse de otra tabla de la base de datos?.Podríamos diseñar un programa
que recorriera toda la tabla y buscará el valor adecuado para cada registro y lo actualizase. Sin duda es
una solución, y en ocasiones casí la única, pero es una solución cara y compleja que además exige que
conozcamos algún otro lenguaje de programación. Para estos casos podemos utilizar subconsultas con la
sentencia UPDATE. 

La sintaxis es la siguiente:

UPDATE <nombre_tabla>
SET  <campo1> = <valor1> | <subconsulta1> 
        {[,<campo2> = <valor2> | <subconsulta2>
          ,...
          , <campoN> = <valorN> | <subconsultaN>]}
[ WHERE <condicion>];

Como puede verse la sintaxis es prácticamente igual a la sintaxis del la sentencia UPDATE, con la
salvedad de que podemos utilizar subconsultas en lugar de valores al asignar los campos. De forma
genérica podemos decir que las subconsultas son consultas SELECT incluidas dentro de otra sentencia
SQL.

Las siguientes sentencias UPDATE son equivalentes:

Utilizando sentencias UPDATE normales:

UPDATE tCoches
SET  marca = '1'
WHERE marca = 'FORD';
UPDATE tCoches
SET  marca = '2'
WHERE marca = 'RENAULT';
UPDATE tCoches
SET  marca = '3'
WHERE marca = 'SEAT';
Utilizando sentencias UPDATE combinadas con subconsultas:
UPDATE tCoches
SET  marca = (SELECT CODIGO FROM tMarcas 
              WHERE tMarcas.Marca = tCoches.Marca )
WHERE marca IN ('FORD','RENAULT','SEAT');
Por cada registro de la tabla tCoches se ejecutará la subconsulta, actualizando el campo marca a el valor
del código de la marca en la tabla tMarcas.

    El uso de subconsultas para actualizar datos tiene algunas limitaciones:

 La subconsulta sólo puede devover un único campo.

 La subconsulta sólo puede devolver un sólo registro.
 El tipo de datos devuelto por la subconsulta debe ser del mismo tipo que el campo al que
estamos asignando el valor.
 No todos los sistemas de bases de datos permiten usar subconsultas para actualizar datos
(Access) aunque si una buena parte de ellos (ORACLE, SQL Server, Sybase ...)

Pero en nuestro ejemplo el campo codigo de la tabla tMarcas es numérico y el campo marca de la tabla
tCoches es texto. ¿Por qué funciona? Muy facil, el motor de la base de datos es capaz de convertir el
valor numérico a un valor texto de forma automática, si bien esta es una excepción.

Ahora que ya tenemos modificado el valor de la marca de los registros, es conveniente modificar su tipo

de datos y crear una foreign key contra la tabla tMarcas. Para ello  ejecutaremos las siguientes
sentencias.

ALTER TABLE tCoches

alter column marca int not null;

La opcion alter column es propia de SQL Server. Para modificar el tipo de datos de una tabla debemos
consultar la ayuda del gestor de bases de datos. 

ALTER TABLE tCoches

add constraint FK_Coches_Marcas foreign key (marca)
references tMarcas (codigo);

Si no recuerda como modificar tablas o crear foreing key pulse AQUÍ .

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 la sentencia 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 los cambios
inmediatamente sin necesidad de hacer COMMIT. Por supuesto podemos gestionar nosotros las
transacciones pero es algo que hay que hacer de forma explícita con la instrucción BEGIN TRAN y que se
verá en capítulos posteriores de este tutorial.

Update INNER JOIN

En ocasiones queremos actualizar los datos de una tabla con los datos de otra (muy común para des
normalizar un modelo de datos).

Habitualmente, usamos subconsultas para este propósito, pero Transact SQL permite la utilización de la
sentencia UPDATE INNER JOIN.

UPDATE CLIENTES

SET

NOMBRE = FICHERO_CLIENTES.NOMBRE,

APELLIDO1 = FICHERO_CLIENTES.APELLIDO1,

APELLIDO2 = FICHERO_CLIENTES.APELLIDO2

FROM CLIENTES

INNER JOIN FICHERO_CLIENTES

ON FICHERO_CLIENTES.CO_CLIENTE = CLIENTES.CO_CLIENTE

Clausula OUTPUT

 A partir de la versión 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ógicas INSERTED y DELETED.

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o
DELETE. Es decir, son una copia de los datos "antes" del cambio.

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

 DECLARE @FILAS_ACTUALIZADAS TABLE

( CO_CLIENTE int ,

NOMBRE varchar(100),

APELLIDO1 varchar(100),

APELLIDO2 varchar(100)

UPDATE CLIENTES

SET

NOMBRE = '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ón UPDATE o
INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copia de 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

Borrado de datos
La sentencia DELETE

Para borrar datos de una tabla, debemos utilizar la sentencia DELETE. La sintaxis de la sentencia DELETE
es la siguiente:

DELETE FROM <nombre_tabla>

[ WHERE <condicion>];

El siguiente ejemplo ilustra el uso de la sentencia DELETE. Es buena idea especificar en la sentencia
WHERE los campos que forman la clave primaria de la tabla para evitar borrar datos que no  queramos
eliminar.

DELETE FROM tCoches

WHERE marca = 'SEAT';

La sintaxis de DELETE varia en Access, siendo necesario el uso del comodín *. DELETE *
FROM <tCoches>

Cuando trabajemos con la sentencia DELETE debemos tener en cuenta las siguientes consideraciones:

 Solo podemos borrar datos de una única tabla.

 Cuando borramos datos de una vista, los estamos borrando también de la tabla. Las vistas son
solo una forma de ver los datos, no una copia.
 Si intentamos borrar un registro de una tabla referenciada por una FOREING KEY como tabla
maestra, si la tabla dependiente tiene registros relacionados la sentencia DELETE fallará.

La sentencia TRUNCATE
Para realizar un borrado completo de tabla debemos considerar la posibilidad de utilizar la sentencia
TRUNCATE, mucho más rápida que DELETE.

La sintaxis de la sentencia TRUNCATE es la siguiente:

TRUNCATE TABLE <nombre_tabla>;

El siguiente ejemplo muestra el uso de la sentencia TRUNCATE.

TRUNCATE TABLE tCoches;

Cuando trabajemos con la sentencia TRUNCATE debemos tener en cuenta las siguientes
consideraciones.

 La sentencia TRUNCATE no es transaccional. No se puede deshacer.

 La sentencia TRUNCATE no admite clausula WHERE. Borra toda la tabla.
 No todos los gestores de bases de datos admiten la sentencia TRUNCATE.

Borrar datos en Transact SQL

Delete

Para borrar datos de una tabla debemos utilizar la sentencia DELETE.

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

CREATE TABLE DATOS

( Id int identity not null, dato varchar(100), fx_alta datetime, constraint PK_DATOS PRIMARY KEY (Id) )

 GO

 DECLARE @i int,

@dato varchar(100)

set @i = 0

WHILE (@i <100)

BEGIN

SET @i = @i +1

set @dato = 'Dato:' + cast(@i as varchar)

INSERT INTO DATOS (dato, fx_alta)

 VALUES (@dato, getdate())

END

 GO

 SELECT * from DATOS

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

DELETE

FROM DATOS

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

DELETE

FROM DATOS

WHERE Id=12

Cuando borramos datos de una tabla, podemos obtener el número de filas que han sido afectadas por la
instrucción a través de la variable @@RowCount.

El siguiente ejemplo ilustra el uso de @@RowCount.

DELETE

FROM DATOS

WHERE Id=17

SELECT @@ROWCOUNT

Clausula OUTPUT

A partir de la versión de SQL Server 2005 disponemos de la clausula OUTPUT para recuperar los valores
que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y DELETED.

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o
DELETE. Es decir, son una copia de los datos "antes" del cambio.

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

DECLARE @FILAS_BORRADAS TABLE

 ( Id int,

dato varchar(100),

fx_alta datetime )

 DELETE

FROM DATOS

OUTPUT DELETED.* INTO @FILAS_BORRADAS

WHERE Id=17

 SELECT * from @FILAS_BORRADAS

Truncate Table

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

TRUNCATE TABLE DATOS

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

 TRUNCATE TABLE no admite la clausula WHERE.

 No podemos ejecutar TRUNCATE TABLE sobre tablas que sean "padres" en foreign keys.

Programación con Transact SQL

Introducción

SQL es un lenguaje de consulta para los sistemas de bases de datos relaciónales, pero que no posee la
potencia de los lenguajes de programación. No permite el uso de variables, estructuras de control de
flujo, bucles ... y demás elementos característicos de la programación. No es de extrañar, SQL es un
lenguaje de consulta, no un lenguaje de programación.

Sin embargo, SQL es la herramienta ideal para trabajar con bases de datos. Cuando se desea realizar una
aplicación completa para el manejo de una base de datos relacional, resulta necesario utilizar alguna
herramienta que soporte la capacidad de consulta del SQL y la versatilidad de los lenguajes de
programación tradicionales. Transact SQL es el lenguaje de programación que proporciona Microsoft SQL
Server para extender el SQL estándar con otro tipo de instrucciones y elementos propios de los lenguajes
de programación.

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

 Procedimientos almacenados
 Funciones
 Triggers
 Scripts

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

 Service Broker

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, el nombre de una variable debe comenzar por @. Para declarar variables en Transact SQL
debemos utilizar la palabra clave declare, seguido del identificador y tipo de datos de la variable.

-- Esto es un comentario de línea simple

/*
Este es un comentario con varias líneas.
Conjunto de Lineas.
*/
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 instruncción set.

 Utilizando una sentencia SELECT.
 Realizando un FETCH de un cursor.

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

DECLARE @nombre VARCHAR(100)

-- La consulta debe devolver un único registro

SET @nombre = (SELECT nombre

 FROM CLIENTES

WHERE ID = 1)

PRINT @nombre

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

DECLARE @nombre VARCHAR(100),

@apellido1 VARCHAR(100),

@apellido2 VARCHAR(100)

 SELECT @nombre=nombre ,

@apellido1=Apellido1,

@apellido2=Apellido2

FROM CLIENTES

WHERE ID = 1

 PRINT @nombre

PRINT @apellido1

PRINT @apellido2

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

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

DECLARE @nombre VARCHAR(100),

@apellido1 VARCHAR(100),

@apellido2 VARCHAR(100)

 DECLARE CDATOS CURSOR

FOR

SELECT nombre , Apellido1, Apellido2

 FROM CLIENTES

 OPEN CDATOS

FETCH CDATOS INTO @nombre, @apellido1, @apellido2

 WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT @nombre

PRINT @apellido1

PRINT @apellido2

FETCH CDATOS INTO @nombre, @apellido1, @apellido2

END

 CLOSE CDATOS

DEALLOCATE CDATOS

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

Procedimientos almacenados en Transact SQL

Un procedimiento es un programa dentro de la base de datos que ejecuta una acción o conjunto de
acciones específicas.

Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código. En

Transact SQL los procedimientos almacenados pueden devolver valores (numerico entero) o conjuntos
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 que inserta un

registro en la tabla "CLIENTES". 

CREATE PROCEDURE spu_addCliente @nombre varchar(100),

@apellido1 varchar(100),

@apellido2 varchar(100),

@nifCif varchar(20),

@fxNaciento datetime

AS

INSERT INTO CLIENTES

(nombre, apellido1, apellido2, nifcif, fxnacimiento) VALUES

(@nombre, @apellido1, @apellido2, @nifCif, @fxNaciento)

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

El siguiente ejemplo muestra la ejecución del procedimiento almacenado anterior.

DECLARE @fecha_nacimiento datetime

set @fecha_nacimiento = convert(datetime, '13/05/1975', 103)

EXEC spu_addCliente 'Pedro', 'Herrarte', 'Sanchez',

'00000002323', @fecha_nacimiento

Siempre es deseable que las instrucciones del procedure estén dentro de un bloque TRY CATCH y
controlados por una transacción.

ALTER PROCEDURE spu_addCliente @nombre varchar(100),

@apellido1 varchar(100),

@apellido2 varchar(100),

@nifCif varchar(20),
 @fxNaciento datetime

AS

BEGIN TRY

BEGIN TRAN

INSERT INTO CLIENTES

(nombre, apellido1, apellido2, nifcif, fxnacimiento) VALUES

(@nombre, @apellido1, @apellido2, @nifCif, @fxNaciento)

COMMIT

END TRY

BEGIN CATCH

ROLLBACK

PRINT ERROR_MESSAGE()

END CATCH

Si queremos que los parámetros de un procedimiento almacenado sean de entrada-salida debemos

especificarlo a través de la palabra clave OUTPUT, tanto en la definición del procedure como en la
ejecución.

El siguiente ejemplo muestra la definición de un procedure con parámetros de salida.

CREATE PROCEDURE spu_ObtenerSaldoCuenta @numCuenta varchar(20),

@saldo decimal(10,2) output

AS

BEGIN

SELECT @saldo = SALDO

 FROM CUENTAS

WHERE NUMCUENTA = @numCuenta

END

 Y para ejecutar este procedure:

DECLARE @saldo decimal(10,2)

EXEC spu_ObtenerSaldoCuenta '200700000001', @saldo output

PRINT @saldo

Un procedimiento almacenado puede devolver valores numéricos enteros a través de la instrucción

RETURN. Normalmente debemos utilizar los valores de retorno para determinar si la ejecución del
procedimiento ha sido correcta o no. Si queremos obtener valores se recomienda utilizar parámetros de
salida o funciones escalares (se verán más adelante en este tutorial).

El siguiente ejemplo muestra un procedimiento almacenado que devuelve valores.

CREATE PROCEDURE spu_EstaEnNumerosRojos @numCuenta varchar(20)

AS

BEGIN

IF (SELECT SALDO FROM CUENTAS

WHERE NUMCUENTA = @numCuenta) < 0

BEGIN

RETURN 1

END

ELSE

RETURN 0

END

    El siguiente ejemplo muestra como ejecutar el procedure y obtener el valor devuelto.

DECLARE @rv int

EXEC @rv = spu_EstaEnNumerosRojos '200700000001'

 PRINT @rv

Otra característica muy interesante de los procedimientos almacenados en Transact SQL es que pueden
devolver uno o varios conjuntos de resultados.

El siguiente ejemplo muestra un procedimiento almacenado que devuelve un conjunto de resultados.

CREATE PROCEDURE spu_MovimientosCuenta @numCuenta varchar(20)

AS

BEGIN

SELECT @numCuenta,

SALDO_ANTERIOR,

SALDO_POSTERIOR,

IMPORTE,

FXMOVIMIENTO

FROM MOVIMIENTOS

INNER JOIN CUENTAS ON MOVIMIENTOS.IDCUENTA = CUENTAS.IDCUENTA

WHERE NUMCUENTA = @numCuenta

ORDER BY FXMOVIMIENTO DESC

END

    La ejecución del procedimiento se realiza normalmente.

EXEC spu_MovimientosCuenta '200700000001'

 El resultado de la ejecución...

NUMCUENTA     SALDO_ANTERIOR SALDO_POSTERIOR  IMPORTE FXMOVIMIENTO

------------  -------------- ---------------- ------- -----------------------
200700000001  50.99          100.99           50.00   2007-08-25 16:18:36.490
200700000001  0.99           50.99            50.00   2007-08-23 16:20:41.183
200700000001  50.99          0.99             50.00   2007-08-23 16:16:29.840
200700000001  0.99           50.99            50.00   2007-08-23 16:14:05.900
 Triggers en Transact SQL
Un trigger(o desencadenador) es una clase especial de procedimiento almacenado que se ejecuta
automáticamente cuando se produce un evento en el servidor de bases de datos.

SQL Server proporciona los siguientes tipos de triggers:

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

Trigger DML

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

La sintaxis general de un trigger es la siguiente.

CREATE TRIGGER <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 tablas inserted y
deleted es la misma que tiene la tabla que ha desencadenado la ejecución del trigger.
La primera tabla (inserted) solo está disponible en las operaciones INSERT y UPDATE y en ella están los
valores resultantes despues de la inserción o actualización. Es decir, los datos insertados. Inserted estará
vacia en una operación DELETE.

En la segunda (deleted), disponible en las operaciones UPDATE y DELETE, están los valores anteriores
a la ejecución de la actualización o borrado. Es decir, los datos que serán borrados. Deleted estará vacia
en una operacion INSERT.

¿No existe una tabla UPDATED? No, hacer una actualización es lo mismo que borrar (deleted) e insertar
los nuevos (inserted). La sentencia UPDATE es la única en la que inserted y deleted tienen datos
simultaneamente.

No se puede modificar directamente los datos de estas tablas.

El siguiente ejemplo, graba un histórico de saldos cada vez que se modifica un saldo de la tabla cuentas.

CREATE TRIGGER TR_CUENTAS

ON CUENTAS

AFTER UPDATE

AS

BEGIN

-- SET NOCOUNT ON impide que se generen mensajes de texto

-- con cada instrucción

SET NOCOUNT ON;

INSERT INTO HCO_SALDOS

(IDCUENTA, SALDO, FXSALDO)

SELECT IDCUENTA, SALDO, getdate()

FROM INSERTED

END

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

UPDATE CUENTAS

SET SALDO = SALDO + 10

WHERE IDCUENTA = 1
Una consideración a tener en cuenta es que el trigger se ejecutará aunque la instruccion DML (UPDATE,
INSERT o DELETE) no haya afectado a ninguna fila. En este caso inserted y deleted devolveran un
conjunto de datos vacio.

 Podemos especificar a qué columnas de la tabla debe afectar el trigger.

ALTER TRIGGER TR_CUENTAS

ON CUENTAS

AFTER UPDATE

AS

BEGIN

-- SET NOCOUNT ON impide que se generen mensajes de texto

-- con cada instrucción

SET NOCOUNT ON;

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

BEGIN

INSERT INTO HCO_SALDOS

(IDCUENTA, SALDO, FXSALDO)

SELECT IDCUENTA, SALDO, getdate()

FROM INSERTED

END END

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

ALTER TRIGGER TR_CUENTAS

ON CUENTAS

AFTER UPDATE

AS
 BEGIN

-- SET NOCOUNT ON impide que se generen mensajes de texto

-- con cada instrucción

SET NOCOUNT ON;

INSERT INTO HCO_SALDOS

(IDCUENTA, SALDO, FXSALDO)

SELECT IDCUENTA, SALDO, getdate()

FROM INSERTED

 ROLLBACK

END

 En este caso obtendremos el siguiente mensaje de error:

La transacción terminó en el desencadenador. Se anuló el lote.

Podemos activar y desactivar Triggers a 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 de datos
(DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-
SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL.

 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 base de
datos. 

CREATE TRIGGER TR_SEGURIDAD

ON DATABASE FOR DROP_TABLE, ALTER_TABLE

AS

BEGIN

RAISERROR ('No está permitido borrar ni modificar tablas !' , 16, 1)

ROLLBACK TRANSACTION

END

Funciones en Transact SQL

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

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

Funciones escalares
Las funciones escalares devuelven un único valor de cualquier tipo de los datos tal como int, money,
varchar, real, etc.  

La sintaxis para una función escalar es la siguiente:

CREATE FUNCTION <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 cómo crear una función escalar.

CREATE FUNCTION fn_MultiplicaSaldo

@NumCuenta VARCHAR(20),

@Multiplicador DECIMAL(10,2)

RETURNS DECIMAL(10,2)

AS

BEGIN

DECLARE @Saldo DECIMAL(10,2),

@Return DECIMAL(10,2)

 SELECT @Saldo = SALDO

 FROM CUENTAS

WHERE NUMCUENTA = @NumCuenta

 SET @Return = @Saldo * @Multiplicador

 RETURN @Return

END

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

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

SELECT IDCUENTA,

NUMCUENTA,

SALDO,

FXALTA,

-- Ejecucion de la funcion:

dbo.fn_MultiplicaSaldo( NUMCUENTA, IDCUENTA) AS RESULTADO

FROM CUENTAS

    El siguiente ejemplo muestra cómo utilizar una función escalar en un script Transact SQL.

DECLARE @NumCuenta VARCHAR(20),

@Resultado DECIMAL(10,2)

 SET @NumCuenta = '200700000001'

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

 PRINT @Resultado

Las funciones escalares son muy similares a procedimientos almacenados con parámetros de salida, pero
estas pueden ser utilizadas en consultas de seleccion y en la clausula where de las mismas.

Las funciones no pueden ejecutar sentencias INSERT o UPDATE.

Funciones en línea

Las funciones en línea son las funciones que devuelven un conjunto de resultados correspondientes a la
ejecución de una sentencia SELECT.

La sintaxis para una función de tabla en línea es la siguiente:

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>

-- Lista de parámetros

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

RETURNS TABLE

AS

RETURN

-- Sentencia Transact SQL

    El siguiente ejemplo muestra cómo crear una función en línea.

CREATE FUNCTION fn_MovimientosCuenta

@NumCuenta VARCHAR(20)

RETURNS TABLE

AS

RETURN
 (

SELECT MOVIMIENTOS.*

FROM MOVIMIENTOS

INNER JOIN CUENTAS ON MOVIMIENTOS.IDCUENTA = CUENTAS.IDCUENTA

WHERE CUENTAS.NUMCUENTA = @NumCuenta

    No podemos utilizar la clausula ORDER BY en la sentencia de una función el línea.

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

SELECT * FROM fn_MovimientosCuenta('200700000001')

SELECT *

FROM CUENTAS

INNER JOIN CUENTAS_CLIENTE

ON CUENTAS_CLIENTE.IDCUENTA = CUENTAS.IDCUENTA

INNER JOIN CLIENTES

ON CLIENTES.id = CUENTAS_CLIENTE.IDCLIENTE

INNER JOIN fn_MovimientosCuenta('200700000001') A

ON A.IDCUENTA= CUENTAS.IDCUENTA

Funciones en línea de múltiples sentencias

Las funciones en línea de múltiples sentencias son similares a las funciones en línea excepto que el
conjunto de resultados que devuelven puede estar compuesto por la ejecución de varias consultas
SELECT.

Este tipo de función se usa en situaciones donde se requiere una mayor lógica de proceso.

La sintaxis para una función de tabla de multi sentencias es la siguiente:

CREATE FUNCTION <Table_Function_Name, sysname, FunctionName>

 (

-- Lista de parámetros

<@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 función 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                NULL                  
NULL         NULL   550.00          500.00           50.00               2007-08-25 16:18:36.490
NULL         NULL   600.00          550.00           50.00               2007-08-23 16:20:41.183
NULL         NULL   600.00          550.00           50.00               2007-08-23 16:14:05.900
200700000001 100.99 NULL            NULL             NULL                NULL                  
NULL         NULL   50.99           100.99           50.00               2007-08-25 16:18:36.490
NULL         NULL   0.99            50.99            50.00               2007-08-23 16:20:41.183
NULL         NULL   50.99           0.99             50.00               2007-08-23 16:16:29.840

Cursores en Transact SQL

Un cursor es una variable que nos permite recorrer con un conjunto de resultados obtenido a través de
una sentencia SELECT fila a fila.

    Cuando trabajemos con cursores debemos seguir los siguientes pasos.

 Declarar el cursor, utilizando DECLARE

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

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

-- Declaración del cursor

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

-- Declaración del cursor

DECLARE cClientes CURSOR FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

-- Apertura del cursor

OPEN cClientes

-- Lectura de la primera fila del cursor

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

 WHILE (@@FETCH_STATUS = 0 )

BEGIN

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

-- Lectura de la siguiente fila del cursor

FETCH cClientes INTO @id, @Nombre, @Apellido1,

 @Apellido2, @NifCif, @FxNacimiento

END

 -- Cierre del cursor

CLOSE cClientes

-- Liberar los recursos

DEALLOCATE cClientes

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

Valor devuelto Descripción

0 La instrucción FETCH se ejecutó correctamente.
La instrucción FETCH no se ejecutó correctamente o la fila estaba más allá del conjunto de
-1
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ón
mostramos el significado de cada una de estas opciones.

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

DECLARE cClientes CURSOR LOCAL FOR

SELECT Id, Nombre, Apellido1,

 Apellido2, NifCif, FxNacimiento

FROM CLIENTES

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

DECLARE cClientes CURSOR GLOBAL FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

Si no se especifica GLOBAL ni LOCAL, el valor predeterminado se controla mediante la configuración de la

opción de base de datos default to local cursor.  

El siguiente conjunto de parámetros que podemos especificar es [  FORWARD_ONLY | SCROLL  ]. A

continuación mostramos el significado de cada una de estas opciones.

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

 DECLARE cClientes CURSOR FORWARD_ONLY FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

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

 -- Declaracion de variables para el cursor

DECLARE @Id int,

@Nombre varchar(255),
 @Apellido1 varchar(255),

@Apellido2 varchar(255),

@NifCif varchar(20),

@FxNacimiento datetime

 -- Declaración del cursor

DECLARE cClientes CURSOR SCROLL FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

-- Apertura del cursor

OPEN cClientes

-- Lectura de la primera fila del cursor

FETCH NEXT FROM cClientes

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

 WHILE (@@FETCH_STATUS = 0 )

BEGIN

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

-- Lectura de la siguiente fila del cursor

FETCH NEXT FROM cClientes

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

END

-- Lectura de la fila anterior

FETCH PRIOR FROM cClientes

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

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

 -- Cierre del cursor

CLOSE cClientes

-- Liberar los recursos

DEALLOCATE cClientes

El siguiente conjunto de parámetros que podemos especificar es [  STATIC | KEYSET | DYNAMIC |

FAST_FORWARD ]. A continuación mostramos el significado de cada una de estas opciones.

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

 DECLARE cClientes CURSOR STATIC FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

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

 DECLARE cClientes CURSOR KEYSET FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

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

 DECLARE cClientes CURSOR DYNAMIC FOR

SELECT Id, Nombre, Apellido1,

 Apellido2, NifCif, FxNacimiento

FROM CLIENTES

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

 DECLARE cClientes CURSOR FAST_FORWARD FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

En SQL Server 2000, las opciones de cursor FAST_FORWARD y FORWARD_ONLY se excluyen

mutuamente. Si se especifican ambas, se genera un error. En SQL Server 2005, las dos palabras clave se
pueden utilizar en la misma instrucción DECLARE CURSOR.

El siguiente conjunto de parámetros que podemos especificar es [  READ_ONLY | SCROLL_LOCKS |

OPTIMISTIC  ]. A continuación mostramos el significado de cada una de estas opciones.

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

 DECLARE cClientes CURSOR READ_ONLY FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

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

 DECLARE cClientes CURSOR SCROLL_LOCKS FOR

SELECT Id, Nombre, Apellido1,

 Apellido2, NifCif, FxNacimiento

FROM CLIENTES

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

 DECLARE cClientes CURSOR OPTIMISTIC FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

    Por último, queda la opción TYPE_WARNING

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

 DECLARE cClientes CURSOR TYPE_WARNING FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

Podemos especificar múltiples parámetros en la apertura de cursor, pero únicamente un parámetro de

cada grupo. Por ejemplo:

 DECLARE cClientes CURSOR LOCAL STATIC TYPE_WARNING FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES
 Para actualizar los datos de un cursor debemos especificar FOR UPDATE después de la sentencia SELECT
en la declaración del cursor, y WHERE CURRENT OF <nombre_cursor> en la sentencia UPDATE tal y
como muestra el siguiente ejemplo.

-- Declaracion de variables para el cursor

DECLARE @Id int,

@Nombre varchar(255),

@Apellido1 varchar(255),

@Apellido2 varchar(255),

@NifCif varchar(20),

@FxNacimiento datetime

-- Declaración del cursor

DECLARE cClientes CURSOR FOR

SELECT Id, Nombre, Apellido1,

Apellido2, NifCif, FxNacimiento

FROM CLIENTES

FOR UPDATE

 -- Apertura del cursor

OPEN cClientes

-- Lectura de la primera fila del cursor

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

WHILE (@@FETCH_STATUS = 0 )

BEGIN

UPDATE Clientes

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

WHERE CURRENT OF cClientes

-- Lectura de la siguiente fila del cursor

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

END

-- Cierre del cursor

CLOSE cClientes

-- Liberar los recursos

DEALLOCATE cClientes

Transacciones en Transact SQL

Concepto de transacción

Una transacción es un conjunto de operaciones Transact SQL que se ejecutan como un único bloque, es
decir, si falla una operación Transact SQL fallan todas. Si una transacción tiene éxito, todas las
modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte
permanente de la base de datos. Si una transacción encuentra errores y debe cancelarse o revertirse, se
borran todas las modificaciones de los datos.

El ejemplo clásico de transacción es una transferencia bancaria, en la que quitamos saldo a una cuenta y
lo añadimos en otra. Si no somo capaces de abonar el dinero en la cuenta de destino, no debemos
quitarlo de la cuenta de origen.

SQL Server funciona por defecto con Transacciones de confirmación automática, es decir, cada
instrucción individual es una transacción y se confirma automáticamente. 

Sobre el ejemplo anterior de la transferencia bancaria, un script debería realizar algo parecido a los
siguientes:

DECLARE @importe DECIMAL(18,2),

@CuentaOrigen VARCHAR(12),

@CuentaDestino VARCHAR(12)

/* Asignamos el importe de la transferencia

* y las cuentas de origen y destino

*/

SET @importe = 50
SET @CuentaOrigen = '200700000001'

SET @CuentaDestino = '200700000002'

/* Descontamos el importe de la cuenta origen */

UPDATE CUENTAS

SET SALDO = SALDO - @importe

WHERE NUMCUENTA = @CuentaOrigen

/* Registramos el movimiento */

INSERT INTO MOVIMIENTOS

(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)

SELECT

IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()

FROM CUENTAS

WHERE NUMCUENTA = @CuentaOrigen

/* Incrementamos el importe de la cuenta destino */

UPDATE CUENTAS

SET SALDO = SALDO + @importe

WHERE NUMCUENTA = @CuentaDestino

/* Registramos el movimiento */

INSERT INTO MOVIMIENTOS

(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)

SELECT

IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()

FROM CUENTAS
 WHERE NUMCUENTA = @CuentaDestino

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

Transacciones implícitas y explicitas

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

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

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

--Activamos el modo de transacciones 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 @importe DECIMAL(18,2),

@CuentaOrigen VARCHAR(12),

@CuentaDestino VARCHAR(12)

 /* Asignamos el importe de la transferencia

* y las cuentas de origen y destino

*/

SET @importe = 50
SET @CuentaOrigen = '200700000002'

SET @CuentaDestino = '200700000001'

 BEGIN TRANSACTION -- O solo BEGIN TRAN

BEGIN TRY

/* Descontamos el importe de la cuenta origen */

UPDATE CUENTAS

SET SALDO = SALDO - @importe

WHERE NUMCUENTA = @CuentaOrigen

 /* Registramos el movimiento */

INSERT INTO MOVIMIENTOS

(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR,

IMPORTE, FXMOVIMIENTO)

SELECT

IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()

FROM CUENTAS

WHERE NUMCUENTA = @CuentaOrigen

 /* Incrementamos el importe de la cuenta destino */

UPDATE CUENTAS

SET SALDO = SALDO + @importe

WHERE NUMCUENTA = @CuentaDestino

 /* Registramos el movimiento */

INSERT INTO MOVIMIENTOS

(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR,

IMPORTE, FXMOVIMIENTO)

SELECT

IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()

 FROM CUENTAS

WHERE NUMCUENTA = @CuentaDestino

 /* Confirmamos la transaccion*/

COMMIT TRANSACTION -- O solo COMMIT

 END TRY

BEGIN CATCH

/* Hay un error, deshacemos los cambios*/

ROLLBACK TRANSACTION -- O solo ROLLBACK

PRINT 'Se ha producido un error!'

END CATCH

    El siguiente ejemplo muestra el mismo script con transacciones implicitas.

SET IMPLICIT_TRANSACTIONS ON

 DECLARE @importe DECIMAL(18,2),

@CuentaOrigen VARCHAR(12),

@CuentaDestino VARCHAR(12)

 /* Asignamos el importe de la transferencia

* y las cuentas de origen y destino

*/

SET @importe = 50

SET @CuentaOrigen = '200700000002'

SET @CuentaDestino = '200700000001'

 BEGIN TRY

/* Descontamos el importe de la cuenta origen */

UPDATE CUENTAS

SET SALDO = SALDO - @importe

WHERE NUMCUENTA = @CuentaOrigen

 /* Registramos el movimiento */

INSERT INTO MOVIMIENTOS

(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR,

IMPORTE, FXMOVIMIENTO)

SELECT

IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()

FROM CUENTAS

WHERE NUMCUENTA = @CuentaOrigen

 /* Incrementamos el importe de la cuenta destino */

UPDATE CUENTAS

SET SALDO = SALDO + @importe

WHERE NUMCUENTA = @CuentaDestino

 /* Registramos el movimiento */

INSERT INTO MOVIMIENTOS

(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR,

IMPORTE, FXMOVIMIENTO)

SELECT

IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()

FROM CUENTAS

WHERE NUMCUENTA = @CuentaDestino

 /* Confirmamos la transaccion*/

COMMIT TRANSACTION -- O solo COMMIT

END TRY

BEGIN CATCH

/* Hay un error, deshacemos los cambios*/

 ROLLBACK TRANSACTION -- O solo ROLLBACK

PRINT 'Se ha producido un error!'

END CATCH

La transacción sigue activa hasta que emita una instrucción COMMIT o ROLLBACK. Una vez que la
primera transacción se ha confirmado o revertido, se inicia automáticamente una nueva transacción la
siguiente vez que la conexión ejecuta una instrucción para modificar datos.

La conexión continúa generando transacciones implícitas hasta que se desactiva el modo de

transacciones implícitas.

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

SET IMPLICIT_TRANSACTIONS ON

BEGIN TRY

UPDATE CUENTAS SET FXALTA = FXALTA - 1

PRINT @@TRANCOUNT

COMMIT

END TRY

BEGIN CATCH

ROLLBACK

PRINT 'Error'

END CATCH

Otro punto a tener en cuenta cuando trabajamos con transacciones son los bloqueos y el nivel de
aislamiento. Podemos aprender más sobre bloqueos y nivel de aislamiento en este articulo.

Transacciones anidadas

Podemos anidar varias transacciones. Cuando anidamos varias transacciones la instrucción COMMIT
afectará a la última transacción abierta, pero ROLLBACK afectará a todas las transacciones abiertas.

Un hecho a tener en cuenta, es que, si hacemos ROLLBACK de la transacción superior se desharán

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

BEGIN TRAN
  UPDATE EMPLEADOS

SET NOMBRE = '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 la posibilidad de
utilizar puntos de guardado o SAVEPOINTs.

Puntos de recuperacion (SavePoint).

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

El siguiente ejemplo muestra 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

Select FOR XML

Clausula FOR XML.

A partir de la versión 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 es
devuelto:

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

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

    Un ejemplo de XML AUTO.

SELECT CO_FAMILIA, FAMILIA

FROM FAMILIAS

ORDER BY FAMILIA

FOR XML AUTO, TYPE

    Obtendremos el siguiente resultado:

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

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

<FAMILIAS CO_FAMILIA="3" FAMILIA="FAMILIA 3" />

<FAMILIAS CO_FAMILIA="4" FAMILIA="FAMILIA 4" />

    Podemos obtener el resultado como elementos de la siguiente forma:

SELECT CO_FAMILIA, FAMILIA

FROM FAMILIAS

FOR XML AUTO, ELEMENTS

    Obtendremos el siguiente resultado:

<FAMILIAS>

<CO_FAMILIA>1</CO_FAMILIA>

<FAMILIA>FAMILIA 1</FAMILIA>

</FAMILIAS>

<FAMILIAS>

<CO_FAMILIA>2</CO_FAMILIA>

<FAMILIA>FAMILIA 2</FAMILIA>

</FAMILIAS>

<FAMILIAS>

<CO_FAMILIA>3</CO_FAMILIA>

<FAMILIA>FAMILIA 3</FAMILIA>

</FAMILIAS>

<FAMILIAS>

<CO_FAMILIA>4</CO_FAMILIA>

<FAMILIA>FAMILIA 4</FAMILIA>
 </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" />

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

<row CO_FAMILIA="3" FAMILIA="FAMILIA 3" />

<row CO_FAMILIA="4" FAMILIA="FAMILIA 4" />

    Podemos obtener el resultado como elementos de la siguiente forma:

<row>

<CO_FAMILIA>1</CO_FAMILIA>

<FAMILIA>FAMILIA 1</FAMILIA>

</row>

<row>

<CO_FAMILIA>2</CO_FAMILIA>

<FAMILIA>FAMILIA 2</FAMILIA>

</row>

<row>

<CO_FAMILIA>3</CO_FAMILIA>

<FAMILIA>FAMILIA 3</FAMILIA>

</row>
 <row>

<CO_FAMILIA>4</CO_FAMILIA>

<FAMILIA>FAMILIA 4</FAMILIA>

</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" />

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

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

    Del mismo modo con la opción ELEMENTS:

SELECT CO_FAMILIA, FAMILIA

FROM FAMILIAS

ORDER BY FAMILIA

FOR XML RAW ('FamiliasDeProductos') , ELEMENTS

   

Obtendremos el siguiente resultado:

<FamiliasDeProductos>

<CO_FAMILIA>1</CO_FAMILIA>

<FAMILIA>FAMILIA 1</FAMILIA>
 </FamiliasDeProductos>

<FamiliasDeProductos>

<CO_FAMILIA>2</CO_FAMILIA>

<FAMILIA>FAMILIA 2</FAMILIA>

</FamiliasDeProductos>

<FamiliasDeProductos>

<CO_FAMILIA>3</CO_FAMILIA>

<FAMILIA>FAMILIA 3</FAMILIA>

</FamiliasDeProductos>

<FamiliasDeProductos>

<CO_FAMILIA>4</CO_FAMILIA>

<FAMILIA>FAMILIA 4</FAMILIA>

</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" />

<FamiliaDeProductos CODIGO_FAMILIA="2" DESCRIPCION="FAMILIA 2" />

<FamiliaDeProductos CODIGO_FAMILIA="3" DESCRIPCION="FAMILIA 3" />

<FamiliaDeProductos CODIGO_FAMILIA="4" DESCRIPCION="FAMILIA 4" />

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

<!--Registro de la tabla familias-->

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

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

<FAMILIAS CO_FAMILIA="3" FAMILIA="FAMILIA 3" />

<FAMILIAS CO_FAMILIA="4" FAMILIA="FAMILIA 4" />

<!--Registro de la tabla Productos-->

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

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

<PRODUCTOS CO_PRODUCTO="3" CO_CATEGORIA="2" PRODUCTO="PRODUCTO 3" />

SECCION AGREGADA POR: ALEXI ESPINOZA

SQL Server 2005: ROW_NUMBER() y WITH

En SQL 2005 existe una nueva función llamada ROW_NUMBER() que te devuelve la
posición de la fila devuelta dentro del orden que le indiques.

Por ejemplo, la consulta:

SELECT ROW_NUMBER() OVER (ORDER BY Puntos DESC) AS Posicion, Nombre,Puntos

FROM Equipos

Nos devolvería los equipos de una liga ordenados de mayor a menor número de
puntos conseguidos y su posición dentro de la clasificación.

WITH es otra nueva característica que te permite crear resultados temporales de

una consulta para luego utilizarlos. Si unimos esto característica con
ROW_NUMBER() podemos paginar de forma sencilla.

Este ejemplo muestra como podríamos obtener la clasificación por equipos de con la
posición, donde @StartRowIndex indica la posición inicial y @NumRows el número
de filas que se desea mostrar.

WITH Clasificacion AS (
SELECT ROW_NUMBER() OVER (ORDER BY Puntos DESC) AS Posicion, Nombre, Puntos
FROM Equipos)

SELECT Posicion, Nombre, Puntos

FROM Clasificacion
WHERE Posicion between @StartRowIndex and @StartRowIndex+@NumRows
EL SIGUIENTE EJEMPLO SE UTILIZA AMBAS CLAUSULAS:

WITH Enumerado(nro,id_mes,id_loc,cod_mes,cvo_mes)
as (
select ROW_NUMBER() OVER( ORDER BY id_loc DESC ) AS
nro,id_mes,id_loc,cod_mes,cvo_mes from dbo.TB_Mesavota
where id_loc =2610
)
SELECT *FROM Enumerado where nro<=10
CEILING (Transact-SQL)
Devuelve el entero más pequeño mayor o igual que la expresión numérica especificada.

 Sintaxis

CEILING ( numeric_expression )
 Argumentos

numeric_expression

Es una expresión de la categoría de tipo de datos numérico exacto o numérico aproximado, excepto para el tipo de
datos bit.

 Tipos de valor devueltos

Devuelve el mismo tipo que numeric_expression.

 Ejemplos

El siguiente ejemplo muestra valores numéricos positivos, negativos y cero con la función CEILING.

SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)

GO

Éste es el conjunto de resultados.

--------- --------- -------------------------

124.00 -123.00 0.00

(1 row(s) affected)