Procedimientos almacenados (motor de base de datos)Stored Procedures (Database Engine)

En este tema se aplica a: SíSQL ServerSíbase de datos de SQL AzureSíalmacenamiento de datos de SQL Azure Sí Almacenamiento de datos paralelosTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Un procedimiento almacenado de SQL ServerSQL Server es un grupo de una o varias instrucciones Transact-SQLTransact-SQL o una referencia a un método de Common Runtime Language (CLR) de MicrosoftMicrosoft .NET Framework.NET Framework .A stored procedure in SQL ServerSQL Server is a group of one or more Transact-SQLTransact-SQL statements or a reference to a MicrosoftMicrosoft .NET Framework.NET Framework common runtime language (CLR) method. Los procedimientos se asemejan a las construcciones de otros lenguajes de programación, porque pueden:Procedures resemble constructs in other programming languages because they can:

  • Aceptar parámetros de entrada y devolver varios valores en forma de parámetros de salida al programa que realiza la llamada.Accept input parameters and return multiple values in the form of output parameters to the calling program.

  • Contener instrucciones de programación que realicen operaciones en la base de datos.Contain programming statements that perform operations in the database. Entre otras, pueden contener llamadas a otros procedimientos.These include calling other procedures.

  • Devolver un valor de estado a un programa que realiza una llamada para indicar si la operación se ha realizado correctamente o se han producido errores, y el motivo de estos.Return a status value to a calling program to indicate success or failure (and the reason for failure).

Ventajas de usar procedimientos almacenadosBenefits of Using Stored Procedures

En la siguiente lista se describen algunas de las ventajas que brinda el uso de procedimientos.The following list describes some benefits of using procedures.

Tráfico de red reducido entre el cliente y el servidorReduced server/client network traffic
Los comandos de un procedimiento se ejecutan en un único lote de código.The commands in a procedure are executed as a single batch of code. 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.This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network. Sin la encapsulación de código que proporciona un procedimiento, cada una de las líneas de código tendría que enviarse a través de la red.Without the code encapsulation provided by a procedure, every individual line of code would have to cross the network.

Mayor seguridadStronger security
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.Multiple users and client programs can perform operations on underlying database objects through a procedure, even if the users and programs do not have direct permissions on those underlying objects. El procedimiento controla qué procesos y actividades se llevan a cabo y protege los objetos de base de datos subyacentes.The procedure controls what processes and activities are performed and protects the underlying database objects. Esto elimina la necesidad de conceder permisos en cada nivel de objetos y simplifica los niveles de seguridad.This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers.

La cláusula EXECUTE AS puede especificarse en la instrucción CREATE PROCEDURE para habilitar la suplantación de otro usuario o para permitir que los usuarios o las aplicaciones puedan realizar ciertas actividades en la base de datos sin necesidad de contar con permisos directos sobre los objetos y comandos subyacentes.The EXECUTE AS clause can be specified in the CREATE PROCEDURE statement to enable impersonating another user, or enable users or applications to perform certain database activities without needing direct permissions on the underlying objects and commands. Por ejemplo, algunas acciones como TRUNCATE TABLE no tienen permisos que se puedan conceder.For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. Para poder ejecutar TRUNCATE TABLE, el usuario debe tener permisos ALTER en la tabla especificada.To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table. Puede que la concesión de permisos ALTER a un usuario en una tabla no sea lo ideal, pues en realidad el usuario tendrá permisos muy superiores a la posibilidad de truncar una tabla.Granting a user ALTER permissions on a table may not be ideal because the user will effectively have permissions well beyond the ability to truncate a table. Si se incorpora la instrucción TRUNCATE TABLE en un módulo y se especifica la ejecución del módulo como un usuario con permisos para modificar la tabla, se pueden ampliar los permisos para truncar la tabla al usuario al que se concedan permisos EXECUTE para el módulo.By incorporating the TRUNCATE TABLE statement in a module and specifying that module execute as a user who has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the module.

Al llamar a un procedimiento a través de la red, solo está visible la llamada que va a ejecutar el procedimiento.When calling a procedure over the network, only the call to execute the procedure is visible. Por lo tanto, los usuarios malintencionados no pueden ver los nombres de los objetos de base de datos y tabla, incrustados en sus propias instrucciones Transact-SQLTransact-SQL , ni buscar datos críticos.Therefore, malicious users cannot see table and database object names, embed Transact-SQLTransact-SQL statements of their own, or search for critical data.

