CREATE PROCEDURE (Transact-SQL)CREATE PROCEDURE (Transact-SQL)

ESTE TEMA SE APLICA A: síSQL Server (a partir de 2008)síAzure SQL DatabasesíAzure SQL Data Warehouse síAlmacenamiento de datos paralelos THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Crea un procedimiento almacenado de Transact-SQLTransact-SQL o Common Language Runtime (CLR) en SQL ServerSQL Server, Base de datos SQL de AzureAzure SQL Database, Azure SQL Data Warehouse y Almacenamiento de datos paralelos.Creates a Transact-SQLTransact-SQL or common language runtime (CLR) stored procedure in SQL ServerSQL Server, Base de datos SQL de AzureAzure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse. Los procedimientos almacenados son similares a los procedimientos de otros lenguajes de programación en tanto que pueden:Stored procedures are similar to procedures in other programming languages in that they can:

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

  • Contener instrucciones de programación que realicen operaciones en la base de datos, incluidas las llamadas a otros procedimientos.Contain programming statements that perform operations in the database, including calling other procedures.

  • Devolver un valor de estado a un lote o a un procedimiento que realice 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 procedure or batch to indicate success or failure (and the reason for failure).

    Use esta instrucción para crear un procedimiento permanente en la base de datos actual o un procedimiento temporal en la base de datos tempdb.Use this statement to create a permanent procedure in the current database or a temporary procedure in the tempdb database.

Nota

La integración de CLR de .NET Framework en SQL Server se describe en este tema.The integration of .NET Framework CLR into SQL Server is discussed in this topic. La integración de CLR no se aplica a Azure SQL DatabaseSQL Database.CLR integration does not apply to Azure SQL DatabaseSQL Database.

Vaya directamente a Ejemplos sencillos para omitir los detalles de la sintaxis y ver un ejemplo rápido de procedimiento almacenado básico.Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure.

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

-- Transact-SQL Syntax for Stored Procedures in SQL Server and Azure SQL Database  

CREATE [ OR ALTER ] { PROC | PROCEDURE } 
    [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]  
    ] [ ,...n ]   
[ WITH <procedure_option> [ ,...n ] ]  
[ FOR REPLICATION ]   
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }  
[;]  

<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE AS Clause ]  
-- Transact-SQL Syntax for CLR Stored Procedures  

CREATE [ OR ALTER ] { PROC | PROCEDURE } 
    [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ type_schema_name. ] data_type }   
        [ = default ] [ OUT | OUTPUT ] [READONLY]  
    ] [ ,...n ]   
[ WITH EXECUTE AS Clause ]  
AS { EXTERNAL NAME assembly_name.class_name.method_name }  
[;]  
-- Transact-SQL Syntax for Natively Compiled Stored Procedures  

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name  
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ] 
        [ OUT | OUTPUT ] [READONLY] 
    ] [ ,... n ]  
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]  
AS  
{  
  BEGIN ATOMIC WITH (set_option [ ,... n ] )  
sql_statement [;] [ ... n ]  
 [ END ]  
}  
 [;]  

<set_option> ::=  
    LANGUAGE =  [ N ] 'language'  
  | TRANSACTION ISOLATION LEVEL =  { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }  
  | [ DATEFIRST = number ]  
  | [ DATEFORMAT = format ]  
  | [ DELAYED_DURABILITY = { OFF | ON } ]  
-- Transact-SQL Syntax for Stored Procedures in Azure SQL Data Warehouse
-- and Parallel Data Warehouse  

-- Create a stored procedure   
CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name  
    [ { @parameterdata_type } [ OUT | OUTPUT ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [;][ ,...n ] [ END ] }  
[;]  

ArgumentosArguments

OR ALTEROR ALTER
Se aplica a: Azure SQL DatabaseSQL Database, SQL ServerSQL Server (a partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).Applies to: Azure SQL DatabaseSQL Database, SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).

Modifica el procedimiento si es que ya existe.Alters the procedure if it already exists.

schema_nameschema_name
El nombre del esquema al que pertenece el procedimiento.The name of the schema to which the procedure belongs. Los procedimientos se enlazan a un esquema.Procedures are schema-bound. Si no se especifica el nombre del esquema cuando se crea el procedimiento, se asigna automáticamente el esquema predeterminado del usuario que crea este procedimiento.If a schema name is not specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned.

procedure_nameprocedure_name
El nombre del procedimiento.The name of the procedure. Los nombres de los procedimientos deben cumplir las reglas de los identificadores y deben ser exclusivos en el esquema.Procedure names must comply with the rules for identifiers and must be unique within the schema.

Evite el uso del prefijo sp_ cuando asigne nombre a los procedimientos.Avoid the use of the sp_ prefix when naming procedures. SQL ServerSQL Server usa este prefijo para designar los procedimientos del sistema.This prefix is used by SQL ServerSQL Server to designate system procedures. Si usa el prefijo, puede provocar la ruptura del código de la aplicación si existe un procedimiento del sistema con el mismo nombre.Using the prefix can cause application code to break if there is a system procedure with the same name.

