Procedimientos almacenados (motor de base de datos)

Un procedimiento almacenado en SQL Server es un grupo de una o varias instrucciones Transact-SQL o una referencia a un método de Common Runtime Language (CLR) de Microsoft .NET Framework. Los procedimientos se asemejan a las construcciones de otros lenguajes de programación porque pueden:

• Acepte parámetros de entrada y devuelva varios valores en forma de parámetros de salida al programa de llamada.

• Contienen instrucciones de programación que realizan operaciones en la base de datos. Esto incluye la llamada a otros procedimientos.

• Devuelve un valor de estado a un programa de llamada para indicar éxito o error (y el motivo del error).

Ventajas del uso de procedimientos almacenados

En la lista siguiente se describen algunas ventajas del uso de procedimientos.

Tráfico de red de servidor/cliente reducido
Los comandos de un procedimiento se ejecutan en un único lote de código. Esto puede reducir significativamente el tráfico de red entre el servidor y el cliente porque únicamente se envía a través de la red la llamada que va a ejecutar el procedimiento. Sin la encapsulación de código proporcionada por un procedimiento, cada línea de código individual tendría que cruzar la red.

Mayor seguridad
Varios usuarios y programas cliente pueden realizar operaciones en los objetos de base de datos subyacentes a través de un procedimiento, aunque los usuarios y los programas no tengan permisos directos sobre esos objetos subyacentes. El procedimiento controla qué procesos y actividades se llevan a cabo y protege los objetos de base de datos subyacentes. Esto elimina la necesidad de conceder permisos en cada nivel de objetos y simplifica los niveles de seguridad.

La cláusula EXECUTE AS se puede especificar en la instrucción CREATE PROCEDURE para habilitar la suplantación de otro usuario o permitir que los usuarios o aplicaciones realicen determinadas actividades de base de datos sin necesidad de permisos directos en los objetos y comandos subyacentes. Por ejemplo, algunas acciones, como TRUNCATE TABLE, no tienen permisos concedidos. Para ejecutar TRUNCATE TABLE, el usuario debe tener permisos ALTER en la tabla especificada. La concesión de permisos ALTER a un usuario en una tabla puede no ser ideal porque el usuario tendrá permisos que exceden la capacidad de truncar una tabla. Al incorporar la instrucción TRUNCATE TABLE en un módulo y especificar que el módulo se ejecute como un usuario que tenga permisos para modificar la tabla, puede ampliar los permisos para truncar la tabla al usuario que concede permisos EXECUTE en el módulo.

Al llamar a un procedimiento a través de la red, solo está visible la llamada para ejecutar el procedimiento. Por lo tanto, los usuarios malintencionados no pueden ver los nombres de tablas y objetos de base de datos, incluir sus propias instrucciones Transact-SQL o buscar datos críticos.

El uso de parámetros de procedimiento ayuda a protegerse contra ataques por inyección de CÓDIGO SQL. Dado que la entrada de parámetro se trata como un valor literal y no como código ejecutable, es más difícil que un atacante inserte un comando en las instrucciones Transact-SQL dentro del procedimiento y ponga en peligro la seguridad.

Los procedimientos se pueden cifrar, lo que ayuda a ofuscar el código fuente. Para obtener más información, vea Cifrado de SQL Server.

Reutilización del código
El código de cualquier operación de base de datos repetitiva es el candidato perfecto para la encapsulación en procedimientos. Esto elimina las reescrituras innecesarias del mismo código, reduce la incoherencia de código y permite que cualquier usuario o aplicación tenga los permisos necesarios para acceder al código y ejecutarlo.

Mantenimiento más sencillo
Cuando las aplicaciones cliente llaman a procedimientos y mantienen las operaciones de base de datos en la capa de datos, solo deben actualizarse los cambios de los procesos en la base de datos subyacente. El nivel de aplicación sigue siendo independiente y no tiene que saber cómo se producen cambios en los diseños de base de datos, las relaciones o los procesos.

Rendimiento mejorado
De forma predeterminada, un procedimiento compila la primera vez que se ejecuta y crea un plan de ejecución que se reutiliza para las ejecuciones posteriores. Dado que el procesador de consultas no tiene que crear un nuevo plan, normalmente tarda menos tiempo en procesar el procedimiento.

