Implementacin de procedimientos almacenados Procesamiento inicial de los

Implementación de procedimientos almacenados

Procesamiento inicial de los procedimientos almacenados Creación Ejecución (por primera vez o recompilación) Análisis Se almacena en las tablas sysobjects y syscomments Optimización Compilación El plan compilado se coloca en la caché de procedimientos

Procesamientos posteriores de los procedimientos almacenados Plan de ejecución recuperado Plan de consulta Contexto de ejecución Conexión 1 SELECT * FROM dbo. member WHERE member_no = ? 8082 Conexión 2 24 Conexión 3 1003 Plan sin usar se retira

Ventajas de los procedimientos almacenados n Compartir la lógica de la aplicación n Exposición de los detalles de las tablas de la base de datos n Proporcionar mecanismos de seguridad n Mejorar el rendimiento n Reducir el tráfico de red

Creación de procedimientos almacenados n Utilice la instrucción CREATE PROCEDURE para crearlos en la base de datos activa USE Northwind GO CREATE PROC dbo. Overdue. Orders AS SELECT * FROM dbo. Orders WHERE Required. Date < GETDATE() AND Shipped. Date IS Null GO n Puede anidar hasta 32 niveles n Use sp_help para mostrar información

Recomendaciones para la creación de procedimientos almacenados n El usuario dbo debe ser el propietario de todos los procedimientos almacenados n Un procedimiento almacenado por tarea n Crear, probar y solucionar problemas n Evite sp_Prefix en los nombres de procedimientos almacenados n Utilice la misma configuración de conexión para todos los procedimientos almacenados n Reduzca al mínimo la utilización de procedimientos almacenados temporales n No elimine nunca directamente las entradas de Syscomments

Ejecución de procedimientos almacenados n Ejecución de un procedimiento almacenado por separado EXEC Overdue. Orders n Ejecución de un procedimiento almacenado en una instrucción INSERT INTO Customers EXEC Employee. Customer

Alteración y eliminación de procedimientos almacenados n Modificación de procedimientos almacenados l Incluya cualquiera de las opciones en ALTER PROCEDURE l No afecta a los procedimientos almacenados anidados USE Northwind GO ALTER PROC dbo. Overdue. Orders AS SELECT CONVERT(char(8), Required. Date, 1) Required. Date, CONVERT(char(8), Order. Date, 1) Order. Date, Order. ID, Customer. ID, Employee. ID FROM Orders WHERE Required. Date < GETDATE() AND Shipped. Date IS Null ORDER BY Required. Date GO l Eliminación de procedimientos almacenados l Ejecute el procedimiento almacenado sp_depends para determinar si los objetos dependen del procedimiento almacenado

Utilización de parámetros de entrada n Valide primero todos los valores de los parámetros de entrada n Proporcione los valores predeterminados apropiados e incluya las comprobaciones de Null CREATE PROCEDURE dbo. [Year to Year Sales] @Beginning. Date. Time, @Ending. Date. Time AS IF @Beginning. Date IS NULL OR @Ending. Date IS NULL BEGIN RAISERROR('NULL values are not allowed', 14, 1) RETURN END SELECT O. Shipped. Date, O. Order. ID, OS. Subtotal, DATENAME(yy, Shipped. Date) AS Year FROM ORDERS O INNER JOIN [Order Subtotals] OS ON O. Order. ID = OS. Order. ID WHERE O. Shipped. Date BETWEEN @Beginning. Date AND @Ending. Date GO

Ejecución de procedimientos almacenados con parámetros de entrada n Paso de valores por el nombre del parámetro EXEC Add. Customer @Customer. ID = 'ALFKI', @Contact. Name = 'Maria Anders', @Company. Name = 'Alfreds Futterkiste', @Contact. Title = 'Sales Representative', @Address = 'Obere Str. 57', @City = 'Berlin', @Postal. Code = '12209', @Country = 'Germany', @Phone = '030 -0074321' n Paso de valores por posición EXEC Add. Customer 'ALFKI 2', 'Alfreds Futterkiste', 'Maria Anders', 'Sales Representative', 'Obere Str. 57', 'Berlin', NULL, '12209', 'Germany', '030 -0074321'

Devolución de valores mediante parámetros de salida Creación del procedimiento almacenado Ejecución del procedimiento almacenado Resultados del procedimiento almacenado CREATE PROCEDURE dbo. mathtutor @m 1 smallint, @m 2 smallint, @result smallint OUTPUT AS SET @result = @m 1* @m 2 GO DECLARE @answer smallint EXECUTE mathtutor 5, 6, @answer OUTPUT SELECT 'The result is: ' , @answer The result is: 30

Volver a compilar explícitamente procedimientos almacenados n n Volver a compilar cuando l El procedimiento almacenado devuelve conjuntos de resultados que varían considerablemente l Se agrega un nuevo índice a una tabla subyacente l El valor del parámetro es atípico Volver a compilar mediante l CREATE PROCEDURE [WITH RECOMPILE] l EXECUTE [WITH RECOMPILE] l sp_recompile

Ejecución de procedimientos almacenados extendidos n Se programan con la API Servicios abiertos de datos n Pueden incluir características de C y C++ n Pueden contener múltiples funciones n Se pueden llamar desde un cliente o desde SQL Server n Se pueden agregar sólo a la base de datos master EXEC master. . xp_cmdshell 'dir c: '

Control de mensajes de error n La instrucción RETURN sale incondicionalmente de una consulta o procedimiento n sp_addmessage crea mensajes de error personalizados n @@error contiene el número de error de la instrucción ejecutada más recientemente n Instrucción RAISERROR l Devuelve un mensaje de error del sistema definido por el usuario l Establece un indicador del sistema para registrar un error

Consideraciones acerca del rendimiento n n Monitor de sistema de Windows 2000 l Objeto: SQL Server: Administrador de caché l Objeto: Estadísticas de SQL Analizador de SQL l Puede supervisar eventos l Puede probar cada instrucción en un procedimiento almacenado