Los procedimientos temporales locales o globales se pueden crear anteponiendo un signo de número (#) al parámetro procedure_name (#procedure_name) para los procedimientos temporales locales y dos signos de número (##procedure_name) para los procedimientos temporales globales.Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). Solo la conexión que creó un procedimiento temporal local lo ve y se quita cuando se cierra esa conexión.A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. Un procedimiento temporal global está disponible para todas las conexiones y se quita al final de la última sesión que lo use.A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. No se pueden especificar nombres temporales para los procedimientos CLR.Temporary names cannot be specified for CLR procedures.

El nombre completo de un procedimiento o un procedimiento temporal global, incluidos los signos de número ##, no puede superar los 128 caracteres.The complete name for a procedure or a global temporary procedure, including ##, cannot exceed 128 characters. El nombre completo de un procedimiento temporal local, incluido el signo de número #, no puede superar los 116 caracteres.The complete name for a local temporary procedure, including #, cannot exceed 116 characters.

; number; number
Se aplica a: SQL Server 2008SQL Server 2008 a SQL Server 2017SQL Server 2017 y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Base de datos SQL de AzureAzure SQL Database.

Entero opcional que se usa para agrupar procedimientos con el mismo nombre.An optional integer that is used to group procedures of the same name. Estos procedimientos agrupados se pueden quitar juntos mediante una instrucción DROP PROCEDURE.These grouped procedures can be dropped together by using one DROP PROCEDURE statement.

Nota

Esta característica está en modo de mantenimiento y es posible que no se incluya en una versión futura de Microsoft SQL Server.This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Los procedimientos numerados no pueden usar xml ni los tipos definidos por el usuario CLR. Tampoco se pueden usar en una guía de plan.Numbered procedures cannot use the xml or CLR user-defined types and cannot be used in a plan guide.

@ parameter@ parameter
Parámetro declarado en el procedimiento.A parameter declared in the procedure. Especifique un nombre de parámetro usando una arroba (@) como el primer carácter.Specify a parameter name by using the at sign (@) as the first character. El nombre del parámetro debe cumplir las mismas reglas para identifiers.The parameter name must comply with the rules for identifiers. Los parámetros son locales respecto al procedimiento; los mismos nombres de parámetro se pueden usar en otros procedimientos.Parameters are local to the procedure; the same parameter names can be used in other procedures.

Se pueden declarar uno o varios parámetros; el valor máximo es 2.100.One or more parameters can be declared; the maximum is 2,100. El usuario debe proporcionar el valor de cada parámetro declarado cuando se llame al procedimiento, a menos que se haya definido un valor predeterminado para el parámetro o se haya establecido en el mismo valor que el de otro parámetro.The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter. Si un procedimiento contiene parámetros con valores de tabla y el parámetro no está en la llamada, se pasa una tabla vacía.If a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in. Los parámetros solo pueden ocupar el lugar de expresiones constantes; no se pueden usar en lugar de nombres de tablas, nombres de columnas o nombres de otros objetos de base de datos.Parameters can take the place only of constant expressions; they cannot be used instead of table names, column names, or the names of other database objects. Para obtener más información, vea EXECUTE (Transact-SQL).For more information, see EXECUTE (Transact-SQL).

No se pueden declarar los parámetros si se especifica FOR REPLICATION.Parameters cannot be declared if FOR REPLICATION is specified.

[ type_schema_name. ] data_type[ type_schema_name. ] data_type
El tipo de datos del parámetro y el esquema al que pertenece el tipo de datos.The data type of the parameter and the schema to which the data type belongs.

Directrices para procedimientos de Transact-SQLTransact-SQL:Guidelines for Transact-SQLTransact-SQL procedures:

  • Todos los tipos de datos de Transact-SQLTransact-SQL se pueden usar como parámetros.All Transact-SQLTransact-SQL data types can be used as parameters.

  • Puede usar el tipo de tabla definido por el usuario para crear parámetros con valores de tabla.You can use the user-defined table type to create table-valued parameters. Los parámetros con valores de tabla solo pueden ser parámetros INPUT y deben ir acompañados de la palabra clave READONLY.Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword. Para más información, veaUsar parámetros con valores de tabla (motor de base de datos).For more information, see Use Table-Valued Parameters (Database Engine)

  • Los tipos de datos cursor solo pueden ser parámetros OUTPUT y deben ir acompañados de la palabra clave VARYING.cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.

Directrices para procedimientos CLR:Guidelines for CLR procedures:

  • Todos los tipos de datos de SQL ServerSQL Server nativos con un equivalente en código administrado se pueden usar como parámetros.All of the native SQL ServerSQL Server data types that have an equivalent in managed code can be used as parameters. Para más información sobre la correspondencia entre los tipos CLR y los tipos de datos del sistema de SQL ServerSQL Server, vea Asignar datos de parámetros CLR.For more information about the correspondence between CLR types and SQL ServerSQL Server system data types, see Mapping CLR Parameter Data. Para más información sobre los tipos de datos del sistema de SQL ServerSQL Server y su sintaxis, vea Tipos de datos (Transact-SQL).For more information about SQL ServerSQL Server system data types and their syntax, see Data Types (Transact-SQL).

  • Los tipos de datos cursor o con valores de tabla no se pueden usar como parámetros.Table-valued or cursor data types cannot be used as parameters.

  • Si el tipo de datos del parámetro es un tipo definido por el usuario de CLR, se debe disponer del permiso EXECUTE en el tipo.If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.

VARYINGVARYING
Especifica el conjunto de resultados admitido como parámetro de salida.Specifies the result set supported as an output parameter. Este parámetro lo crea de forma dinámica el procedimiento y su contenido puede variar.This parameter is dynamically constructed by the procedure and its contents may vary. Solo se aplica a los parámetros cursor.Applies only to cursor parameters. Esta opción no es válida para los procedimientos CLR.This option is not valid for CLR procedures.

defaultdefault
Valor predeterminado de un parámetro.A default value for a parameter. Si se define un valor predeterminado para un parámetro, el procedimiento se puede ejecutar sin especificar ningún valor para ese parámetro.If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. El valor predeterminado debe ser una constante o puede ser NULL.The default value must be a constant or it can be NULL. El valor constante puede tener el formato de un carácter comodín, lo que permite usar la palabra clave LIKE cuando se pase el parámetro al procedimiento.The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.

Los valores predeterminados solo se registran en la columna sys.parameters.default de los procedimientos CLR.Default values are recorded in the sys.parameters.default column only for CLR procedures. Esa columna es NULL para los parámetros de procedimientos de Transact-SQLTransact-SQL.That column is NULL for Transact-SQLTransact-SQL procedure parameters.

OUT | OUTPUTOUT | OUTPUT
Indica que se trata de un parámetro de salida.Indicates that the parameter is an output parameter. Utilice los parámetros OUTPUT para devolver valores al autor de la llamada del procedimiento.Use OUTPUT parameters to return values to the caller of the procedure. Los parámetros text, ntext e image no se pueden usar como parámetros OUTPUT, a menos que se trate de un procedimiento de CLR.text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. Un parámetro de salida puede ser un marcador de posición de cursor, a menos que el procedimiento sea un procedimiento CLR.An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. Un tipo de datos con valores de tabla no se puede especificar como parámetro OUTPUT de un procedimiento.A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

READONLYREADONLY
Indica que el parámetro no se puede actualizar ni modificar dentro del cuerpo del procedimiento.Indicates that the parameter cannot be updated or modified within the body of the procedure. Si el tipo de parámetro es un tipo con valores de tabla, se debe especificar READONLY.If the parameter type is a table-value type, READONLY must be specified.

RECOMPILERECOMPILE
Indica que Motor de base de datosDatabase Engine no almacena en caché ningún plan de consulta para este procedimiento, forzándolo a ser compilado cada vez que se ejecute.Indicates that the Motor de base de datosDatabase Engine does not cache a query plan for this procedure, forcing it to be compiled each time it is executed. Para más información sobre las razones para forzar una nueva compilación, vea Volver a compilar un procedimiento almacenado.For more information regarding the reasons for forcing a recompile, see Recompile a Stored Procedure. Esta opción no se puede usar cuando se especifica FOR REPLICATION ni para procedimientos CLR.This option cannot be used when FOR REPLICATION is specified or for CLR procedures.

Para indicar a Motor de base de datosDatabase Engine que descarte planes de consulta para consultas individuales en un procedimiento, use la sugerencia de consulta RECOMPILE en la definición de la consulta.To instruct the Motor de base de datosDatabase Engine to discard query plans for individual queries inside a procedure, use the RECOMPILE query hint in the definition of the query. Para obtener más información, vea Sugerencias de consulta (Transact-SQL).For more information, see Query Hints (Transact-SQL).

ENCRYPTIONENCRYPTION
Se aplica a: SQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017), Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Base de datos SQL de AzureAzure SQL Database.

Indica que SQL ServerSQL Server convierte el texto original de la instrucción CREATE PROCEDURE en un formato confuso.Indicates that SQL ServerSQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. La salida de la protección no es directamente visible en ninguna de las vistas de catálogo de SQL ServerSQL Server.The output of the obfuscation is not directly visible in any of the catalog views in SQL ServerSQL Server. Los usuarios que no dispongan de acceso a las tablas del sistema o a los archivos de base de datos no pueden recuperar el texto confuso.Users who have no access to system tables or database files cannot retrieve the obfuscated text. Sin embargo, estará disponible para los usuarios con privilegios que puedan acceder a las tablas del sistema a través del puerto DAC o que puedan acceder directamente a los archivos de base de datos.However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files. Además, los usuarios que puedan adjuntar un depurador al proceso del servidor pueden recuperar el procedimiento descifrado de la memoria en tiempo de ejecución.Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. Para más información sobre cómo tener acceso al sistema, vea Configuración de visibilidad de los metadatos.For more information about accessing system metadata, see Metadata Visibility Configuration.

Esta opción no es válida para los procedimientos CLR.This option is not valid for CLR procedures.

Los procedimientos creados mediante esta opción no se pueden publicar como parte de la replicación de SQL ServerSQL Server.Procedures created with this option cannot be published as part of SQL ServerSQL Server replication.

Cláusula EXECUTE ASEXECUTE AS clause
Especifica el contexto de seguridad en el que se ejecuta el procedimiento.Specifies the security context under which to execute the procedure.

Para los procedimientos almacenados compilados de forma nativa, a partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x) y en Base de datos SQL de AzureAzure SQL Database, no hay limitaciones en la cláusula EXECUTE AS.For natively compiled stored procedures, starting SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Base de datos SQL de AzureAzure SQL Database, there are no limitations on the EXECUTE AS clause. En SQL Server 2014 (12.x)SQL Server 2014 (12.x), las cláusulas SELF, OWNER y 'user_name' son compatibles con procedimientos almacenados compilados de forma nativa.In SQL Server 2014 (12.x)SQL Server 2014 (12.x) the SELF, OWNER, and ‘user_name’ clauses are supported with natively compiled stored procedures.

Para obtener más información, vea EXECUTE AS (cláusula de Transact-SQL).For more information, see EXECUTE AS Clause (Transact-SQL).

FOR REPLICATIONFOR REPLICATION
Se aplica a: SQL Server (desde SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017), Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Base de datos SQL de AzureAzure SQL Database.

Especifica que el procedimiento se crea para replicación.Specifies that the procedure is created for replication. Por consiguiente, no se puede ejecutar en el suscriptor.Consequently, it cannot be executed on the Subscriber. Se usa un procedimiento creado con la opción FOR REPLICATION como filtro de procedimiento y solo se ejecuta durante la replicación.A procedure created with the FOR REPLICATION option is used as a procedure filter and is executed only during replication. No se pueden declarar los parámetros si se especifica FOR REPLICATION.Parameters cannot be declared if FOR REPLICATION is specified. No se puede especificar FOR REPLICATION en los procedimientos CLR.FOR REPLICATION cannot be specified for CLR procedures. La opción RECOMPILE no se tiene en cuenta en el caso de procedimientos creados con FOR REPLICATION.The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

Un procedimiento FOR REPLICATION tiene un tipo de objeto RF en sys.objects y sys.procedures.A FOR REPLICATION procedure has an object type RF in sys.objects and sys.procedures.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
Una o más instrucciones Transact-SQLTransact-SQL que comprenden el cuerpo del procedimiento.One or more Transact-SQLTransact-SQL statements comprising the body of the procedure. Puede usar las palabras clave BEGIN y END opcionales para incluir las instrucciones.You can use the optional BEGIN and END keywords to enclose the statements. Para obtener información, vea las secciones Prácticas recomendadas, Comentarios generales, así como Limitaciones y restricciones que aparecen más adelante.For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

EXTERNAL NAME assembly_name ***.** class_name . method_nameEXTERNAL NAME *assembly_name ***.** class_name . method_name
Se aplica a: de SQL Server 2008SQL Server 2008 a SQL Server 2017SQL Server 2017, SQL DatabaseSQL Database.Applies to*: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL DatabaseSQL Database.