Si se ha producido un cambio significativo en las tablas o los datos a los que hace referencia el procedimiento, el plan precompilado puede causar que el procedimiento se ejecute más lentamente. En este caso, volver a compilar el procedimiento y forzar un nuevo plan de ejecución puede mejorar el rendimiento.

Tipos de procedimientos almacenados

Definidas por el usuario
Un procedimiento definido por el usuario se puede crear en una base de datos definida por el usuario o en todas las bases de datos del sistema, excepto la base de datos de recursos . El procedimiento se puede desarrollar en Transact-SQL o como referencia a un método de Common Runtime Language (CLR) de Microsoft .NET Framework.

Temporales
Los procedimientos temporales son una forma de procedimientos definidos por el usuario. Los procedimientos temporales son como un procedimiento permanente, excepto los procedimientos temporales se almacenan en tempdb. Hay dos tipos de procedimientos temporales: locales y globales. Se diferencian entre sí por los nombres, la visibilidad y la disponibilidad. Los procedimientos temporales locales tienen un signo de número único (#) como primer carácter de sus nombres; solo son visibles para la conexión de usuario actual y se eliminan cuando se cierra la conexión. Los procedimientos temporales globales tienen dos signos numéricos (##) como los dos primeros caracteres de sus nombres; son visibles para cualquier usuario después de crearlos y se eliminan al final de la última sesión mediante el procedimiento .

Sistema
Los procedimientos del sistema se incluyen con SQL Server. Se almacenan físicamente en la base de datos de recursos interna y oculta y aparecen lógicamente en el esquema sys de cada base de datos definida por el usuario y del sistema. Además, la base de datos msdb también contiene procedimientos almacenados del sistema en el esquema dbo que se usan para programar alertas y trabajos. Dado que los procedimientos del sistema comienzan con el prefijo sp_, se recomienda no usar este prefijo al asignar nombres a procedimientos definidos por el usuario. Para obtener una lista completa de los procedimientos del sistema, vea Procedimientos almacenados del sistema (Transact-SQL)

SQL Server admite los procedimientos del sistema que proporcionan una interfaz de SQL Server a programas externos para diversas actividades de mantenimiento. Estos procedimientos extendidos usan el prefijo xp_. Para obtener una lista completa de los procedimientos extendidos, vea Procedimientos almacenados extendidos generales (Transact-SQL).

User-Defined extendidas
Los procedimientos extendidos permiten crear rutinas externas en un lenguaje de programación como C. Estos procedimientos son archivos DLL que una instancia de SQL Server puede cargar y ejecutar dinámicamente.

Nota:

Los procedimientos almacenados extendidos se quitarán en una versión futura de SQL Server. No utilice esta característica en nuevos trabajos de desarrollo y modifique lo antes posible las aplicaciones que actualmente la utilizan. En su lugar, cree procedimientos CLR. Este método proporciona una alternativa más sólida y segura a la escritura de procedimientos extendidos.

Tareas relacionadas

Descripción de la tarea	tema
Describe cómo crear un procedimiento almacenado.	Crear un procedimiento almacenado
Describe cómo modificar un procedimiento almacenado.	Modificar un procedimiento almacenado
Describe cómo eliminar un procedimiento almacenado.	Eliminar un procedimiento almacenado
Describe cómo ejecutar un procedimiento almacenado.	Ejecutar un procedimiento almacenado
Describe cómo conceder permisos en un procedimiento almacenado.	Concesión de permisos en un procedimiento almacenado
Describe cómo devolver datos de un procedimiento almacenado a una aplicación.	Devolver datos de un procedimiento almacenado
Describe cómo volver a compilar un procedimiento almacenado.	Volver a compilar un procedimiento almacenado
Describe cómo cambiar el nombre de un procedimiento almacenado.	Cambiar el nombre de un procedimiento almacenado
Describe cómo ver la definición de un procedimiento almacenado.	Ver la definición de un procedimiento almacenado
Describe cómo ver las dependencias en un procedimiento almacenado.	Ver las dependencias de un procedimiento almacenado

Contenido relacionado

Procedimientos almacenados CLR