El uso de parámetros de procedimientos ayuda a protegerse contra ataques por inyección de código SQL.Using procedure parameters helps guard against SQL injection attacks. Dado que la entrada de parámetros se trata como un valor literal y no como código ejecutable, resulta más difícil para un atacante insertar un comando en la instrucción Transact-SQLTransact-SQL del procedimiento y comprometer la seguridad.Since parameter input is treated as a literal value and not as executable code, it is more difficult for an attacker to insert a command into the Transact-SQLTransact-SQL statement(s) inside the procedure and compromise security.

Los procedimientos pueden cifrarse, lo que ayuda a ofuscar el código fuente.Procedures can be encrypted, helping to obfuscate the source code. Para más información, consulte SQL Server Encryption.For more information, see SQL Server Encryption.

Reutilización del códigoReuse of code
El código de cualquier operación de base de datos redundante resulta un candidato perfecto para la encapsulación de procedimientos.The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. De este modo, se elimina la necesidad de escribir de nuevo el mismo código, se reducen las inconsistencias de código y se permite que cualquier usuario o aplicación que cuente con los permisos necesarios pueda acceder al código y ejecutarlo.This eliminates needless rewrites of the same code, decreases code inconsistency, and allows the code to be accessed and executed by any user or application possessing the necessary permissions.

Mantenimiento más sencilloEasier maintenance
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.When client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. El nivel de aplicación permanece independiente y no tiene que tener conocimiento sobre los cambios realizados en los diseños, las relaciones o los procesos de la base de datos.The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.

Rendimiento mejoradoImproved performance
De forma predeterminada, un procedimiento se compila la primera vez que se ejecuta y crea un plan de ejecución que vuelve a usarse en posteriores ejecuciones.By default, a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Como el procesador de consultas no tiene que crear un nuevo plan, normalmente necesita menos tiempo para procesar el procedimiento.Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.

Si ha habido cambios importantes en las tablas o datos a los que se hace referencia en el procedimiento, el plan precompilado podría hacer que el procedimiento se ejecutara con mayor lentitud.If there has been significant change to the tables or data referenced by the procedure, the precompiled plan may actually cause the procedure to perform slower. En este caso, volver a crear el procedimiento y forzar un nuevo plan de ejecución puede mejorar el rendimiento.In this case, recompiling the procedure and forcing a new execution plan can improve performance.

Tipos de procedimientos almacenadosTypes of Stored Procedures

Definidos por el usuarioUser-defined
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 en la base de datos Resource .A user-defined procedure can be created in a user-defined database or in all system databases except the Resource database. El procedimiento se puede desarrollar en Transact-SQLTransact-SQL o como una referencia a un método de Common Runtime Language (CLR) de MicrosoftMicrosoft .NET Framework.NET Framework .The procedure can be developed in either Transact-SQLTransact-SQL or as a reference to a MicrosoftMicrosoft .NET Framework.NET Framework common runtime language (CLR) method.