Especifica el método de un ensamblado de .NET Framework.NET Framework para un procedimiento CLR al que se va a hacer referencia.Specifies the method of a .NET Framework.NET Framework assembly for a CLR procedure to reference. class_name debe ser un identificador SQL ServerSQL Server válido y debe existir como clase en el ensamblado.class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly. Si la clase tiene un nombre completo de espacio de nombres que usa un punto (.) para separar las partes del espacio de nombres, el nombre de la clase debe delimitarse mediante paréntesis ([]) o comillas ("").If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). El método especificado debe ser un método estático de la clase.The specified method must be a static method of the class.

De manera predeterminada, SQL ServerSQL Server no puede ejecutar código CLR.By default, SQL ServerSQL Server cannot execute CLR code. Se pueden crear, modificar y quitar objetos de base de datos que hagan referencia a módulos de Common Language Runtime, pero estas referencias no se pueden ejecutar en SQL ServerSQL Server hasta que se habilite la opción clr enabled.You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. Para habilitar esta opción, use sp_configure.To enable the option, use sp_configure.

Nota

Los procedimientos CLR no se admiten en las bases de datos independientes.CLR procedures are not supported in a contained database.

ATOMIC WITHATOMIC WITH
Se aplica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Base de datos SQL de AzureAzure SQL Database.

Indica la ejecución automática de procedimientos almacenados.Indicates atomic stored procedure execution. Los cambios se confirman o todos se revierten iniciando una excepción.Changes are either committed or all of the changes rolled back by throwing an exception. El bloqueo ATOMIC WITH se requiere para los procedimientos almacenados compilados de forma nativa.The ATOMIC WITH block is required for natively compiled stored procedures.

Si el procedimiento vuelve (explícitamente mediante la instrucción RETURN o implícitamente completando su ejecución), el trabajo que realiza el procedimiento se confirma.If the procedure RETURNs (explicitly through the RETURN statement, or implicitly by completing execution), the work performed by the procedure is committed. Si se inicia el procedimiento, el trabajo que realiza se revierte.If the procedure THROWs, the work performed by the procedure is rolled back.

XACT_ABORT es ON de forma predeterminada en un bloque atómico y no se puede cambiar.XACT_ABORT is ON by default inside an atomic block and cannot be changed. XACT_ABORT especifica si SQL ServerSQL Server revierte automáticamente la transacción actual cuando una instrucción Transact-SQLTransact-SQL genera un error en tiempo de ejecución.XACT_ABORT specifies whether SQL ServerSQL Server automatically rolls back the current transaction when a Transact-SQLTransact-SQL statement raises a run-time error.

Las siguientes opciones SET son siempre ON en el bloqueo ATOMIC; las opciones no se pueden cambiar.The following SET options are always ON in the ATOMIC block; the options cannot be changed.

  • CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER, ARITHABORTQUOTED_IDENTIFIER, ARITHABORT
  • NOCOUNTNOCOUNT
  • ANSI_NULLSANSI_NULLS
  • ANSI_WARNINGSANSI_WARNINGS

Las opciones SET no pueden cambiarse dentro de bloques ATOMIC.SET options cannot be changed inside ATOMIC blocks. Las opciones SET de la sesión de usuario no se utilizan en el ámbito de los procedimientos almacenados compilados de forma nativa.The SET options in the user session are not used in the scope of natively compiled stored procedures. Estas opciones se corrigen en tiempo de compilación.These options are fixed at compile time.

Las operaciones BEGIN, ROLLBACK y COMMIT no se pueden utilizar dentro de un bloque atómico.BEGIN, ROLLBACK, and COMMIT operations cannot be used inside an atomic block.

Hay un bloqueo ATOMIC por cada procedimiento almacenado compilado de forma nativa, en el ámbito externo del procedimiento.There is one ATOMIC block per natively compiled stored procedure, at the outer scope of the procedure. Los bloqueos no se pueden anidar.The blocks cannot be nested. Para más información sobre los bloques ATOMIC, vea Procedimientos almacenados compilados de forma nativa.For more information about atomic blocks, see Natively Compiled Stored Procedures.

NULL | NOT NULLNULL | NOT NULL
Determina si se permiten valores NULL en un parámetro.Determines whether null values are allowed in a parameter. NULL es el valor predeterminado.NULL is the default.

NATIVE_COMPILATIONNATIVE_COMPILATION
Se aplica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Base de datos SQL de AzureAzure SQL Database.

Indica que el procedimiento está compilado de forma nativa.Indicates that the procedure is natively compiled. NATIVE_COMPILATION, SCHEMABINDING, y EXECUTE AS se pueden especificar en cualquier orden.NATIVE_COMPILATION, SCHEMABINDING, and EXECUTE AS can be specified in any order. Para más información, vea Procedimientos almacenados compilados de forma nativa.For more information, see Natively Compiled Stored Procedures.

SCHEMABINDINGSCHEMABINDING
Se aplica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Base de datos SQL de AzureAzure SQL Database.

Se garantiza que las tablas a las que un procedimiento hace referencia no se pueden quitar o modificar.Ensures that tables that are referenced by a procedure cannot be dropped or altered. SCHEMABINDING se requiere en los procedimientos almacenados compilados de forma nativaSCHEMABINDING is required in natively compiled stored procedures. (Para más información, vea Procedimientos almacenados compilados de forma nativa). Las restricciones SCHEMABINDING son las mismas que para las funciones definidas por el usuario.(For more information, see Natively Compiled Stored Procedures.) The SCHEMABINDING restrictions are the same as they are for user-defined functions. Para más información, vea la sección SCHEMABINDING en CREATE FUNCTION (Transact-SQL).For more information, see the SCHEMABINDING section in CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'language'LANGUAGE = [N] 'language'
Se aplica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) hasta SQL Server 2017SQL Server 2017 y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Base de datos SQL de AzureAzure SQL Database.

Equivalente a la opción de sesión SET LANGUAGE (Transact-SQL).Equivalent to SET LANGUAGE (Transact-SQL) session option. LANGUAGE = [N] 'language' es necesaria.LANGUAGE = [N] 'language' is required.

NIVEL DE AISLAMIENTO DE TRANSACCIÓNTRANSACTION ISOLATION LEVEL
Se aplica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Base de datos SQL de AzureAzure SQL Database.

Se requiere para los procedimientos almacenados compilados de forma nativa.Required for natively compiled stored procedures. Especifica el nivel de aislamiento de la transacción para el procedimiento almacenado.Specifies the transaction isolation level for the stored procedure. Las opciones son las siguientes:The options are as follows:

Para más información sobre estas opciones, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).For more information about these options, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READREPEATABLE READ
Especifica que las instrucciones no pueden leer datos que otras transacciones hayan modificado, pero aún no hayan confirmado.Specifies that statements cannot read data that has been modified but not yet committed by other transactions. Si otra transacción modifica los datos leídos por la transacción actual, esta dará error.If another transaction modifies data that has been read by the current transaction, the current transaction fails.

SERIALIZABLESERIALIZABLE
Especifica lo siguiente:Specifies the following:

  • Las instrucciones no pueden leer datos que hayan sido modificados, pero aún no confirmados, por otras transacciones.Statements cannot read data that has been modified but not yet committed by other transactions.
  • Si otras transacciones modifican los datos leídos por la transacción actual, esta dará error.If another transactions modifies data that has been read by the current transaction, the current transaction fails.
  • Si otra transacción inserta filas nuevas con valores de clave que pudieran estar incluidos en el intervalo de claves leído por las instrucciones de la transacción actual, esta dará error.If another transaction inserts new rows with key values that would fall in the range of keys read by any statements in the current transaction, the current transaction fails.

SNAPSHOTSNAPSHOT
Especifica que los datos leídos por cualquier instrucción de una transacción son la versión coherente, desde el punto de vista transaccional, de los datos existentes al inicio de la transacción.Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction.

DATEFIRST = numberDATEFIRST = number
Se aplica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Base de datos SQL de AzureAzure SQL Database.

Especifica el primer día de la semana en un número del 1 al 7.Specifies the first day of the week to a number from 1 through 7. DATEFIRST es opcional.DATEFIRST is optional. Si no se especifica, el valor se deduce del idioma especificado.If it is not specified, the setting is inferred from the specified language.

Para más información, vea SET DATEFIRST (Transact-SQL).For more information, see SET DATEFIRST (Transact-SQL).

DATEFORMAT = formatDATEFORMAT = format
Se aplica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Base de datos SQL de AzureAzure SQL Database.

Especifica el orden de las partes correspondientes al mes, día y año de una fecha para interpretar las cadenas de caracteres date, smalldatetime, datetime, datetime2 y datetimeoffset.Specifies the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings. DATEFORMAT es opcional.DATEFORMAT is optional. Si no se especifica, el valor se deduce del idioma especificado.If it is not specified, the setting is inferred from the specified language.

Para más información, vea SET DATEFORMAT (Transact-SQL).For more information, see SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | ON }DELAYED_DURABILITY = { OFF | ON }
Se aplica a: SQL Server 2014 (12.x)SQL Server 2014 (12.x) a SQL Server 2017SQL Server 2017 y Base de datos SQL de AzureAzure SQL Database.Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Base de datos SQL de AzureAzure SQL Database.

