procedimientos almacenados, funciones y desencadenadores. En lugar de ejecutar una sola instrucción o comando a la vez, puede crear objetos que contienen bloques de código en un lenguaje de programación que completa con las variables,parámetros, el flujo de control, y manejo de errores.
SQL Server se distribuye con cientos de funciones y le permite crear sus propias funciones para encapsular frecuentemente las operaciones de recuperación de datos. Los desencadenantes(triggers) son una forma especial de procedimiento almacenado que se ejecutan automáticamente cuando el lenguaje de manipulación de datos (DML) o lenguaje de definición de datos (DDL) ejecutan comandos.
Lección 1: procedimientos almacenados
Los procedimientos almacenados ofrecen una capa de abstracción que protege las aplicaciones de la estructura de base de datos subyacente. Como la columna vertebral de cualquier aplicación de servidor SQL, los procedimientos almacenados le permiten hacer cambios en la estructura de base de datos y gestionar el rendimiento sin necesidad de reescribir las aplicaciones o desplegar actualizaciones en las aplicaciones.
Creación de procedimientos almacenados
Un procedimiento almacenado es una o más instrucciones que se le ha dado un nombre y se almacena en una base de datos. Casi todos los comandos en el lenguaje T-SQL se pueden incluir en un procedimiento almacenado, por lo que los procedimientos adecuados para las aplicaciones y para la realización de innumerables acciones administrativas. Los únicos comandos que no se puede utilizar en un procedimiento almacenado son los siguientes:
USE <database name>
SET SHOWPLAN_TEXT
SET SHOWPLAN_ALL
SET PARSEONLY
SET SHOWPLAN_XML
CREATE AGGREGATE
CREATE RULE
CREATE DEFAULT
CREATE SCHEMA
CREATE FUNCTION o ALTER FUNCTION
CREATE TRIGGER o ALTER TRIGGER
CREATE PROCEDURE o ALTER PROCEDURE
CREATE VIEW o ALTER VIEW
La primera vez que se tiene acceso a un procedimiento almacenado SQL Server genera planes de compilación y de ejecución que se almacenan en la caché de consultas y se reutiliza para posteriores ejecuciones.
Por lo tanto, usted puede recibir un rendimiento ligeramente mejor cuando se utiliza un procedimiento almacenado, evitando la necesidad de analizar, compilar y generar un plan de consulta en posteriores ejecuciones de un procedimiento almacenado. Sin embargo, el propósito principal de un procedimiento almacenado es proporcionar una capa de seguridad y una API para sus bases de datos para aislar las aplicaciones de los cambios en la estructura de base de datos.
Cuando se especifica la opción de ENCRYPTION , disponible para los disparadores, funciones, procedimientos y vistas SQL Server aplica a nivel de bits un OR con el código en el objeto. La opción de cifrado es un remanente de las primeras versiones de SQL Server y genera un poco de confusión. Cuando se especifica la opción de codificación, usted no esta aplicando una rutina de cifrado para ocultar su código. El algoritmo que utiliza SQL Server es un simple operador OR que sólo ofusca el código en el objeto. Si nos fijamos en la definición del objeto, que aparece como texto ininteligible. Sin embargo, una manera muy sencilla, rutinaria a disposición del público invierte la ofuscación.
SQL Server no permiten ocultar el código en triggers, funciones, vistas y procedimientos almacenados, y cualquier persona con autoridad VIEW DEFINITION sobre el objeto se puede recuperar el código que ha escrito.
SQL Server no es una administración de derechos digitales (DRM). El texto del módulo no está cifrado, sino que está ofuscado. Cualquier usuario con acceso a los metadatos de la base de datos puede realizar ingeniería inversa del texto protegido. La opción de cifrado no es la intención de evitar que un usuario la lectura del código dentro de sus módulos.Si desea modificar el contenido de un procedimiento almacenado o las opciones de procedimiento, puede utilizar la instrucción ALTER PROCEDURE.
Lo que diferencia a un procedimiento almacenado, aparte de un lote simple de T-SQL son todas las estructuras de código que se pueden emplear, tales como variables, parametrización, manejo de errores, y las construcciones de control de flujo.
Comentando Código:
Una de las características del código bien estructurado es que los comentarios simplifican el mantenimiento futuro.
Buenas Practicas: Usted debe incluir suficientes comentarios en su código para permitir a los desarrolladores con posterioridad a entender lo que hace el código, así como las soluciones específicas que deben ser implementadas. La única vez que un disparador, una función o procedimiento almacenado no debe tener un comentario es cuando el código es totalmente obvia, como una sola instrucción SELECT contenido dentro de un procedimiento almacenado.T-SQL tiene dos diferentes construcciones para comentar código, de la siguiente manera:
--Este es un comentario de una línea
/*
Este es un comentario
multi - línea
*/
Precaución: El delimitador por lotes de T-SQL es la palabra clave GO. Cuando SQL Server se encuentra con un GO, el lote se considerará terminado y el código posterior se considera otro lote. Incluso si el GO se encuentra dentro de un bloque de comentario, SQL Server sigue interpreta el GO como un delimitador de lotes.Variables, parámetros y códigos de retorno
SQL Server proporciona tres tipos de objetos que están diseñados para pasar los valores dentro de su código, así como devolver un valor escalar a una rutina de llamada.
Variables: Variables proporcionan una manera de manipular, almacenar y transmitir datos dentro de un procedimiento almacenado, así como entre los procedimientos almacenados y funciones. SQL Server tiene dos tipos de variables: locales y globales. Una variable local es designado por un solo signo de arroba (@), mientras que una variable global es designado por un doble en la señal de arroba (@@). Además, puede crear, leer, y escribir las variables locales, pero sólo se pueden leer los valores de las variables globales. La Tabla 5-1 muestra algunas de las variables globales más comunes (Para mas info clic AQUI).
| Variable Global | Definición |
| @@ERROR | Código de error de la última sentencia ejecutada |
| @@IDENTITY | los último valor de identidad insertado dentro de la conexión |
| @@ROWCOUNT | El número de filas afectadas por la última declaración |
| @@TRANCOUNT | El número de transacciones abiertas en la conexión |
| @@VERSION | La versión de SQL Server |
La variable @@ IDENTITY contiene el último valor de identidad insertado en la conexión.
Si usted llama a varios módulos de código que insertan los valores de identidad, el último valor insertado siempre está contenido en @@ IDENTITY. Por ejemplo, si usted llama a un procedimiento que inserta una fila en la Tabla A, que tiene un desencadenador que inserta filas en TableB TableC, y TableD, con cada inserción generando un nuevo valor de identidad, el valor de @@IDENTITY que se recupera después de la insertar en la Tabla A se corresponde con el valor de identidad insertado en TableD.
Debido a esta limitación, el uso de @@IDENTITY es totalmente desaconsejado. Utilice la función SCOPE_IDENTITY() en su lugar, que devuelve el último valor de identidad insertado en el ámbito de la instrucción actual.
Crear una instancia de una variable con la cláusula DECLARE, donde se especifica el nombre y el tipo de datos de la variable. Una variable se puede definir con cualquier tipo de datos excepto text, ntext e image. Por ejemplo:
DECLARE @intvariable INT, @datevariable DATE, @spatialvar GEOGRAPHY, @levelvar HIERARCHYID DECLARE @tablevar TABLE (ID INT NOT NULL, Customer VARCHAR(50) NOT NULL)
Mientras que una sola instrucción DECLARE se puede utilizar para crear instancias de variables múltiples, la creación de instancias de una variable de tabla debe estar en un DECLARE aparte .
Se puede asignar un valor estático o un valor simple para el retorno de un comando SELECT a una variable. Una instrucción SET o SELECT se puede utilizar para asignar un valor, sin embargo, si usted está ejecutando una consulta para asignar un valor, debe utilizar una instrucción SELECT. SELECT también se utiliza para devolver el valor de una variable.
Además de asignar un valor con una instrucción SET o SELECT, también puede asignar un valor a la vez una instancia de una variable. Aquí están algunos ejemplos de configuración de las variables de varias maneras:
DECLARE @intvariable INT = 2, @datevariable DATE = GETDATE(), @maxorderdate DATE = (SELECT MAX(OrderDate) FROM Orders.OrderHeader), @counter1 INT, @counter2 INT SET @counter1 = 1 SELECT @counter2 = -1 SELECT @intvariable, @datevariable, @maxorderdate, @counter1, @counter2
Una precaución en la asignación de variables: Uno de los errores más comunes es olvidar que, con la excepción de una variable de tabla, las variables contienen valores escalares. Si se asigna el resultado de una sentencia SELECT a una variable, debe asegurarse de que sólo un registro es devuelto por la sentencia SELECT. Si la instrucción SELECT devuelve más de una fila, la variable se establece en el valor correspondiente a la última fila del conjunto de resultados y todos los demás valores en el conjunto de resultados se descartan.Una variable se puede utilizar para realizar cálculos, procesamiento de control, o llenar un argumento de búsqueda (SARG) en una consulta. Se pueden realizar cálculos con variables mediante una instrucción SET o una instrucción SELECT.SQL Server 2008 introduce una forma más compacta de asignar valores a las variables a través de un cálculo:
--SQL Server 2005 e inferiores DECLARE @var INT SET @var = 1 SET @var = @var + 1 SELECT @var SET @var = @var * 2 SELECT @var SET @var = @var / 4 SELECT @var GO --SQL Server 2008 DECLARE @var INT SET @var = 1 SET @var += 1 SELECT @var SET @var *= 2 SELECT @var SET @var /= 4 SELECT @var GO
Parámetros: Los parámetros son variables locales que se utilizan para pasar valores a un procedimiento almacenado cuando se ejecuta. Durante la ejecución, los parámetros se utilizan como variables y pueden ser leídos y escritos. Se declara un parámetro como en este ejemplo:
CREATE PROCEDURE <procedure name> @parm1 INT, @parm2 VARCHAR(20) = 'Default value' AS --Bloque de código
Se pueden crear dos tipos de parámetros: entrada y salida. Un parámetro de salida se designa mediante el uso de la palabra clave OUTPUT:
CREATE PROCEDURE <procedure name> @parm1 INT, @parm2 VARCHAR(20) = 'Default value', @orderid INT OUTPUT AS --Bloque de codigo
Los parámetros de salida se utilizan cuando se necesita devolver un único valor para una aplicación. Si necesita devolver un conjunto de resultados completo, que incluye una sentencia SELECT en el procedimiento almacenado que genera los resultados y devuelve el conjunto de resultados a la aplicación como se muestra aquí:
CREATE PROCEDURE <procedure name> @parm1 INT, @parm2 VARCHAR(20) = 'Default value' AS --Esto devolverá los resultados de esta consulta a una aplicación SELECT SalesOrderID, CustomerID, OrderDate, SubTotal, TaxAmt, Freight, TotalDue FROM Sales.SalesOrderHeader
Las construcciones de control de flujo
- RETURN
- IF. . .ELSE
- BEGIN. . .END
- WHILE
- BREAK/CONTINUE
- WAITFOR
- GOTO
RETURN finaliza la ejecución del procedimiento y devuelve el control a la aplicación que llama. Todas las declaraciones después de la sentencia RETURN no se ejecutan, como se muestra aquí:
CREATE PROCEDURE <procedure name> @parm1 INT, @parm2 VARCHAR(20) = 'Default value' AS --Esto devolverá el valor 1 al llamador del procedimiento almacenado RETURN 1 --Cualquier código de ahora en adelante no se ejecutará
IF. . . ELSE proporciona la capacidad de ejecutar código condicionalmente. La instrucción IF comprueba la condición suministrados y ejecuta la instrucción siguiente, cuando la condición se evalúa como True.
La instrucción ELSE opcional le permite ejecutar código cuando la condición da como resultado False. Aquí está un ejemplo:
DECLARE @var INT SET @var = 1 IF @var = 1 PRINT 'Este es el código que se ejecuta cuando la condición es TRUE.' ELSE PRINT 'Este es el código que se ejecuta cuando la condición es FALSE.'
Con independencia del código necesario para que un IF. . . ELSE, sólo la declaración siguiente es ejecutado condicionalmente, como se demuestra en el siguiente ejemplo:
DECLARE @var INT SET @var = 1 IF @var = 2 PRINT 'Este es el código que se ejecuta cuando la condición es TRUE.' PRINT 'Este es el código que se ejecuta siempre.'
Debido a que una sentencia condicional IF sólo ejecuta la siguiente línea de código, usted tiene un problema cuando se quiere ejecutar un bloque de código de forma condicional. La sentencia BEGIN. . . END permite delimitar los bloques de código que se ejecutan como una unidad, como se muestra aquí:
DECLARE @var INT SET @var = 1 IF @var = 2 BEGIN PRINT 'Este es el código que se ejecuta cuando la condición es TRUE.' PRINT 'Este código también se ejecuta cuando la condición es TRUE.' END
WHILE se usa para ejecutar iterativamente un bloque de código mientras una condición especificada sea verdadera.
Aquí está un ejemplo:
DECLARE @var1 INT, @var2 VARCHAR(30) SET @var1 = 1 WHILE @var1 <= 10 BEGIN SET @var2 = 'Iteration #' + CAST(@var1 AS VARCHAR(2)) PRINT @var2 SET @var1 += 1 END
Buenas Practicas: Uno de los errores más comunes que usted puede hacer al escribir bloques de código que utilizan un IF o un WHILE es olvidar que SQL Server ejecuta la siguiente sentencia sólo de manera condicional.
Para evitar los errores más comunes de codificación, se recomienda utilizar siempre un BEGIN. . . END con un IF o WHILE, incluso cuando se va a ejecutar una sola línea de código de forma condicional. No sólo hace que el código sea más legible, sino que también ayuda a prevenir errores en tu código se ha modificado en el futuro.
BREAK se utiliza junto con un bucle WHILE. Si usted necesita terminar la ejecución dentro de un bucle WHILE, se puede utilizar la sentencia break para terminar con la repetición del bucle. Una vez que se ejecuta BREAK, la siguiente línea de código que sigue al bucle WHILE se ejecuta. CONTINUE se utiliza dentro de un bucle WHILE para hacer que el código sigue ejecutándose desde el principio del bucle.
Declaraciones BREAK y CONTINUE casi nunca se usan. Un bucle WHILE termina tan pronto como la condición para el bucle WHILE ya no es verdad. En vez de incluir una prueba condicional, junto con una declaración de BREAK, los bucles WHILE se controlan normalmente mediante el uso de condiciones adecuadas para el WHILE.Además, siempre que la condición para el WHILE es cierta, el ciclo continúa con la ejecución. Por lo tanto, nunca debería ser necesario utilizar una instrucción CONTINUE.
WAITFOR se utiliza para permitir la ejecución de código para hacer una pausa.WAITFOR tiene tres variantes diferentes: WAITFOR DELAY, TIME WAITFOR y WAITFOR RECEIVE. WAITFOR RECEIVE se utiliza en conjunción con WAITFOR TIME de Service Broker para hacer pausas en la ejecución de código hasta que una hora determinada se alcanza. WAITFOR DELAY detiene la ejecución de código por un período específico de tiempo:
DECLARE @var1 INT, @var2 VARCHAR(30) SET @var1 = 1 --Pausa por 2 segundos WAITFOR DELAY '00:00:02' WHILE @var1 <= 10 BEGIN SET @var2 = 'Iteration #' + CAST(@var1 AS VARCHAR(2)) PRINT @var2 SET @var1 += 1 END
GOTO le permite pasar a la ejecución de una etiqueta incrustada en el procedimiento. Construcciones de código tales como GOTO son desalentados en todos los lenguajes de programación.
Mensajes de error:
Mensajes de error en SQL Server tiene tres componentes:
- Número de error
- Nivel de gravedad
- Mensaje de error
SQL Server define 26 los niveles de severidad numerados del 0 al 25. Cualquier error con un nivel de gravedad de 16 o más alto se registra automáticamente en el registro de errores de SQL Server y el registro de aplicación de eventos de Windows. Los errores con un nivel de gravedad de 19 a 25 sólo se puede especificar por miembros de la función sysadmin. Los errores con un nivel de gravedad de 20 a 25 son considerados fatales y cualquier conexión que desea terminar y abrir a cualquier transacción se deshace.
El mensaje de error puede ser de hasta 255 caracteres Unicode de longitud y permite hasta dos parámetros que deben pasarse.
Usted puede crear sus propios mensajes de error personalizados, que deberán numerarse desde el 50001 y superiores. (El número 50000 está reservado para designar aun mensaje cuyo número no se especifica).
Mensajes de error pueden ser adaptados para cada idioma en que SQL Server es compatible, sin embargo, es necesario crear una versión en Inglés del mensaje antes de crear una versión que no este en Inglés.
Para crear un mensaje de error personalizado mediante la ejecución de sp_addmessage de la siguiente manera:
El siguiente ejemplo se crea un mensaje personalizado en el idioma Inglés, con un número de error de 50.001 y una gravedad de 16:
EXEC sp_addmessage 50001, 16, N'The approved credit must be between 100 and 10,000'; GO
El siguiente ejemplo se crea un mensaje personalizado en Inglés y Francés que acepta dos parámetros con un número de mensaje de 50.002 y una gravedad de 16:
EXEC sp_addmessage @msgnum = 50002, @severity = 16, @msgtext = N'The product named %s already exists in %d.', @lang = 'us_english'; EXEC sp_addmessage @msgnum = 50002, @severity = 16, @msgtext = N' %1! de produit existent déjà dans %2!', @lang = 'French'; GO
El primer parámetro es designado como %s, mientras que el segundo parámetro es designado como %d. Los parámetros %s y %d se utilizan sólo con mensajes basados en ingles de EE.UU. Todos los otros lenguajes usan %1! para designar el primer parámetro y el %2! para designar el segundo parámetro.
Para enviar un mensaje error al ejecutar el comando RAISERROR, se usa la siguiente sintaxis:
RAISERROR ( { msg_id | msg_str | @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] ]
Puede ver los mensajes disponibles para la instancia de SQL Server mediante la consulta de la vista de catálogo sys.messages. Los mensajes de error personalizados pueden ser modificados mediante la ejecución del procedimiento almacenado sp_altermessage. Usted puede eliminar un mensaje de error personalizado mediante la ejecución del procedimientos almacenados sp_dropmessage.
Gestión de errores
Aunque usted siempre escriba código libre de errores ,haya tenido acceso siempre en un lugar bien definido, de manera predecible, nunca debería haber ningún error. Sin embargo, todo el código está siempre sujeto al fracaso.
Una aplicación podría tratar de pasar los parámetros no válidos, las reglas de negocio pueden ser violados, o el código puede no ser diseñado para manejar las llamadas realizadas a partir de una serie de nuevas aplicaciones. Por lo tanto, es necesario incluir el tratamiento de errores en los procedimientos almacenados, que permite que el origen de un problema que se diagnostica y se fija de una manera fácil de usar.
Antes de SQL Server 2005, la única manera de llevar a cabo la gestión de errores fue evaluar el valor de la variable @@ERROR global. Cuando cada instrucción se ejecuta, SQL Server registra el estado de los resultados de @@ERROR. Si ocurre un error, @@ERROR contiene el número de error.
Si la declaración se ha realizado correctamente, @@ERROR contiene un 0. A continuación, deberá consultar la variable para determinar si una declaración es correcta o incorrecta. Desafortunadamente, el simple hecho de ejecutar una instrucción SELECT para recuperar el valor de @@ERROR también se establece el valor de la variable, por lo que sobre-escribe cualquier valor de error anterior. Usar @@ERROR para llevar a cabo la gestión de errores es muy engorroso, lo cual que requiere integrar los controles después de cada declaración, junto con una rutina de control de errores para cada declaración.
Para proporcionar una forma más estructurada de manejo de errores muy parecida a las rutinas de manejo de errores de otros lenguajes de programación, ahora se puede utilizar TRY. . . CATCH.
El TRY. . . CATCH tiene dos componentes. El bloque TRY se utiliza para envolver cualquier código en el que es posible que reciba un error que desea atrapar y manipular. El bloque CATCH se utiliza para controlar el error.
El siguiente código crea un error debido a la violación de una restricción de clave principal. Ud. podría esperar que este código hiciera salir de la misma, dejando una tabla vacía debido al error en la transacción, Sin embargo, te encuentras con que el comando primer y el tercer INSERT tuvieron éxito y deja dos filas de la tabla:
--Errores de Transacción CREATE TABLE dbo.mytable (ID INT NOT NULL PRIMARY KEY) BEGIN TRAN INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(2) COMMIT TRAN SELECT * FROM dbo.mytable
La razón por la que tiene dos filas insertadas en la tabla es porque por defecto, SQL Server no deshace una transacción que tiene un error. Si desea que la transacción se complete en su totalidad o no por completo, puede utilizar el comando SET para cambiar la configuración de XACT_ABORT en su conexión, de la siguiente manera:
TRUNCATE TABLE dbo.mytable SET XACT_ABORT ON; BEGIN TRAN INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(2) COMMIT TRAN SET XACT_ABORT OFF; SELECT * FROM dbo.mytable
A pesar de la instrucción SET XACT_ABORT ON logra su objetivo, cuando se cambia la configuración de una conexión, puede tener resultados impredecibles para una aplicación si el código no restablece las opciones correctamente. Una solución mejor es usar un controlador de errores estructurado para atrapar y para decidir cómo manejar el error.
La forma en que TRY. . . CATCH se implementa en SQL Server 2008 es el siguiente:
- Si un error con una gravedad menor de 20 se encuentra dentro del bloque TRY, el control pasa al bloque CATCH correspondiente.
- Si se detecta un error en el bloque CATCH, se anula la transacción y el error se devuelve a la aplicación que llama a menos que el bloque catch anidado dentro de otro bloque TRY.
- El bloque CATCH debe seguir inmediatamente al bloque TRY.
- Dentro del bloque CATCH, se puede confirmar o deshacer la transacción actual a menos que la transacción está en un estado uncommitable.
- Un RAISERROR ejecutadas en el bloque TRY inmediatamente pasa el control al bloque CATCH sin devolver un mensaje de error a la aplicación.
- Un RAISERROR ejecutadas en el bloque CATCH se cierra la transacción y devuelve el control a la aplicación de llamada con el mensaje de error especificado.
- Si un RAISERROR no se ejecuta en el bloque CATCH, nunca la aplicación de llamada recibe un mensaje de error.
Un Bloque TRY. . . CATCH no puede capturar los errores que causan la conexión al darse por terminada, como un error grave o un sysadmin que ejecuta el comando KILL.Tampoco se puede capturar los errores que se producen debido a los errores de compilación, errores de sintaxis u objetos inexistentes. Por lo tanto, no puede utilizar un Bloque TRY. . . CATCH para probar la existencia de un objeto.
El siguiente código implementa control de errores estructurado para el bloque de código anterior:
--TRY...CATCH TRUNCATE TABLE dbo.mytable BEGIN TRY BEGIN TRAN INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(2) COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN PRINT 'Catch' END CATCH SELECT * FROM dbo.mytable
Uno de los aspectos más importantes de TRY. . . CATCH es que ningún mensaje de error es enviado a una aplicación a menos que un RAISERROR se ejecute dentro del CATCH. Dentro del bloque CATCH, se tiene acceso a las siguientes funciones:
- ERROR_NUMBER () El número de error del error producido
- ERROR_MESSAGE () El texto del mensaje de error
- ERROR_SEVERITY () El nivel de gravedad del mensaje de error
- ERROR_STATE () El estado del error
- ERROR_PROCEDURE () El nombre de función, un desencadenador o un procedimiento que se estaba ejecutando cuando se produjo el error
- ERROR_LINE () La línea de código dentro de la función, un desencadenador o un procedimiento que causó el error
Por lo tanto, existe un ligero problema si quieres devolver un error generado por el sistema a una aplicación que llama.
Si desea devolver un mensaje de error del sistema, se debe de forma dinámica construir un mensaje que incluye el error del sistema de información de mensajes,que se devuelven con una instrucción RAISERROR que no proporciona un identificador de mensaje. De esta forma, cualquiera de los mensajes del sistema generados se devuelven siempre con un número de error 50000.
Dentro del bloque CATCH, se puede determinar el nivel de anidamiento de la transacción actual con la variable global @@TRANCOUNT. También puede recuperar el estado de la transacción más interna con la función XACT_STATE. La función puede devolver XACT_STATE los siguientes valores:
1: Una transacción abierta existe y puede ser confirmado o revertido.XACT_ABORT se comporta diferente cuando se utiliza junto con un bloque TRY. En vez de terminar la transacción, se transfiere el control al bloque CATCH. Sin embargo, si XACT_ABORT está activada, cualquier error es fatal. La transacción se deja en un estado condenado al fracaso y vuelve XACT_STATE -1. Por lo tanto, no se puede confirmar una transacción dentro de un bloque CATCH si XACT_ABORT está encendido.
0: No hay ninguna transacción abierta.
-1: Una transacción abierta existe, pero está en un estado condenado al fracaso.Debido al tipo de error que se ha planteado, la operación sólo se puede revertir.
Ejecutando procedimientos almacenados
Se accede a un procedimiento almacenado con una instrucción EXEC. Si un procedimiento almacenado no tiene parámetros de entrada, el código sólo se requiere
EXEC <stored procedure>
Si un procedimiento almacenado tiene parámetros de entrada, se puede pasar en los parámetros, ya sea por nombre o por posición:
--Por Nombre de parámetro EXEC <stored procedure> @parm1=<value>, @parm2=<value>,... --Por Posición del parámetro EXEC <stored procedure> <value>, <value>,...
Pasar parámetros a un procedimiento almacenado por resultados la posición en el código que es más compacto, sin embargo, es más propenso a errores. Cuando se pasan parámetros a un procedimiento almacenado por su nombre, los cambios en el orden de los parámetros en el procedimiento no requieren cambios en otras partes de sus aplicaciones. Independientemente de si usted está pasando parámetros por posición o por su nombre, es necesario especificar un valor para cada parámetro que no tiene valor predeterminado. Si el procedimiento almacenado se ejecuta es la primera línea en el lote, la palabra clave EXEC es opcional. Sin embargo, la palabra clave EXEC se requiere para una llamada de procedimiento almacenado en el resto del lote. Incluso si el código sólo antes de que el procedimiento almacenado es un comentario, la palabra clave EXEC sigue siendo necesaria. Para evitar confusiones y garantizar que su código se ejecuta siempre, independientemente de la estructura del lote, siempre debe incluir la palabra clave EXEC.
Para usar un parámetro de salida, es necesario especificar la palabra clave OUTPUT o OUT después de cada parámetro:
--Usando parámetros output DECLARE @variable1 <data type>, @variable2 <data type> ... EXEC <stored procedure> @parameter1, @variable1 OUTPUT, @variable2 OUT
Si usted necesita para capturar el código de retorno de un procedimiento almacenado, se debe almacenar en una variable, como sigue:
--Capturando un codigo de retorno DECLARE @variable1 <data type>, @variable2 <data type>, @returncode INT EXEC @returncode = <stored procedure> @parameter1, @variable1 OUTPUT, @variable2 OUT
Ejecución dinámica
Mientras que la ejecución de comandos dinámica es muy raro dentro de procedimientos almacenados que utilizan las aplicaciones, muchas de los procedimientos administrativos para la construcción de las órdenes y las ejecutan de forma dinámica. T-SQL tiene dos maneras de ejecutar sentencias construidas dinámicamente:
- EXEC(<comando>)
- sp_executesql <comando>
EXEC('SELECT OrderID, CustomerID FROM Sales.SalesOrderHeader WHERE OrderID = 1') GO DECLARE @var VARCHAR(MAX) SET @var = 'SELECT OrderID, CustomerID FROM Sales.SalesOrderHeader WHERE OrderID = 1' EXEC(@var) GO EXEC sp_executesql N'SELECT OrderID, CustomerID FROM Sales.SalesOrderHeader WHERE OrderID = 1' GO DECLARE @var NVARCHAR(MAX) SET @var = 'SELECT OrderID, CustomerID FROM Sales.SalesOrderHeader WHERE OrderID = 1' EXEC sp_executesql @var GO
Módulo del contexto de ejecución
Las Funciones y los procedimientos almacenados le permiten modificar el contexto de seguridad bajo el cual el objeto se ejecuta mediante el uso de la opción EXECUTE AS. EXECUTE AS tiene tres posibles argumentos:
- LOGIN: ejecuta bajo el contexto del inicio de sesión especificado.
- USER: se ejecuta bajo el contexto de seguridad del usuario de base de datos especificada. Esta cuenta no puede ser un rol, grupo, un certificado o una clave asimétrica.
- CALLER: se ejecuta bajo el contexto de seguridad de la rutina que llamó al módulo.
Cursores
SQL Server se basa en procesos conjuntos de datos. Sin embargo, hay ocasiones en que usted necesita para procesar los datos de una fila a la vez. El resultado de una sentencia SELECT se devuelve a un objeto del lado del servidor llamado cursor, que le permite acceder a una fila en un momento en el conjunto de resultados, e incluso permite el desplazamiento hacia adelante y hacia atrás a través del conjunto de resultados.
Los cursores constan de cinco componentes. DECLARE se usa para definir la instrucción SELECT que es la base de las filas en el cursor. OPEN hace que la instrucción SELECT sea ejecutada y cargue las filas en una estructura de memoria. FETCH se utiliza para recuperar una fila a la vez desde el cursor. CLOSE se utiliza para cerrar el proceso en el cursor. DEALLOCATE se utiliza para eliminar el cursor y liberar las estructuras de memoria que contiene el conjunto de resultados del cursor.
Si el cursor se utiliza dentro de un procedimiento almacenado, no es necesario cerrar y liberar el cursor. Cuando se sale del procedimiento almacenado, SQL Server se cierra automáticamente y cancela la asignación de los cursores creados en el procedimiento para recuperar el espacio de la memoria.
La sintaxis genérica para la declaración de un cursor (Mas info AQUI):
Las instrucciones siguientes muestran tres formas distintas de declarar el mismo cursor:
DECLARE curproducts CURSOR FAST_FORWARD FOR SELECT ProductID, ProductName, ListPrice FROM Products.Product GO DECLARE curproducts CURSOR READ_ONLY FOR SELECT ProductID, ProductName, ListPrice FROM Products.Product GO DECLARE curproducts CURSOR FOR SELECT ProductID, ProductName, ListPrice FROM Products.Product FOR READ ONLY GO
Una vez que el cursor se ha declarado, se emite una comando OPEN para ejecutar la sentencia SELECT:
OPEN curproducts
A continuación, deberá recuperar los datos de la fila en el cursor mediante una instrucción FETCH.
Al ejecutar FETCH por primera vez, un puntero se coloca en la primera fila en el conjunto de resultados del cursor. Cada vez que un FETCH se ejecuta, el puntero del cursor se adelanta una fila del conjunto de resultados hasta que se ejecute fuera de las filas del conjunto de resultados. Cada ejecución de FETCH también establece un valor para la variable global @@FETCH_STATUS. Por lo general, se utiliza un bucle while para iterar a través del cursor, recuperar una fila de cada iteración del bucle. Se puede recorrer en iteración la bucle WHILE, siempre y cuando @@FETCH_STATUS = 0. Aquí está un ejemplo:
DECLARE @ProductID INT, @ProductName VARCHAR(50), @ListPrice MONEY DECLARE curproducts CURSOR FOR SELECT ProductID, ProductName, ListPrice FROM Products.Product FOR READ ONLY OPEN curproducts FETCH curproducts INTO @ProductID, @ProductName, @ListPrice WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ProductID, @ProductName, @ListPrice FETCH curproducts INTO @ProductID, @ProductName, @ListPrice END CLOSE curproducts DEALLOCATE curproducts
Si va a escribir procedimientos almacenados que tienen cursores (especialmente los cursores de varios niveles), se debe reevaluar el proceso que se está tratando de escribir. Es probable que pueda sustituir a los cursores con un proceso de configuración basada en conjuntos que es más eficiente.
Se puede declarar cuatro tipos de cursores:
- FAST_FORWARD: El más rápido de realizar el tipo de cursor porque permite únicamente avanzar una fila a la vez. El SCROLL(discutido más adelante en esta sección) no es compatible. Un cursor FAST_FORWARD es lo mismo que declarar un cursor FORWARD_ONLY, READ_ONLY. FAST_FORWARD es la opción predeterminada para los cursores.
- STATIC: El conjunto de resultados se recuperan y se almacenan en una tabla temporal en la base de datos tempdb. Todos las consultas se recuperan de la tabla temporal y modificaciones a las tablas subyacentes del cursor no son visibles. Un cursor de tipo STATIC admite el desplazamiento, pero las modificaciones no están permitidas.
- KEYSET: El conjunto de claves que identifican de forma única cada fila del conjunto de resultados del cursor se almacena en una tabla temporal en tempdb. Mientras se desplaza en el cursor, las columnas que no son clave se obtienen de las tablas subyacentes. Por lo tanto, cualquier modificación a las filas se refleja cuando el cursor se desplaza. Cualquier inserciones en la tabla subyacente no son accesibles para el cursor. Si se intenta acceder a una fila que se ha eliminado, @ @ FETCH_STATUS devuelve -2.
- DYNAMIC: El cursor más costoso. El cursor refleja todos los cambios realizados en el conjunto de resultados subyacentes, incluyendo las filas recién insertadas cuando el cursor se desplaza. La posición y el orden de las filas en el cursor pueden cambiar cada vez que se realiza un recuperar. La opción de FETCH ABSOLUTE no está disponible para los cursores dinámicos.
Si todo lo que se va a hacer es leer los datos en un cursor, asegúrese de que usted está declarando el cursor como de sólo lectura. Cursores de sólo lectura requiere menos espacio que los cursores actualizables.El acceso a las filas en un cursor se puede restringir mediante el uso de las opciones FORWARD_ONLY y SCROLL . Si se declara un cursor como FORWARD_ONLY, cada fila puede ser leído sólo una vez a medida que avanza el puntero del cursor a través del conjunto de resultados. Si se declara un cursor utilizando la opción SCROLL, la sentencia FETCH tiene las siguientes opciones:
- FETCH FIRST obtiene la primera fila del conjunto de resultados.
- FETCH LAST Obtiene la última fila del conjunto de resultados.
- FETCH NEXT Busca la siguiente fila del conjunto de resultados sobre la base de la posición actual del puntero. FETCH NEXT es equivalente a sólo la ejecución de FETCH, que también avanza una fila a la vez dentro del conjunto de resultados del cursor.
- FETCH PRIOR Obtiene la fila del conjunto de resultados justo antes de la posición actual del puntero del cursor.
- FETCH ABSOLUTE n Obtiene la fila enésima desde el comienzo del conjunto de resultados.
- FETCH RELATIVE n Obtiene la enésima fila hacia adelante en el resultado sitúa el cursor en la posición actual del puntero del cursor.
- READ_ONLY: SQL Server no adquiere un bloqueo en la fila subyacente en la tabla debido a que un cursor marcado como READ_ONLY no se puede actualizar.
- SCROLL_LOCKS: se adquiere un bloqueo, ya que cada fila es leída en el cursor, lo que garantiza que cualquier operación ejecutada contra el cursor tiene éxito.
- OPTIMISTIC El bloqueo no se adquiere. SQL Server en su lugar utiliza ya sea un timestamp o un checksum calculado en el caso de que una columna de timestamp no exista para detectar si los datos han cambiado desde que fue leída en el cursor.Si los datos han cambiado, la modificación falla.
Cuando un procedimiento almacenado se crea, SQL Server comprueba la sintaxis, pero no valida ninguno de los objetos de referencia dentro del procedimiento. La primera vez que ejecuta un procedimiento almacenado, SQL Server analiza y compila el código. Cuando el procedimiento se compila por primera vez, se realiza una comprobación para asegurarse de que todos los objetos de referencia existen ose creará en el procedimiento previo a que se accede.
En tiempo de compilación, un plan de consulta se genera y se almacena en la caché de consultas. Este plan de compilación es reentrante y se reutiliza cada vez que se ejecuta el procedimiento. Cada ejecución simultánea de un procedimiento almacenado genera también un plan de consulta para su ejecución, que se denomina el plan de ejecución. Los planes de ejecución también se almacenan en la caché de consultas, pero no son reentrantes.
Una vez que una conexión ha terminado de ejecutar el procedimiento, el plan de ejecución almacenado en la caché de consultas pueden ser asignados a la próxima conexión que ejecuta el procedimiento. Por lo tanto, un único procedimiento almacenado que se usa mucho puede tener un plan de compilación única, junto con planes de ejecución de muchos en la caché de consultas, cada una de las que consume memoria.
En la mayoría de las circunstancias, guardar del plan de compilación para su reutilización elimina los recursos que deben ser utilizados para generar un plan de consulta cada vez que se ejecuta el procedimiento. Sin embargo, si el procedimiento almacenado contiene varias rutas de código en función de los resultados de la comprobación de condiciones, los planes de consulta totalmente diferente podrían ser generada para el procedimiento. Al reutilizar el mismo plan de consulta para cada ejecución, el rendimiento pueden sufrir en algunos casos. Si un procedimiento almacenado genera un plan de consulta diferente la mayoría de las veces que se ejecuta, se debe crear el procedimiento utilizando la opción RECOMPILE. Cuando la opción RECOMPILE está habilitado para un procedimiento almacenado, SQL Server no almacena en caché y la reutilización de un plan de consulta.
Además de la opción RECOMPILE, SQL Server puede detectar si las estadísticas no están actualizados durante la ejecución de un procedimiento. Si SQL Server determina que el plan de consulta puede ser inferior al óptimo en la ejecución de un procedimiento, la ejecución se detendrá mientras que un nuevo plan de consulta se genera para el siguiente comando a ejecutar dentro del procedimiento. Antes de SQL Server 2005, el plan de consulta para todo el procedimiento almacenado se regenera. Sin embargo, desde SQL Server 2005, recompilación se produce a nivel del estado.
Una de las ventajas de los procedimientos y funciones es la capacidad de SQL Server para almacenar en caché planes de consulta para las posteriores ejecuciones del código. Aunque usted quiera maximizar el almacenamiento en caché, hay momentos en que las ejecuciones posteriores podrían requerir distintos planes de consulta para la ejecución óptima. Cuando el plan de consulta generado depende de los valores utilizados para cada ejecución, debe utilizar la opción RECOMPILE para obligar a SQL Server a el procedimiento o la función cada vez que se ejecuta.
Sin embargo, un método más eficiente es la de dividir el procedimiento almacenado en varios procedimientos.
Por ejemplo, usted podría tener un procedimiento similar al siguiente, donde la ejecución de cada rama de la instrucción IF produce distintos planes de consulta:
CREATE PROCEDURE PROC1 AS IF <alguna condición> BEGIN <code block A> END ELSE BEGIN <code block B> END GO
Usted puede tomar ventaja de la caché de consultas y evitar los planes de consulta poco óptimos mediante la creación de un procedimiento almacenado para cada rama de la prueba condicional, de la siguiente manera:
CREATE PROCEDURE PROCA AS <code block A> GO CREATE PROCEDURE PROCB AS <code block B> GO CREATE PROCEDURE PROC1 AS IF <alguna condición> BEGIN EXEC PROCA END ELSE BEGIN EXEC PROCB END GO
Cuando se ejecuta Proc1, un plan muy simple de compilación se genera, que puede ser reutilizado, independientemente de la ruta de código tomado. Cuando PROCA se ejecuta por primera vez, SQL Server analiza, compila y almacena en caché del plan de compilación, que pueden ser reutilizados cada ruta de código de tiempo Ase ejecuta. Del mismo modo, el PROCB primera vez se ejecuta un plan de compilación se almacena en caché, que pueden ser posteriormente reutilizados también.
32 bits y 64 bits SQL Server: Cuando se habla de la posibilidad de implementar una versión de 32 bits o 64 bits de SQL Server, muchas personas suponen erróneamente que la versión de 64-bit automáticamente va a mejorar el rendimiento de sus aplicaciones. Las aplicaciones que obtienen las mejoras más importantes son aquellos que son consolidadadas en la memoria caché de consultas no, la caché de datos. La caché de consultas contiene el código ejecutable, que en una plataforma de 32 bits no puede residir en la memoria por encima de 4 gigabytes (GB), mientras que al mismo tiempo, SQL Server limita el tamaño máximo de la caché de consultas a cerca de 20 por ciento de la memoria asignada a la instancia SQL Server.Aplicaciones con un gran número de usuarios concurrentes puede rápidamente agotar toda la memoria disponible para el caché de consultas entre la compilación y planes de ejecución, lo que causa que más solicitudes tengan que esperar hasta que la memoria puede ser liberada en la caché de consultas.Resumen de la lección
Al instalar la versión de 64 bits de SQL Server, se elimina el límite de 4 GB para el código ejecutable y se habilita más espacio disponible para la caché de consultas. Con mayor espacio de memoria disponible, más ejecuciones simultáneas pueden ser manejadas, mejorando así el rendimiento.
- Un procedimiento almacenado es un lote de de código que T-SQL se le da un nombre y se almacena en una base de datos.
- Puede pasar parámetros a un procedimiento almacenado, ya sea por nombre o por posición. Usted también puede devolver los datos de un procedimiento almacenado con parámetros de salida.
- Puede utilizar la cláusula EXECUTE AS para hacer un procedimiento almacenado para ejecutar en un contexto de seguridad específico.
- Los cursores permiten procesar los datos en una fila por fila base, sin embargo, si usted está haciendo la misma modificación para cada fila en un cursor, un enfoque orientado a conjuntos es más eficiente.
- Un Bloque TRY. . . CATCH entrega de errores estructurado para sus procedimientos.
No hay comentarios:
Publicar un comentario