TemporalesTemporary
Los procedimientos temporales son una forma de procedimientos definidos por el usuario.Temporary procedures are a form of user-defined procedures. Los procedimientos temporales son iguales que los procedimientos permanentes salvo porque se almacenan en tempdb.The temporary procedures are like a permanent procedure, except temporary procedures are stored in tempdb. Hay dos tipos de procedimientos temporales: locales y globales.There are two types of temporary procedures: local and global. Se diferencian entre sí por los nombres, la visibilidad y la disponibilidad.They differ from each other in their names, their visibility, and their availability. Los procedimientos temporales locales tienen como primer carácter de sus nombres un solo signo de número (#); solo son visibles en la conexión actual del usuario y se eliminan cuando se cierra la conexión.Local temporary procedures have a single number sign (#) as the first character of their names; they are visible only to the current user connection, and they are deleted when the connection is closed. Los procedimientos temporales globales presentan dos signos de número (##) antes del nombre; son visibles para cualquier usuario después de su creación y se eliminan al final de la última sesión en la que se usa el procedimiento.Global temporary procedures have two number signs (##) as the first two characters of their names; they are visible to any user after they are created, and they are deleted at the end of the last session using the procedure.

SistemaSystem
Los procedimientos del sistema se incluyen con SQL ServerSQL Server.System procedures are included with SQL ServerSQL Server. Están almacenados físicamente en la base de datos interna y oculta Resource y se muestran de forma lógica en el esquema sys de cada base de datos definida por el sistema y por el usuario.They are physically stored in the internal, hidden Resource database and logically appear in the sys schema of every system- and user-defined database. 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.In addition, the msdb database also contains system stored procedures in the dbo schema that are used for scheduling alerts and jobs. Dado que los procedimientos del sistema empiezan con el prefijo sp_, le recomendamos que no use este prefijo cuando asigne un nombre a los procedimientos definidos por el usuario.Because system procedures start with the prefix sp_, we recommend that you do not use this prefix when naming user-defined procedures. Para obtener una lista completa de los procedimientos del sistema, vea Procedimientos almacenados del sistema (Transact-SQL)For a complete list of system procedures, see System Stored Procedures (Transact-SQL)

SQL ServerSQL Server admite los procedimientos del sistema que proporcionan una interfaz de SQL ServerSQL Server a los programas externos para varias actividades de mantenimiento. supports the system procedures that provide an interface from SQL ServerSQL Server to external programs for various maintenance activities. Estos procedimientos extendidos usan el prefijo xp_.These extended procedures use the xp_ prefix. Para obtener una lista completa de los procedimientos extendidos, vea Procedimientos almacenados extendidos generales (Transact-SQL).For a complete list of extended procedures, see General Extended Stored Procedures (Transact-SQL).

Extendidos definidos por el usuarioExtended User-Defined
Los procedimientos extendidos le permiten crear sus propias rutinas externas en un lenguaje de programación como puede ser C. Estos procedimientos son DLL que una instancia de SQL ServerSQL Server puede cargar y ejecutar dinámicamente.Extended procedures enable creating external routines in a programming language such as C. These procedures are DLLs that an instance of SQL ServerSQL Server can dynamically load and run.

Nota

Los procedimientos almacenados extendidos se quitarán en una versión futura de SQL ServerSQL Server.Extended stored procedures will be removed in a future version of SQL ServerSQL Server. No utilice esta característica en nuevos trabajos de desarrollo y modifique lo antes posible las aplicaciones que actualmente la utilizan.Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Cree en su lugar procedimientos CLR.Create CLR procedures instead. Este método constituye una alternativa más consolidada y segura para escribir procedimientos extendidos.This method provides a more robust and secure alternative to writing extended procedures.

Descripción de la tareaTask Description TemaTopic
Describe cómo se crea un procedimiento almacenado.Describes how to create a stored procedure. Crear un procedimiento almacenadoCreate a Stored Procedure
Describe cómo se modifica un procedimiento almacenado.Describes how to modify a stored procedure. Modificar un procedimiento almacenadoModify a Stored Procedure
Describe cómo se elimina un procedimiento almacenado.Describes how to delete a stored procedure. Eliminar un procedimiento almacenadoDelete a Stored Procedure
Describe cómo se ejecuta un procedimiento almacenado.Describes how to execute a stored procedure. Ejecutar un procedimiento almacenadoExecute a Stored Procedure
Describe cómo se conceden permisos en un procedimiento almacenado.Describes how to grant permissions on a stored procedure. Conceder permisos para un procedimiento almacenadoGrant Permissions on a Stored Procedure
Describe cómo se devuelven datos de un procedimiento almacenado a una aplicación.Describes how to return data from a stored procedure to an application. Devolver datos de un procedimiento almacenadoReturn Data from a Stored Procedure
Describe cómo se recompila un procedimiento almacenado.Describes how to recompile a stored procedure. Volver a compilar un procedimiento almacenadoRecompile a Stored Procedure
Describe cómo se cambia el nombre de un procedimiento almacenado.Describes how to rename a stored procedure. Cambiar el nombre de un procedimiento almacenadoRename a Stored Procedure
Describe cómo se consulta la definición de un procedimiento almacenado.Describes how to view the definition of a stored procedure. Ver la definición de un procedimiento almacenadoView the Definition of a Stored Procedure
Describe cómo se consultan las dependencias de un procedimiento almacenado.Describes how to view the dependencies on a stored procedure. Ver las dependencias de un procedimiento almacenadoView the Dependencies of a Stored Procedure
Describe cómo se usan los parámetros en un procedimiento almacenado.Describes how Parameters are used in a stored procedure. ParámetrosParameters

Procedimientos almacenados de CLRCLR Stored Procedures