Las confirmaciones de transacciones de SQL ServerSQL Server pueden ser totalmente durables (el valor predeterminado) o durables diferidas. SQL ServerSQL Server transaction commits can be either fully durable, the default, or delayed durable.

Para saber más, vea Control de la durabilidad de las transacciones.For more information, see Control Transaction Durability.

Ejemplos sencillosSimple Examples

Aquí incluimos dos ejemplos rápidos con los que podrá empezar:To help you get started, here are two quick examples:
SELECT DB_NAME() AS ThisDB; devuelve el nombre de la base de datos actual.SELECT DB_NAME() AS ThisDB; returns the name of the current database.
Puede ajustar esa instrucción en un procedimiento almacenado, como:You can wrap that statement in a stored procedure, such as:

CREATE PROC What_DB_is_this     
AS   
SELECT DB_NAME() AS ThisDB; 

Llame al procedimiento de almacenamiento con la instrucción: EXEC What_DB_is_this;Call the store procedure with statement: EXEC What_DB_is_this;

Un paso un poco más complejo consiste en proporcionar un parámetro de entrada para hacer que el procedimiento sea más flexible.Slightly more complex, is to provide an input parameter to make the procedure more flexible. Por ejemplo:For example:

CREATE PROC What_DB_is_that @ID int   
AS    
SELECT DB_NAME(@ID) AS ThatDB;   

Proporcione un número de identificador de base de datos cuando se llame al procedimiento.Provide a database id number when you call the procedure. Por ejemplo, EXEC What_DB_is_that 2; devuelve tempdb.For example, EXEC What_DB_is_that 2; returns tempdb.

Vea la sección Ejemplos casi al final de este tema para ver muchos más ejemplos.See Examples towards the end of this topic for many more examples.

Procedimientos recomendadosBest Practices

Aunque esta no es una lista de prácticas recomendadas exhaustiva, estas sugerencias pueden mejorar el rendimiento de los procedimientos.Although this is not an exhaustive list of best practices, these suggestions may improve procedure performance.

  • Use la instrucción SET NOCOUNT ON como la primera instrucción del cuerpo del procedimiento.Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. Es decir, colóquela a continuación de la palabra clave AS.That is, place it just after the AS keyword. De esta forma, se desactivan los mensajes que devuelve SQL ServerSQL Server al cliente después de que se ejecute cualquier instrucción SELECT, INSERT, UPDATE, MERGE y DELETE.This turns off messages that SQL ServerSQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. El rendimiento general de la base de datos y de la aplicación mejora si se elimina esta sobrecarga de red innecesaria.Overall performance of the database and application is improved by eliminating this unnecessary network overhead. Para más información, vea SET NOCOUNT (Transact-SQL).For information, see SET NOCOUNT (Transact-SQL).

  • Use nombres de esquemas cuando cree o haga referencia a los objetos de base de datos del procedimiento.Use schema names when creating or referencing database objects in the procedure. El tiempo de procesamiento para que Motor de base de datosDatabase Engine resuelva los nombres de los objetos es menor si no tiene que buscar en varios esquemas.It takes less processing time for the Motor de base de datosDatabase Engine to resolve object names if it does not have to search multiple schemas. Si no se especifica el esquema al crear objetos, también se evitan problemas de permisos y acceso causados por el esquema predeterminado de un usuario que se asigna.It also prevents permission and access problems caused by a user’s default schema being assigned when objects are created without specifying the schema.

  • Evite las funciones de ajuste en las columnas especificadas en las cláusulas WHERE y JOIN.Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. De esta forma, las columnas no son deterministas y se evita que el procesador de consultas use índices.Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

  • Evite usar funciones escalares en instrucciones SELECT que devuelvan muchas filas de datos.Avoid using scalar functions in SELECT statements that return many rows of data. Dado que la función escalar se debe aplicar a todas las filas, el comportamiento resultante es similar al procesamiento basado en filas y degrada el rendimiento.Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

  • Evite el uso de SELECT *.Avoid the use of SELECT *. En su lugar, especifique los nombres de columna necesarios.Instead, specify the required column names. De esta forma, puede evitar algunos errores de Motor de base de datosDatabase Engine que detengan la ejecución del procedimiento.This can prevent some Motor de base de datosDatabase Engine errors that stop procedure execution. Por ejemplo, una instrucción SELECT * que devuelve datos de una tabla de 12 columnas y luego los inserta en una tabla temporal de 12 columnas funciona correctamente hasta que cambia el número o el orden de las columnas de cualquiera de las tablas.For example, a SELECT * statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table succeeds until the number or order of columns in either table is changed.

  • Evite el procesamiento o la devolución de demasiados datos.Avoid processing or returning too much data. Restrinja los resultados lo antes posible en el código del procedimiento para que las operaciones posteriores realizadas por él se lleven a cabo con el menor conjunto de datos posible.Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. Envíe únicamente los datos fundamentales a la aplicación cliente.Send just the essential data to the client application. Es más eficaz que enviar datos adicionales a través de la red y forzar que dicha aplicación trabaje con conjuntos de resultados innecesariamente grandes.It is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.

  • Use transacciones explícitas mediante BEGIN/COMMIT TRANSACTION y mantenga las transacciones lo más cortas posible.Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible. Las transacciones más largas significan bloqueos de registro más largos y mayores posibilidades de interbloqueos.Longer transactions mean longer record locking and a greater potential for deadlocking.

  • Use la característica TRY…CATCH de Transact-SQLTransact-SQL para el control de errores dentro de un procedimiento.Use the Transact-SQLTransact-SQL TRY…CATCH feature for error handling inside a procedure. TRY…CATCH puede encapsular todo un bloque de instrucciones Transact-SQLTransact-SQL.TRY…CATCH can encapsulate an entire block of Transact-SQLTransact-SQL statements. Esto no solo crea una sobrecarga de rendimiento menor, sino que también hace que los informes de errores sean más precisos con mucha menos programación.This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming.

  • Use la palabra clave DEFAULT en todas las columnas de la tabla a las que haga referencia en las instrucciones CREATE TABLE o ALTER TABLE de Transact-SQLTransact-SQL en el cuerpo del procedimiento.Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQLTransact-SQL statements in the body of the procedure. De esta forma, se evita pasar el valor NULL a columnas que no admiten valores NULL.This prevents passing NULL to columns that do not allow null values.

  • Use NULL o NOT NULL para todas las columnas de una tabla temporal.Use NULL or NOT NULL for each column in a temporary table. Las opciones ANSI_DFLT_ON y ANSI_DFLT_OFF controlan la forma en la que Motor de base de datosDatabase Engine asigna los atributos NULL o NOT NULL a las columnas si no se especifican dichos atributos en una instrucción CREATE TABLE o ALTER TABLE.The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Motor de base de datosDatabase Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. Si una conexión ejecuta un procedimiento con valores distintos para estas opciones a los que usó la conexión que creó el procedimiento, las columnas de la tabla creada para la segunda conexión pueden tener distinta nulabilidad y exhibir diferentes comportamientos.If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. Si se especifica NULL o NOT NULL explícitamente para todas las columnas, las tablas temporales se crean con la misma nulabilidad para todas las conexiones que ejecuten el procedimiento almacenado.If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.

  • Use instrucciones de modificación que conviertan valores NULL e incluya lógica que elimine las filas con valores NULL de las consultas.Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. Tenga en cuenta que en Transact-SQLTransact-SQL, NULL no es un valor vacío o un valor “nothing”.Be aware that in Transact-SQLTransact-SQL, NULL is not an empty or "nothing" value. Es un marcador de posición para un valor desconocido y puede provocar un comportamiento inesperado, especialmente cuando se consultan conjuntos de resultados o se usan funciones AGGREGATE.It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.

  • Use el operador UNION ALL en vez de los operadores UNION u OR, a menos que exista una necesidad específica de valores distintos.Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. El operador UNION ALL necesita menos sobrecarga de procesamiento porque no se filtran los duplicados del conjunto de resultados.The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

Notas generalesGeneral Remarks

No hay ningún tamaño máximo predefinido para un procedimiento.There is no predefined maximum size of a procedure.

Las variables especificadas en el procedimiento pueden ser variables definidas por el usuario o variables del sistema, como @@SPID.Variables specified in the procedure can be user-defined or system variables, such as @@SPID.

Cuando un procedimiento se ejecuta por primera vez, se compila para determinar que dispone de un plan de acceso óptimo para recuperar los datos.When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. En las siguientes ejecuciones del procedimiento se puede volver a usar el plan ya generado si aún permanece en la memoria caché de planes de Motor de base de datosDatabase Engine.Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the Motor de base de datosDatabase Engine.

Cuando se inicia SQL ServerSQL Server, se pueden ejecutar automáticamente uno o varios procedimientos.One or more procedures can execute automatically when SQL ServerSQL Server starts. Los procedimientos los debe crear el administrador del sistema en la base de datos master y deben ejecutarse bajo el rol fijo de servidor sysadmin como un proceso en segundo plano.The procedures must be created by the system administrator in the master database and executed under the sysadmin fixed server role as a background process. Los procedimientos no pueden tener ningún parámetro de entrada o salida.The procedures cannot have any input or output parameters. Para más información, vea Ejecutar un procedimiento almacenado.For more information, see Execute a Stored Procedure.

Los procedimientos se anidan cuando un procedimiento llama a otro o ejecuta código administrado mediante una referencia a una rutina, tipo o agregado CLR.Procedures are nested when one procedure call another or executes managed code by referencing a CLR routine, type, or aggregate. Los procedimientos y las referencias a código administrado se pueden anidar hasta 32 niveles.Procedures and managed code references can be nested up to 32 levels. El nivel de anidamiento aumenta en uno cuando el procedimiento o la referencia a código administrado a los que se ha llamado empiezan a ejecutarse, y disminuye en uno cuando se completa su ejecución.The nesting level increases by one when the called procedure or managed code reference begins execution and decreases by one when the called procedure or managed code reference completes execution. Los métodos que se invocan desde el código administrado no cuentan para este límite de niveles de anidamiento.Methods invoked from within the managed code do not count against the nesting level limit. Sin embargo, cuando un procedimiento almacenado CLR realiza operaciones de acceso a datos mediante el proveedor administrado de SQL Server, se agrega un nivel de anidamiento adicional en la transición desde código administrado a SQL.However, when a CLR stored procedure performs data access operations through the SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL.

Si se intenta superar el nivel máximo de anidamiento, se producirá un error en toda la cadena de llamada.Attempting to exceed the maximum nesting level causes the entire calling chain to fail. Puede usar la función @@NESTLEVEL para devolver el nivel de anidamiento de la ejecución del procedimiento almacenado actual.You can use the @@NESTLEVEL function to return the nesting level of the current stored procedure execution.

InteroperabilidadInteroperability

El Motor de base de datosDatabase Engine guarda los valores de SET QUOTED_IDENTIFIER y de SET ANSI_NULLS cuando se crea o modifica un procedimiento de Transact-SQLTransact-SQL.The Motor de base de datosDatabase Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQLTransact-SQL procedure is created or modified. Estos valores originales se usan cuando se ejecuta el procedimiento.These original settings are used when the procedure is executed. Por tanto, cualquier valor de sesión de cliente de SET QUOTED_IDENTIFIER y SET ANSI_NULLS se ignora durante la ejecución del procedimiento.Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

Otras opciones de SET, como SET ARITHABORT, SET ANSI_WARNINGS o SET ANSI_PADDINGS, no se guardan cuando se crea o se modifica un procedimiento.Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. Si la lógica del procedimiento depende de un valor específico, incluya una instrucción SET al inicio del procedimiento para garantizar el valor adecuado.If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. Cuando una instrucción SET se ejecuta desde un procedimiento, el valor permanece en vigor solo hasta que se complete la ejecución del procedimiento.When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. A continuación, el valor se restaura al que tenía cuando se llamó al procedimiento.The setting is then restored to the value the procedure had when it was called. Esto permite que clientes individuales establezcan las opciones deseadas sin afectar a la lógica del procedimiento.This enables individual clients to set the options they want without affecting the logic of the procedure.

En un procedimiento se puede especificar cualquier instrucción SET, excepto SET SHOWPLAN_TEXT y SET SHOWPLAN_ALL.Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. Estas deben ser las únicas instrucciones del lote.These must be the only statements in the batch. La opción SET elegida permanece vigente durante la ejecución del procedimiento y, a continuación, revierte a su valor anterior.The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

Nota

No se respeta SET ANSI_WARNINGS al pasar parámetros de un procedimiento almacenado, una función definida por el usuario o al declarar y establecer variables en una instrucción por lotes.SET ANSI_WARNINGS is not honored when passing parameters in a procedure, user-defined function, or when declaring and setting variables in a batch statement. Por ejemplo, si una variable se define como char(3) y después se establece en un valor de más de tres caracteres, los datos se truncan hasta el tamaño definido y la instrucción INSERT o UPDATE se ejecuta correctamente.For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

Limitaciones y restriccionesLimitations and Restrictions

La instrucción CREATE PROCEDURE no se puede combinar con otras instrucciones Transact-SQLTransact-SQL en un único lote.The CREATE PROCEDURE statement cannot be combined with other Transact-SQLTransact-SQL statements in a single batch.

Las siguientes instrucciones no se pueden usar en ninguna parte del cuerpo de un procedimiento almacenado.The following statements cannot be used anywhere in the body of a stored procedure.

CREATE AGGREGATECREATE AGGREGATE CREATE SCHEMACREATE SCHEMA SET SHOWPLAN_TEXTSET SHOWPLAN_TEXT
CREATE DEFAULTCREATE DEFAULT CREATE o ALTER TRIGGERCREATE or ALTER TRIGGER SET SHOWPLAN_XMLSET SHOWPLAN_XML
CREATE o ALTER FUNCTIONCREATE or ALTER FUNCTION CREATE o ALTER VIEWCREATE or ALTER VIEW USE database_nameUSE database_name
CREATE o ALTER PROCEDURECREATE or ALTER PROCEDURE SET PARSEONLYSET PARSEONLY
CREATE RULECREATE RULE SET SHOWPLAN_ALLSET SHOWPLAN_ALL

Un procedimiento puede hacer referencia a tablas que aún no existan.A procedure can reference tables that do not yet exist. En el momento de la creación, solo se realiza la comprobación de la sintaxis.At creation time, only syntax checking is performed. El procedimiento no se compila hasta que se ejecute por primera vez.The procedure is not compiled until it is executed for the first time. Solamente durante la compilación se resuelven todos los objetos a los que se haga referencia en el procedimiento.Only during compilation are all objects referenced in the procedure resolved. Por tanto, se puede crear un procedimiento con la sintaxis correcta que haga referencia a tablas que todavía no existen, aunque este procedimiento provocará un error en tiempo de ejecución si las tablas a las que hace referencia no existen.Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure fails at execution time if the referenced tables do not exist.

No puede especificar el nombre de una función como valor predeterminado de parámetro o como el valor pasado a un parámetro cuando se ejecute un procedimiento.You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. Sin embargo, puede pasar una función como una variable, como se muestra en el ejemplo siguiente.However, you can pass a function as a variable as shown in the following example.

-- Passing the function value as a variable.  
DECLARE @CheckDate datetime = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;   
GO  

Si el procedimiento realiza cambios en una instancia remota de SQL ServerSQL Server, dichos cambios no se pueden revertir.If the procedure makes changes on a remote instance of SQL ServerSQL Server, the changes cannot be rolled back. Los procedimientos remotos no intervienen en las transacciones.Remote procedures do not take part in transactions.

Para que el Motor de base de datosDatabase Engine haga referencia al método correcto cuando está sobrecargado en .NET Framework, el método especificado en la cláusula EXTERNAL NAME debe tener las características siguientes:For the Motor de base de datosDatabase Engine to reference the correct method when it is overloaded in the .NET Framework, the method specified in the EXTERNAL NAME clause must have the following characteristics:

  • Ser declarado un método estático.Be declared as a static method.

  • Recibir el mismo número de parámetros que el número de parámetros del procedimiento.Receive the same number of parameters as the number of parameters of the procedure.

  • Usar tipos de parámetros compatibles con los tipos de datos de los parámetros correspondientes del procedimiento de SQL ServerSQL Server.Use parameter types that are compatible with the data types of the corresponding parameters of the SQL ServerSQL Server procedure. Para más información sobre la correspondencia de tipos de datos de SQL ServerSQL Server y tipos de datos de .NET Framework.NET Framework, vea Mapping CLR Parameter Data (Asignación de datos de parámetros de CLR).For information about matching SQL ServerSQL Server data types to the .NET Framework.NET Framework data types, see Mapping CLR Parameter Data.

MetadatosMetadata

En la tabla siguiente se enumeran las vistas de catálogo y las vistas de administración dinámica que puede usar para devolver información sobre los procedimientos almacenados.The following table lists the catalog views and dynamic management views that you can use to return information about stored procedures.

VerView DescripciónDescription
sys.sql_modulessys.sql_modules Devuelve la definición de un procedimiento de Transact-SQLTransact-SQL.Returns the definition of a Transact-SQLTransact-SQL procedure. El texto de un procedimiento creado con la opción ENCRYPTION no se puede ver mediante la vista de catálogo sys.sql_modules.The text of a procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.
sys.assembly_modulessys.assembly_modules Devuelve información sobre un procedimiento CLR.Returns information about a CLR procedure.
sys.parameterssys.parameters Devuelve información sobre los parámetros definidos en un procedimiento.Returns information about the parameters that are defined in a procedure
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entitiessys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities Devuelve los objetos a los que hace referencia un procedimiento.Returns the objects that are referenced by a procedure.

Para calcular el tamaño de un procedimiento compilado, use los siguientes contadores del Monitor de rendimiento.To estimate the size of a compiled procedure, use the following Performance Monitor Counters.

Nombre del objeto del Monitor de rendimientoPerformance Monitor object name Nombre del contador del Monitor de rendimientoPerformance Monitor Counter name
SQLServer: Plan CacheSQLServer: Plan Cache Object Frecuencia de aciertos de cachéCache Hit Ratio
Páginas de cachéCache Pages
Recuentos de objetos de caché*Cache Object Counts*

* Estos contadores están disponibles para varias categorías de objetos de caché, incluidos Transact-SQLTransact-SQL ad hoc, Transact-SQLTransact-SQL preparados, procedimientos, desencadenadores, etc.*These counters are available for various categories of cache objects including ad hoc Transact-SQLTransact-SQL, prepared Transact-SQLTransact-SQL, procedures, triggers, and so on. Para más información, vea Plan Cache (objeto de SQL Server).For more information, see SQL Server, Plan Cache Object.

SeguridadSecurity

PermisosPermissions

Necesita el permiso CREATE PROCEDURE en la base de datos y el permiso ALTER en el esquema en el que se va a crear el procedimiento o la pertenencia al rol fijo de base de datos db_ddladmin.Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.

Para los procedimientos almacenados de CLR, se necesita la propiedad del ensamblado al que se hace referencia en la cláusula EXTERNAL NAME o el permiso REFERENCES en ese ensamblado.For CLR stored procedures, requires ownership of the assembly referenced in the EXTERNAL NAME clause, or REFERENCES permission on that assembly.

CREATE PROCEDURE y tablas optimizadas para memoriaCREATE PROCEDURE and Memory-Optimized Tables

El acceso a las tablas optimizadas para memoria se puede hacer tanto desde procedimientos almacenados compilados de forma nativa como tradicionales.Memory-optimized tables can be accessed through both traditional and natively compiled stored procedures. En la mayoría de los casos, la manera más eficaz es usar procedimientos nativos.Native procedures are in most cases the more efficient way. Para más información, vea Procedimientos almacenados compilados de forma nativa.For more information, see Natively Compiled Stored Procedures.

En este ejemplo se muestra cómo crear un procedimiento almacenado compilado de forma nativa para acceder a una tabla optimizada para memoria dbo.Departments:The following sample shows how to create a natively compiled stored procedure that accesses a memory-optimized table dbo.Departments:

CREATE PROCEDURE dbo.usp_add_kitchen @dept_id int, @kitchen_count int NOT NULL  
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  

  UPDATE dbo.Departments  
  SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count  
  WHERE id = @dept_id  
END;  
GO  

Un procedimiento creado sin NATIVE_COMPILATION no se puede modificar en un procedimiento almacenado compilado de forma nativa.A procedure created without NATIVE_COMPILATION cannot be altered to a natively compiled stored procedure.

Para conocer la programación en procedimientos almacenados compilados de forma nativa, el área expuesta de consulta admitida y los operadores, vea Características admitidas en los módulos T-SQL compilados de forma nativa.For a discussion of programmability in natively compiled stored procedures, supported query surface area, and operators see Supported Features for Natively Compiled T-SQL Modules.

EjemplosExamples

CategoríaCategory Elementos de sintaxis ofrecidosFeatured syntax elements
Sintaxis básicaBasic Syntax CREATE PROCEDURECREATE PROCEDURE
Pasar parámetrosPassing parameters @parameter
   • = predeterminado   • = default
   • OUTPUT   • OUTPUT
   • Tipo de parámetro con valores de tabla   • table-valued parameter type
   • CURSOR VARYING   • CURSOR VARYING
Modificar datos mediante un procedimiento almacenadoModifying data by using a stored procedure UPDATEUPDATE
Tratamiento de erroresError Handling TRY…CATCHTRY…CATCH
Ofuscar la definición del procedimientoObfuscating the procedure definition WITH ENCRYPTIONWITH ENCRYPTION
Forzar la recompilación del procedimientoForcing the Procedure to Recompile WITH RECOMPILEWITH RECOMPILE
Establecer el contexto de seguridadSetting the Security Context EXECUTE ASEXECUTE AS

Sintaxis básicaBasic Syntax

En los ejemplos de esta sección se muestra la funcionalidad básica de la instrucción CREATE PROCEDURE con la sintaxis mínima necesaria.Examples in this section demonstrate the basic functionality of the CREATE PROCEDURE statement using the minimum required syntax.

A.A. Crear un procedimiento simple Transact-SQLCreating a simple Transact-SQL procedure

En el ejemplo siguiente se crea un procedimiento almacenado que devuelve todos los empleados (nombre y apellidos), sus puestos y los nombres de sus departamentos a partir de una vista de la base de datos AdventureWorks2012AdventureWorks2012.The following example creates a stored procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view in the AdventureWorks2012AdventureWorks2012 database. Este procedimiento no usa ningún parámetro.This procedure does not use any parameters. En el ejemplo se muestran a continuación tres métodos de ejecutar el procedimiento.The example then demonstrates three methods of executing the procedure.

CREATE PROCEDURE HumanResources.uspGetAllEmployees  
AS  
    SET NOCOUNT ON;  
    SELECT LastName, FirstName, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment;  
GO  

SELECT * FROM HumanResources.vEmployeeDepartment;  

El procedimiento uspGetEmployees se puede ejecutar de estas formas:The uspGetEmployees procedure can be executed in the following ways:

EXECUTE HumanResources.uspGetAllEmployees;  
GO  
-- Or  
EXEC HumanResources.uspGetAllEmployees;  
GO  
-- Or, if this procedure is the first statement within a batch:  
HumanResources.uspGetAllEmployees;  

B.B. Devolver más de un conjunto de resultadosReturning more than one result set

El procedimiento siguiente devuelve dos conjuntos de resultados.The following procedure returns two result sets.

CREATE PROCEDURE dbo.uspMultipleResults   
AS  
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;  
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;  
GO  

C.C. Crear un procedimiento almacenado CLRCreating a CLR stored procedure

En este ejemplo se crea el procedimiento GetPhotoFromDB que hace referencia al método GetPhotoFromDB de la clase LargeObjectBinary del ensamblado HandlingLOBUsingCLR.The following example creates the GetPhotoFromDB procedure that references the GetPhotoFromDB method of the LargeObjectBinary class in the HandlingLOBUsingCLR assembly. Antes de crear el procedimiento, el ensamblado HandlingLOBUsingCLR se registra en la base de datos local.Before the procedure is created, the HandlingLOBUsingCLR assembly is registered in the local database.

Se aplica a: SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017, SQL DatabaseSQL Database (si usa un ensamblado creado a partir de assembly_bits).Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL DatabaseSQL Database (if using an assembly created from assembly_bits.

CREATE ASSEMBLY HandlingLOBUsingCLR  
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';  
GO  
CREATE PROCEDURE dbo.GetPhotoFromDB  
(  
    @ProductPhotoID int,  
    @CurrentDirectory nvarchar(1024),  
    @FileName nvarchar(1024)  
)  
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;  
GO  

Pasar parámetrosPassing Parameters

En los ejemplos de esta sección se muestra cómo usar parámetros de entrada y de salida para pasar valores a y desde un procedimiento almacenado.Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure.

D.D. Crear un procedimiento con parámetros de entradaCreating a procedure with input parameters

En el ejemplo siguiente se crea un procedimiento almacenado que devuelve información sobre un empleado concreto pasando valores para el nombre y los apellidos del empleado.The following example creates a stored procedure that returns information for a specific employee by passing values for the employee's first name and last name. Este procedimiento solo acepta coincidencias exactas de los parámetros pasados.This procedure accepts only exact matches for the parameters passed.

IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL   
    DROP PROCEDURE HumanResources.uspGetEmployees;  
GO  
CREATE PROCEDURE HumanResources.uspGetEmployees   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   

    SET NOCOUNT ON;  
    SELECT FirstName, LastName, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment  
    WHERE FirstName = @FirstName AND LastName = @LastName;  
GO  

El procedimiento uspGetEmployees se puede ejecutar de estas formas:The uspGetEmployees procedure can be executed in the following ways:

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';  
-- Or  
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';  
GO  
-- Or  
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';  
GO  
-- Or, if this procedure is the first statement within a batch:  
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';  

E.E. Usar un procedimiento con parámetros comodínUsing a procedure with wildcard parameters

En el ejemplo siguiente se crea un procedimiento almacenado que devuelve información sobre empleados pasando valores totales o parciales para el nombre y los apellidos de los empleados.The following example creates a stored procedure that returns information for employees by passing full or partial values for the employee's first name and last name. Este patrón de procedimiento coincide con los parámetros pasados o, si estos no se proporcionan, usa los valores predeterminados (apellidos que empiezan por la letra D).This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter D).

IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL   
    DROP PROCEDURE HumanResources.uspGetEmployees2;  
GO  
CREATE PROCEDURE HumanResources.uspGetEmployees2   
    @LastName nvarchar(50) = N'D%',   
    @FirstName nvarchar(50) = N'%'  
AS   
    SET NOCOUNT ON;  
    SELECT FirstName, LastName, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment  
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;  

El procedimiento uspGetEmployees2 se puede ejecutar en muchas combinaciones.The uspGetEmployees2 procedure can be executed in many combinations. Aquí se muestran solo algunas combinaciones posibles.Only a few possible combinations are shown here.

EXECUTE HumanResources.uspGetEmployees2;  
-- Or  
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';  
-- Or  
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';  
-- Or  
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';  
-- Or  
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';  
-- Or  
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';  

F.F. Usar parámetros OUTPUTUsing OUTPUT parameters

En el ejemplo siguiente se crea el procedimiento uspGetList.The following example creates the uspGetList procedure. Este procedimiento devuelve una lista de productos cuyos precios no superan una cantidad especificada.This procedures returns a list of products that have prices that do not exceed a specified amount. El ejemplo se muestra con varias instrucciones SELECT y varios parámetros OUTPUT.The example shows using multiple SELECT statements and multiple OUTPUT parameters. Los parámetros OUTPUT permiten a un procedimiento externo, un lote o más de una instrucción Transact-SQLTransact-SQL tener acceso a un conjunto de valores durante la ejecución del procedimiento.OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQLTransact-SQL statement to access a value set during the procedure execution.

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL   
    DROP PROCEDURE Production.uspGetList;  
GO  
CREATE PROCEDURE Production.uspGetList @Product varchar(40)   
    , @MaxPrice money   
    , @ComparePrice money OUTPUT  
    , @ListPrice money OUT  
AS  
    SET NOCOUNT ON;  
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'  
    FROM Production.Product AS p  
    JOIN Production.ProductSubcategory AS s   
      ON p.ProductSubcategoryID = s.ProductSubcategoryID  
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;  
-- Populate the output variable @ListPprice.  
SET @ListPrice = (SELECT MAX(p.ListPrice)  
        FROM Production.Product AS p  
        JOIN  Production.ProductSubcategory AS s   
          ON p.ProductSubcategoryID = s.ProductSubcategoryID  
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);  
-- Populate the output variable @compareprice.  
SET @ComparePrice = @MaxPrice;  
GO  

Ejecute uspGetList para obtener una lista de los productos de Adventure WorksAdventure Works (bicicletas) que cuestan menos de $700.Execute uspGetList to return a list of Adventure WorksAdventure Works products (Bikes) that cost less than $700. Los parámetros de OUTPUT, @Cost y @ComparePrices se usa con el lenguaje de control de flujo para devolver un mensaje en la ventana Mensajes.The OUTPUT parameters @Cost and @ComparePrices are used with control-of-flow language to return a message in the Messages window.

Nota

La variable OUTPUT debe definirse al crear el procedimiento y también al utilizar la variable.The OUTPUT variable must be defined when the procedure is created and also when the variable is used. Los nombres de parámetro y de variable no tienen por qué coincidir, pero el tipo de datos y la posición de los parámetros sí deben coincidir, a no ser que se use @ListPrice = variable.The parameter name and variable name do not have to match; however, the data type and parameter positioning must match, unless @ListPrice = variable is used.

DECLARE @ComparePrice money, @Cost money ;  
EXECUTE Production.uspGetList '%Bikes%', 700,   
    @ComparePrice OUT,   
    @Cost OUTPUT  
IF @Cost <= @ComparePrice   
BEGIN  
    PRINT 'These products can be purchased for less than   
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'  
END  
ELSE  
    PRINT 'The prices for all products in this category exceed   
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.';  

Éste es el conjunto de resultados parciales:Here is the partial result set:

Product                     List Price  
--------------------------  ----------  
Road-750 Black, 58          539.99  
Mountain-500 Silver, 40     564.99  
Mountain-500 Silver, 42     564.99  
...  
Road-750 Black, 48          539.99  
Road-750 Black, 52          539.99  

(14 row(s) affected)   

These items can be purchased for less than $700.00.

G.G. Usar un parámetro con valores de tablaUsing a Table-Valued Parameter

En el ejemplo siguiente se usa un tipo de parámetro con valores de tabla para insertar varias filas en una tabla.The following example uses a table-valued parameter type to insert multiple rows into a table. En el ejemplo se crea el tipo de parámetro, se declara una variable de tabla para hacer referencia a ella, se rellena la lista de parámetros y, a continuación, se pasan los valores a un procedimiento almacenado.The example creates the parameter type, declares a table variable to reference it, fills the parameter list, and then passes the values to a stored procedure. El procedimiento almacenado usa los valores para insertar varias filas en una tabla.The stored procedure uses the values to insert multiple rows into a table.

/* Create a table type. */  
CREATE TYPE LocationTableType AS TABLE   
( LocationName VARCHAR(50)  
, CostRate INT );  
GO  

/* Create a procedure to receive data for the table-valued parameter. */  
CREATE PROCEDURE usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO [AdventureWorks2012].[Production].[Location]  
           ([Name]  
           ,[CostRate]  
           ,[Availability]  
           ,[ModifiedDate])  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
GO  

/* Declare a variable that references the type. */  
DECLARE @LocationTVP   
AS LocationTableType;  

/* Add data to the table variable. */  
INSERT INTO @LocationTVP (LocationName, CostRate)  
    SELECT [Name], 0.00  
    FROM   
    [AdventureWorks2012].[Person].[StateProvince];  

/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  
H.H. Usar un parámetro de cursor OUTPUTUsing an OUTPUT cursor parameter

En el ejemplo siguiente se usa el parámetro de cursor OUTPUT para devolver un cursor que es local en un procedimiento al lote, procedimiento o desencadenador que llama.The following example uses the OUTPUT cursor parameter to pass a cursor that is local to a procedure back to the calling batch, procedure, or trigger.

Primero, crea el procedimiento que declara y, a continuación, abre un cursor en la tabla Currency:First, create the procedure that declares and then opens a cursor on the Currency table:

CREATE PROCEDURE dbo.uspCurrencyCursor   
    @CurrencyCursor CURSOR VARYING OUTPUT  
AS  
    SET NOCOUNT ON;  
    SET @CurrencyCursor = CURSOR  
    FORWARD_ONLY STATIC FOR  
      SELECT CurrencyCode, Name  
      FROM Sales.Currency;  
    OPEN @CurrencyCursor;  
GO  

A continuación, ejecuta un lote que declara una variable local de cursor, ejecuta el procedimiento para asignar el cursor a la variable local y, por último, captura las filas desde el cursor.Next, run a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

DECLARE @MyCursor CURSOR;  
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  
WHILE (@@FETCH_STATUS = 0)  
BEGIN;  
     FETCH NEXT FROM @MyCursor;  
END;  
CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO  

Modificar datos mediante un procedimiento almacenadoModifying Data by using a Stored Procedure

En los ejemplos de esta sección se muestra cómo insertar o modificar datos de tablas o vistas incluyendo una instrucción del lenguaje de manipulación de datos (DML) en la definición del procedimiento.Examples in this section demonstrate how to insert or modify data in tables or views by including a Data Manipulation Language (DML) statement in the definition of the procedure.

I.I. Usar UPDATE en un procedimiento almacenadoUsing UPDATE in a stored procedure

En el ejemplo siguiente se usa una instrucción UPDATE en un procedimiento almacenado.The following example uses an UPDATE statement in a stored procedure. El procedimiento toma un parámetro de entrada @NewHours y un parámetro de salida @RowCount.The procedure takes one input parameter, @NewHours and one output parameter @RowCount. El valor del parámetro @NewHours se usa en la instrucción UPDATE para actualizar la columna VacationHours de la tabla HumanResources.Employee.The @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. El parámetro de salida @RowCount se usa para devolver el número de filas afectadas a una variable local.The @RowCount output parameter is used to return the number of rows affected to a local variable. Se usa una expresión CASE en la cláusula SET para determinar de forma condicional el valor establecido para VacationHours.A CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours. Cuando se paga al empleado por hora (SalariedFlag = 0), VacationHours se establece en el número actual de horas más el valor especificado en @NewHours; de lo contrario, VacationHours se establece en el valor especificado en @NewHours.When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is set to the value specified in @NewHours.

CREATE PROCEDURE HumanResources.Update_VacationHours  
@NewHours smallint  
AS   
SET NOCOUNT ON;  
UPDATE HumanResources.Employee  
SET VacationHours =   
    ( CASE  
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours  
         ELSE @NewHours  
       END  
    )  
WHERE CurrentFlag = 1;  
GO  

EXEC HumanResources.Update_VacationHours 40;  

Tratamiento de erroresError Handling

En los ejemplos de esta sección se muestran métodos de controlar errores que pueden producirse cuando se ejecuta el procedimiento almacenado.Examples in this section demonstrate methods to handle errors that might occur when the stored procedure is executed.

J.J. Usar TRY…CATCHUsing TRY…CATCH

En el ejemplo siguiente se usa la construcción TRY…CATCH para devolver información de error capturada durante la ejecución de un procedimiento almacenado.The following example using the TRY…CATCH construct to return error information caught during the execution of a stored procedure.

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )  
AS  
SET NOCOUNT ON;  
BEGIN TRY  
   BEGIN TRANSACTION   
   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.  
   DELETE FROM Production.WorkOrderRouting  
   WHERE WorkOrderID = @WorkOrderID;  

   -- Delete the rows from the parent table, WorkOrder, for the specified work order.  
   DELETE FROM Production.WorkOrder  
   WHERE WorkOrderID = @WorkOrderID;  

   COMMIT  

END TRY  
BEGIN CATCH  
  -- Determine if an error occurred.  
  IF @@TRANCOUNT > 0  
     ROLLBACK  

  -- Return the error information.  
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;  
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();  
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);  
END CATCH;  

GO  
EXEC Production.uspDeleteWorkOrder 13;  

/* Intentionally generate an error by reversing the order in which rows 
   are deleted from the parent and child tables. This change does not 
   cause an error when the procedure definition is altered, but produces 
   an error when the procedure is executed.  
*/  
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int )  
AS  

BEGIN TRY  
   BEGIN TRANSACTION   
      -- Delete the rows from the parent table, WorkOrder, for the specified work order.  
   DELETE FROM Production.WorkOrder  
   WHERE WorkOrderID = @WorkOrderID;  

   -- Delete rows from the child table, WorkOrderRouting, for the specified work order.  
   DELETE FROM Production.WorkOrderRouting  
   WHERE WorkOrderID = @WorkOrderID;  

   COMMIT TRANSACTION  

END TRY  
BEGIN CATCH  
  -- Determine if an error occurred.  
  IF @@TRANCOUNT > 0  
     ROLLBACK TRANSACTION  

  -- Return the error information.  
  DECLARE @ErrorMessage nvarchar(4000),  @ErrorSeverity int;  
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();  
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);  
END CATCH;  
GO  
-- Execute the altered procedure.  
EXEC Production.uspDeleteWorkOrder 15;  

DROP PROCEDURE Production.uspDeleteWorkOrder;  

Ofuscar la definición del procedimientoObfuscating the Procedure Definition

En los ejemplos de esta sección se muestra cómo ofuscar la definición del procedimiento almacenado.Examples in this section show how to obfuscate the definition of the stored procedure.

K.K. Usar la opción WITH ENCRYPTIONUsing the WITH ENCRYPTION option

En el ejemplo siguiente se crea el procedimiento HumanResources.uspEncryptThis.The following example creates the HumanResources.uspEncryptThis procedure.

Se aplica a: SQL Server 2008SQL Server 2008 hasta SQL Server 2017SQL Server 2017, SQL Database.Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL Database.

CREATE PROCEDURE HumanResources.uspEncryptThis  
WITH ENCRYPTION  
AS  
    SET NOCOUNT ON;  
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, 
        VacationHours, SickLeaveHours   
    FROM HumanResources.Employee;  
GO  

La opción WITH ENCRYPTION ofusca la definición del procedimiento al consultar el catálogo del sistema o al usar funciones de metadatos, como se muestra en estos ejemplos.The WITH ENCRYPTION option obfuscates the definition of the procedure when querying the system catalog or using metadata functions, as shown by the following examples.

Ejecute sp_helptext:Run sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';  

El conjunto de resultados es el siguiente.Here is the result set.

The text for object 'HumanResources.uspEncryptThis' is encrypted.

Realice una consulta directamente a la vista de catálogo sys.sql_modules:Directly query the sys.sql_modules catalog view:

SELECT definition FROM sys.sql_modules  
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');  

El conjunto de resultados es el siguiente.Here is the result set.

definition  
--------------------------------  
NULL  

Forzar la recompilación del procedimientoForcing the Procedure to Recompile

En los ejemplos de esta sección se usa la cláusula WITH RECOMPILE para forzar la recompilación del procedimiento cada vez que se ejecute.Examples in this section use the WITH RECOMPILE clause to force the procedure to recompile every time it is executed.

L.L. Usar la opción WITH RECOMPILEUsing the WITH RECOMPILE option

La cláusula WITH RECOMPILE es útil cuando los parámetros suministrados al procedimiento no son los típicos y cuando no debe almacenarse un nuevo plan de ejecución en la memoria caché o en memoria.The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.

IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL   
    DROP PROCEDURE dbo.uspProductByVendor;  
GO  
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'  
WITH RECOMPILE  
AS  
    SET NOCOUNT ON;  
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'  
    FROM Purchasing.Vendor AS v   
    JOIN Purchasing.ProductVendor AS pv   
      ON v.BusinessEntityID = pv.BusinessEntityID   
    JOIN Production.Product AS p   
      ON pv.ProductID = p.ProductID  
    WHERE v.Name LIKE @Name;  

Establecer el contexto de seguridadSetting the Security Context

En los ejemplos de esta sección se usa la cláusula EXECUTE AS para establecer el contexto de seguridad en el que se ejecuta el procedimiento almacenado.Examples in this section use the EXECUTE AS clause to set the security context in which the stored procedure executes.

M.M. Usar la cláusula EXECUTE ASUsing the EXECUTE AS clause

En este ejemplo se muestra el uso de la cláusula EXECUTE AS para especificar el contexto de seguridad en el que se puede ejecutar un procedimiento.The following example shows using the EXECUTE AS clause to specify the security context in which a procedure can be executed. En el ejemplo, la opción CALLER especifica que el procedimiento se puede ejecutar en el contexto del usuario que lo llama.In the example, the option CALLER specifies that the procedure can be executed in the context of the user that calls it.

CREATE PROCEDURE Purchasing.uspVendorAllInfo  
WITH EXECUTE AS CALLER  
AS  
    SET NOCOUNT ON;  
    SELECT v.Name AS Vendor, p.Name AS 'Product name',   
      v.CreditRating AS 'Rating',   
      v.ActiveFlag AS Availability  
    FROM Purchasing.Vendor v   
    INNER JOIN Purchasing.ProductVendor pv  
      ON v.BusinessEntityID = pv.BusinessEntityID   
    INNER JOIN Production.Product p  
      ON pv.ProductID = p.ProductID   
    ORDER BY v.Name ASC;  
GO  

N.N. Crear conjuntos de permisos personalizadosCreating custom permission sets

En el ejemplo siguiente se usa EXECUTE AS para crear permisos personalizados para una operación de base de datos.The following example uses EXECUTE AS to create custom permissions for a database operation. Algunas operaciones, como TRUNCATE TABLE, no tienen permisos que se puedan conceder.Some operations such as TRUNCATE TABLE, do not have grantable permissions. Si se incorpora la instrucción TRUNCATE TABLE en un procedimiento almacenado y se especifica la ejecución del procedimiento 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 en el procedimiento.By incorporating the TRUNCATE TABLE statement within a stored procedure and specifying that procedure execute as a user that 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 procedure.

CREATE PROCEDURE dbo.TruncateMyTable  
WITH EXECUTE AS SELF  
AS TRUNCATE TABLE MyDB..MyTable;  

Ejemplos: Almacenamiento de datos SQL de AzureAzure SQL Data Warehouse y Almacenamiento de datos paralelosParallel Data WarehouseExamples: Almacenamiento de datos SQL de AzureAzure SQL Data Warehouse and Almacenamiento de datos paralelosParallel Data Warehouse

O.O. Crear un procedimiento almacenado que ejecute una instrucción SELECTCreate a Stored Procedure that runs a SELECT statement

Este ejemplo muestra la sintaxis básica para crear y ejecutar un procedimiento.This example shows the basic syntax for creating and running a procedure. Cuando se ejecuta un lote, CREATE PROCEDURE debe ser la primera instrucción.When running a batch, CREATE PROCEDURE must be the first statement. Por ejemplo, para crear el siguiente procedimiento almacenado en AdventureWorksPDW2012AdventureWorksPDW2012, establezca primero el contexto de base de datos y luego ejecute la instrucción CREATE PROCEDURE.For example, to create the following stored procedure in AdventureWorksPDW2012AdventureWorksPDW2012, set the database context first, and then run the CREATE PROCEDURE statement.

-- Uses AdventureWorksDW database  

--Run CREATE PROCEDURE as the first statement in a batch.  
CREATE PROCEDURE Get10TopResellers   
AS   
BEGIN  
    SELECT TOP (10) r.ResellerName, r.AnnualSales  
    FROM DimReseller AS r  
    ORDER BY AnnualSales DESC, ResellerName ASC;  
END  
;  

--Show 10 Top Resellers  
EXEC Get10TopResellers;  

Ver tambiénSee Also

ALTER PROCEDURE (Transact-SQL) ALTER PROCEDURE (Transact-SQL)
Lenguaje de control de flujo (Transact-SQL) Control-of-Flow Language (Transact-SQL)
Cursores Cursors
Tipos de datos (Transact-SQL) Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
DROP PROCEDURE (Transact-SQL) DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
EXECUTE AS (Transact-SQL) EXECUTE AS (Transact-SQL)
Procedimientos almacenados (motor de base de datos) Stored Procedures (Database Engine)
sp_procoption (Transact-SQL) sp_procoption (Transact-SQL)
sp_recompile (Transact-SQL) sp_recompile (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.parameters (Transact-SQL) sys.parameters (Transact-SQL)
sys.procedures (Transact-SQL) sys.procedures (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.numbered_procedures (Transact-SQL) sys.numbered_procedures (Transact-SQL)
sys.numbered_procedure_parameters (Transact-SQL) sys.numbered_procedure_parameters (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
Crear un procedimiento almacenado Create a Stored Procedure
Usar parámetros con valores de tabla (motor de base de datos) Use Table-Valued Parameters (Database Engine)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)