CREATE PROCEDURE (Transact-SQL)

  • Artículo

Crea un procedimiento almacenado Transact-SQL o Common Language Runtime (CLR) en SQL Server 2012. Los procedimientos almacenados son similares a los procedimientos de otros lenguajes de programación en tanto que pueden:

  • 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.

  • Contener instrucciones de programación que realicen operaciones en la base de datos, incluidas las llamadas a otros procedimientos.

  • 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.

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.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

--Transact-SQL Stored Procedure Syntax
CREATE { 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 ]

--CLR Stored Procedure Syntax
CREATE { 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 }
[;]

Argumentos

  • schema_name
    El nombre del esquema al que pertenece el procedimiento. Los procedimientos se enlazan a un esquema. 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.

  • procedure_name
    El nombre del procedimiento. Los nombres de los procedimientos deben cumplir las reglas de los identificadores y deben ser exclusivos en el esquema.

    Evite el uso del prefijo sp_ cuando asigne nombre a los procedimientos. SQL Server usa este prefijo para designar los procedimientos del sistema. 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.

    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. Solo la conexión que creó un procedimiento temporal local lo ve y se quita cuando se cierra esa conexión. Un procedimiento temporal global está disponible para todas las conexiones y se quita al final de la última sesión que lo use. No se pueden especificar nombres temporales para los procedimientos CLR.

    El nombre completo de un procedimiento o un procedimiento temporal global, incluidos los signos de número ##, no puede superar los 128 caracteres. El nombre completo de un procedimiento temporal local, incluido el signo de número #, no puede superar los 116 caracteres.

  • ; number
    Entero opcional que se usa para agrupar procedimientos con el mismo nombre. Estos procedimientos agrupados se pueden quitar juntos mediante una instrucción DROP PROCEDURE.

    [!NOTA]

    Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

    Los procedimientos numerados no pueden usar xml ni los tipos definidos por el usuario de CLR y no se pueden usar en una guía de plan.

  • @ parameter
    Parámetro declarado en el procedimiento. Especifique un nombre de parámetro con una arroba (@) como el primer carácter. El nombre del parámetro se debe ajustar a las reglas de los identificadores. Los parámetros son locales respecto al procedimiento; los mismos nombres de parámetro se pueden usar en otros procedimientos.

    Se pueden declarar uno o varios parámetros; el valor máximo es 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. 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. 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. Para obtener más información, vea EXECUTE (Transact-SQL).

    No se pueden declarar los parámetros si se especifica FOR REPLICATION.

  • [ type_schema_name**.** ] data_type
    El tipo de datos del parámetro y el esquema al que pertenece el tipo de datos.

    Directrices para procedimientos Transact-SQL:

    • Todos los tipos de datos de Transact-SQL se pueden usar como parámetros.

    • Puede usar el tipo de tabla definido por el usuario para crear parámetros con valores de tabla. Los parámetros con valores de tabla solo pueden ser parámetros INPUT y deben ir acompañados de la palabra clave READONLY. Para obtener más información, vea Usar parámetros con valores de tabla (motor de base de datos)

    • Los tipos de datos de cursor solo pueden ser parámetros OUTPUT y deben ir acompañados de la palabra clave VARYING.

    Directrices para procedimientos CLR:

    • Todos los tipos de datos de SQL Server nativos con un equivalente en código administrado se pueden usar como parámetros. Para obtener más información acerca de la correspondencia entre los tipos CLR y los tipos de datos del sistema de SQL Server, vea Asignar datos de parámetros CLR. Para obtener más información acerca de los tipos de datos del sistema de SQL Server y su sintaxis, vea Tipos de datos (Transact-SQL).

    • Los tipos de datos con valores de tabla o de cursor no se pueden usar como parámetros.

    • 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.

  • VARYING
    Especifica el conjunto de resultados admitido como parámetro de salida. Este parámetro lo crea de forma dinámica el procedimiento y su contenido puede variar. Solo se aplica a los parámetros de tipo cursor. Esta opción no es válida para los procedimientos CLR.

  • default
    Valor predeterminado de un parámetro. Si se define un valor predeterminado para un parámetro, el procedimiento se puede ejecutar sin especificar ningún valor para ese parámetro. El valor predeterminado debe ser una constante o puede ser 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. Vea el ejemplo C más adelante.

    Los valores predeterminados solo se registran en la columna sys.parameters.default de los procedimientos CLR. Esa columna será NULL para los parámetros de procedimientos Transact-SQL.

  • OUT | OUTPUT
    Indica que se trata de un parámetro de salida. Use parámetros OUTPUT para devolver valores al autor de la llamada del procedimiento. Los parámetros text, ntext e image no se pueden usar como parámetros OUTPUT, a menos que se trate de un procedimiento CLR. Un parámetro de salida puede ser un marcador de posición de cursor, a menos que el procedimiento sea un procedimiento CLR. Un tipo de datos con valores de tabla no se puede especificar como parámetro OUTPUT de un procedimiento.

  • READONLY
    Indica que el parámetro no se puede actualizar ni modificar en el cuerpo del procedimiento. Si el tipo de parámetro es un tipo con valores de tabla, se debe especificar READONLY.

  • RECOMPILE
    Indica que Motor de base de datos no almacena en caché ningún plan de consulta para este procedimiento, forzándolo a ser compilado cada vez que se ejecute. Para obtener más información sobre las razones para forzar una nueva compilación, vea Volver a compilar un procedimiento almacenado. Esta opción no se puede usar cuando se especifica FOR REPLICATION ni para procedimientos CLR.

    Para indicar a Motor de base de datos que descarte planes de consulta para consultas individuales en un procedimiento, use la sugerencia de consulta RECOMPILE en la definición de la consulta. Para obtener más información, vea Sugerencias de consulta (Transact-SQL).

  • ENCRYPTION
    Indica que SQL Server convertirá el texto original de la instrucción CREATE PROCEDURE en un formato confuso. La salida de la ofuscación no se ve directamente en ninguna de las vistas de catálogo de SQL 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. Sin embargo, estará disponible para los usuarios con privilegios que puedan obtener acceso a las tablas del sistema a través del puerto DAC o directamente a los archivos de base de datos. 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. Para obtener más información acerca del acceso a los metadatos del sistema, vea Configuración de visibilidad de los metadatos.

    Esta opción no es válida para los procedimientos CLR.

    Los procedimientos creados mediante esta opción no se pueden publicar como parte de la replicación de SQL Server.

  • EXECUTE AS
    Especifica el contexto de seguridad en el que se ejecuta el procedimiento.

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

  • FOR REPLICATION
    Especifica que el procedimiento se crea para replicación. Por consiguiente, no se puede ejecutar en el suscriptor. Se usa un procedimiento creado con la opción FOR REPLICATION como filtro de procedimiento y solo se ejecuta durante la replicación. No se pueden declarar los parámetros si se especifica FOR REPLICATION. No se puede especificar FOR REPLICATION en los procedimientos CLR. La opción RECOMPILE se ignora en el caso de procedimientos creados con FOR REPLICATION.

    Un procedimiento FOR REPLICATION tendrá un tipo de objeto RF en sys.objects y sys.procedures.

  • { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
    Una o más instrucciones Transact-SQL que comprenden el cuerpo del procedimiento. Puede usar las palabras clave BEGIN y END opcionales para incluir las instrucciones. Para obtener información, vea las secciones Prácticas recomendadas, Comentarios generales, así como Limitaciones y restricciones que aparecen más adelante.

  • EXTERNAL NAME assembly_name**.class_name.method_name
    Especifica el método de un ensamblado de .NET Framework para un procedimiento CLR al que se va a hacer referencia. class_name debe ser un identificador válido de SQL Server y debe existir como clase en el ensamblado. Si la clase tiene un nombre completo de espacio de nombres que utiliza un punto (    .) para separar las partes del espacio de nombres, el nombre de la clase debe delimitarse mediante paréntesis ([** ]) o comillas (" "). El método especificado debe ser un método estático de la clase.

    De manera predeterminada, SQL Server no puede ejecutar código CLR. Se pueden crear, modificar y quitar objetos de base de datos que hagan referencia a módulos de Common Language Runtime; sin embargo, estas referencias no se pueden ejecutar en SQL Server hasta que se habilite la opción clr enabled. Para habilitar esta opción, use sp_configure.

    [!NOTA]

    Los procedimientos CLR no se admiten en las bases de datos independientes.

Prácticas recomendadas

Aunque esta no es una lista de prácticas recomendadas exhaustiva, estas sugerencias pueden mejorar el rendimiento de los procedimientos.

  • Use la instrucción SET NOCOUNT ON como la primera instrucción del cuerpo del procedimiento. Es decir, colóquela a continuación de la palabra clave AS. De esta forma, se desactivan los mensajes que devuelve SQL Server al cliente después de que se ejecute cualquier instrucción SELECT, INSERT, UPDATE, MERGE y DELETE. El rendimiento general de la base de datos y de la aplicación mejora si se elimina esta sobrecarga de red innecesaria. Para obtener información, vea SET NOCOUNT (Transact-SQL).

  • Use nombres de esquemas cuando cree o haga referencia a los objetos de base de datos del procedimiento. El tiempo de procesamiento será menor para que Motor de base de datos resuelva los nombres de los objetos si no tiene que buscar en varios esquemas. Además, se evitarán problemas de permisos y acceso causados por el esquema predeterminado de un usuario que se asigna cuando se crean objetos sin especificar el esquema.

  • Evite las funciones de ajuste en las columnas especificadas en las cláusulas WHERE y JOIN. De esta forma, las columnas no son deterministas y se evita que el procesador de consultas use índices.

  • Evite usar funciones escalares en instrucciones SELECT que devuelvan muchas filas de datos. 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.

  • Evite el uso de SELECT *. En su lugar, especifique los nombres de columna necesarios. De esta forma, puede evitar algunos errores de Motor de base de datos que detengan la ejecución del procedimiento. Por ejemplo, una instrucción SELECT * que devuelve datos desde una tabla de 12 columnas y, a continuación, inserta los datos en una tabla temporal de 12 columnas funcionará correctamente hasta que cambie el número o el orden de las columnas de cualquiera de las tablas.

  • Evite el procesamiento o la devolución de demasiados datos. 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. Envíe únicamente los datos fundamentales a la aplicación cliente. 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.

  • Use transacciones explícitas mediante el uso de BEGIN/END TRANSACTION y mantenga las transacciones lo más cortas posible. Las transacciones más largas significan bloqueos de registro más largos y mayores posibilidades de interbloqueos.

  • Use la característica TRY…CATCH de Transact-SQL para el control de errores dentro de un procedimiento. TRY…CATCH puede encapsular todo un bloque de instrucciones Transact-SQL. 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.

  • 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-SQL en el cuerpo del procedimiento. De esta forma, se evita pasar el valor NULL a columnas que no admiten valores NULL.

  • Use NULL o NOT NULL para todas las columnas de una tabla temporal. Las opciones ANSI_DFLT_ON y ANSI_DFLT_OFF controlan la forma en la que Motor de base de datos 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. 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. 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.

  • Use instrucciones de modificación que conviertan valores NULL e incluya lógica que elimine las filas con valores NULL de las consultas. Tenga en cuenta que en Transact-SQL, NULL no es un valor vacío ni el valor “Nothing”. 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.

  • Use el operador UNION ALL en vez de los operadores UNION u OR, a menos que exista una necesidad específica de valores distintos. El operador UNION ALL necesita menos sobrecarga de procesamiento porque no se filtran los duplicados del conjunto de resultados.

Comentarios generales

No hay ningún tamaño máximo predefinido para un procedimiento.

Las variables especificadas en el procedimiento las puede definir el usuario o pueden ser variables del sistema, como @@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. 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 datos.

Cuando se inicia SQL Server, se pueden ejecutar automáticamente uno o varios procedimientos. Los procedimientos los debe crear el administrador del sistema en la base de datos maestra y ejecutarse bajo el rol fijo de servidor sysadmin como proceso en segundo plano. Los procedimientos no pueden tener ningún parámetro de entrada o salida. Para obtener más información, vea Ejecutar un procedimiento almacenado.

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. Los procedimientos y las referencias a código administrado se pueden anidar hasta 32 niveles. 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. Los métodos que se invocan desde el código administrado no cuentan para este límite de niveles de anidamiento. 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.

Si se intenta superar el nivel máximo de anidamiento, se producirá un error en toda la cadena de llamada. Puede usar la función @@NESTLEVEL para devolver el nivel de anidamiento de la ejecución del procedimiento almacenado actual.

Interoperabilidad

El Motor de base de datos guarda los valores de SET QUOTED_IDENTIFIER y de SET ANSI_NULLS cuando se crea o modifica un procedimiento de Transact-SQL. Estos valores originales se usan cuando se ejecuta el procedimiento. 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.

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. 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. 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. A continuación, el valor se restaura al que tenía cuando se llamó al procedimiento. Esto permite que clientes individuales establezcan las opciones deseadas sin afectar a la lógica del procedimiento.

En un procedimiento se puede especificar cualquier instrucción SET, excepto SET SHOWPLAN_TEXT y SET SHOWPLAN_ALL. Éstas deben ser las únicas instrucciones del lote. La opción SET elegida permanece vigente durante la ejecución del procedimiento y, a continuación, revierte a su valor anterior.

[!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. Por ejemplo, si la a variable se define como char(3) y después se establece en un valor de más de tres caracteres, los datos se truncan en el tamaño definido y la instrucción INSERT o UPDATE se ejecuta correctamente.

Limitaciones y restricciones

La instrucción CREATE PROCEDURE no se puede combinar con otras instrucciones Transact-SQL en un único lote.

Las siguientes instrucciones no se pueden usar en ninguna parte del cuerpo de un procedimiento almacenado.

CREATE AGGREGATE

CREATE SCHEMA

SET SHOWPLAN_TEXT

CREATE DEFAULT

CREATE o ALTER TRIGGER

SET SHOWPLAN_XML

CREATE o ALTER FUNCTION

CREATE o ALTER VIEW

USE database_name

CREATE o ALTER PROCEDURE

SET PARSEONLY

CREATE RULE

SET SHOWPLAN_ALL

Un procedimiento puede hacer referencia a tablas que aún no existan. En el momento de la creación, solo se realiza la comprobación de la sintaxis. El procedimiento no se compila hasta que se ejecute por primera vez. Solamente durante la compilación se resuelven todos los objetos a los que se haga referencia en el procedimiento. Por tanto, se puede crear un procedimiento con la sintaxis correcta que haga referencia a tablas que todavía no existan; no obstante, el procedimiento provocará un error en tiempo de ejecución si las tablas a las que hace referencia no existen.

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. Sin embargo, puede pasar una función como una variable, como se muestra en el ejemplo siguiente.

-- 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 Server, dichos cambios no se pueden revertir. Los procedimientos remotos no intervienen en las transacciones.

Para que el Motor de base de datos 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:

  • Ser declarado un método estático.

  • Recibir el mismo número de parámetros que el número de parámetros del procedimiento.

  • Usar tipos de parámetros compatibles con los tipos de datos de los parámetros correspondientes del procedimiento de SQL Server. Para obtener más información acerca de la correspondencia de tipos de datos de SQL Server y tipos de datos de .NET Framework, vea Asignar datos de parámetros CLR.

Metadatos

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.

Vista

Descripción

sys.sql_modules

Devuelve la definición de un procedimiento de Transact-SQL. El texto de un procedimiento creado con la opción ENCRYPTION no se puede ver mediante la vista de catálogo sys.sql_modules.

sys.assembly_modules

Devuelve información sobre un procedimiento CLR.

sys.parameters

Devuelve información sobre los parámetros definidos en un procedimiento.

sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities

Devuelve los objetos a los que hace referencia un procedimiento.

Para calcular el tamaño de un procedimiento compilado, use los siguientes contadores del Monitor de rendimiento.

Nombre del objeto del Monitor de rendimiento

Nombre del contador del Monitor de rendimiento

SQLServer: Plan Cache

Frecuencia de aciertos de caché

 

Páginas de caché

 

Recuentos de objetos de caché*

* Estos contadores están disponibles para varias categorías de objetos de caché, incluidos Transact-SQL ad hoc, Transact-SQL preparados, procedimientos, desencadenadores, etc. Para obtener más información, vea Plan Cache (objeto de SQL Server).

Seguridad

Permisos

Requiere 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 requiere la pertenencia al rol fijo de base de datos db_ddladmin.

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.

Ejemplos

Categoría

Elementos de sintaxis ofrecidos

Sintaxis básica

CREATE PROCEDURE

Pasar parámetros

@parameter • = valor predeterminado • OUTPUT • tipo de parámetro con valores de tabla • CURSOR VARYING

Modificar datos usando un procedimiento almacenado

UPDATE

Tratamiento de errores

TRY…CATCH

Ofuscar la definición del procedimiento

WITH ENCRYPTION

Forzar la recompilación del procedimiento

WITH RECOMPILE

Establecer el contexto de seguridad

EXECUTE AS

Sintaxis básica

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.

A.Crear un procedimiento simple Transact-SQL

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. Este procedimiento no usa ningún parámetro. En el ejemplo se muestran a continuación tres métodos de ejecutar el procedimiento.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

El procedimiento uspGetEmployees se puede ejecutar de estas formas:

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

B.Devolver más de un conjunto de resultados

El procedimiento siguiente devuelve dos conjuntos de resultados.

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

C.Crear un procedimiento almacenado CLR

En el ejemplo siguiente se crea el procedimiento GetPhotoFromDB que hace referencia al método GetPhotoFromDB de la clase LargeObjectBinary del ensamblado HandlingLOBUsingCLR . Antes de crear el procedimiento, el ensamblado HandlingLOBUsingCLR se registra en la base de datos local.

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ámetros

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.

A.Crear un procedimiento con parámetros de entrada

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. Este procedimiento solo acepta coincidencias exactas de los parámetros pasados.

USE AdventureWorks2012;
GO
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

El procedimiento uspGetEmployees se puede ejecutar de estas formas:

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';

B.Usar un procedimiento con parámetros comodín

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. Este patrón de procedimiento coincide con los parámetros pasados o, si estos no se proporcionan, usa los valores predeterminados (apellidos que comienzan por la letra D).

USE AdventureWorks2012;
GO
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

El procedimiento uspGetEmployees2 se puede ejecutar en muchas combinaciones. Aquí se muestran solo algunas combinaciones posibles.

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%';

C.Usar parámetros OUTPUT

En el ejemplo siguiente se crea el procedimiento uspGetList. Este procedimiento devuelve una lista de productos cuyos precios no superan una cantidad especificada. El ejemplo se muestra con varias instrucciones SELECT y varios parámetros OUTPUT. Los parámetros OUTPUT permiten a un procedimiento externo, un lote o más de una instrucción Transact-SQL tener acceso a un conjunto de valores durante la ejecución del procedimiento.

USE AdventureWorks2012;
GO
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 Works (bicicletas) que cuestan menos de $700. Los parámetros OUTPUT, @Cost y @ComparePrices se utilizan con el lenguaje de control de flujo para devolver un mensaje en la ventana Mensajes.

[!NOTA]

La variable OUTPUT debe definirse al crear el procedimiento y también al utilizar la variable. Los nombres de parámetro y de variable no tienen por qué coincidir; sin embargo, el tipo de datos y la posición de los parámetros deben coincidir, a menos que se use @ListPrice = variable.

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)))+'.'

Este es el conjunto de resultados parciales:

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.

D.Usar un parámetro con valores de tabla

En el ejemplo siguiente se usa un tipo de parámetro con valores de tabla para insertar varias filas en una tabla. 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. El procedimiento almacenado usa los valores para insertar varias filas en una tabla.

USE AdventureWorks2012;
GO

/* 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

E.Usar un parámetro de cursor OUTPUT

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.

Primero, crea el procedimiento que declara y, a continuación, abre un cursor en la tabla Currency:

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
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.

USE AdventureWorks2012;
GO
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 usando un procedimiento almacenado

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.

A.Usar UPDATE en un procedimiento almacenado

En el ejemplo siguiente se usa una instrucción UPDATE en un procedimiento almacenado. El procedimiento toma un parámetro de entrada @NewHours y un parámetro de salida @RowCount. El valor del parámetro @NewHours se utiliza en la instrucción UPDATE para actualizar la columna VacationHours de la tabla HumanResources.Employee. El parámetro de salida @RowCount se usa para devolver el número de filas afectadas a una variable local. Se usa una expresión CASE en la cláusula SET para determinar de forma condicional el valor establecido para 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.

USE AdventureWorks2012;
GO
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 errores

En los ejemplos de esta sección se muestran métodos de controlar errores que pueden producirse cuando se ejecuta el procedimiento almacenado.

Usar 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.

USE AdventureWorks2012;
GO

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 procedimiento

En los ejemplos de esta sección se muestra cómo ofuscar la definición del procedimiento almacenado.

A.Usar la opción WITH ENCRYPTION

En el ejemplo siguiente se crea el procedimiento HumanResources.uspEncryptThis.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
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 los ejemplos siguientes.

Ejecute sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

El conjunto de resultados es el siguiente.

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

Realice una consulta directamente a la vista de catálogo sys.sql_modules:

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

El conjunto de resultados es el siguiente.

definition

--------------------------------

NULL

Forzar la recompilación del procedimiento

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.

A.Usar la opción WITH RECOMPILE

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.

USE AdventureWorks2012;
GO
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;
GO

Establecer el contexto de seguridad

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.

A.Usar la cláusula EXECUTE AS

En el ejemplo siguiente se muestra el uso de la cláusula EXECUTE AS para especificar el contexto de seguridad en el que se puede ejecutar un procedimiento. En el ejemplo, la opción CALLER especifica que el procedimiento se puede ejecutar en el contexto del usuario que lo llama.

USE AdventureWorks2012;
GO
IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
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

B.Crear conjuntos de permisos personalizados

En el ejemplo siguiente se usa EXECUTE AS para crear permisos personalizados para una operación de base de datos. Algunas operaciones, como TRUNCATE TABLE, no tienen permisos que se puedan conceder. 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.

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

Vea también

Referencia

ALTER PROCEDURE (Transact-SQL)

Lenguaje de control de flujo (Transact-SQL)

Tipos de datos (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

DROP PROCEDURE (Transact-SQL)

EXECUTE (Transact-SQL)

EXECUTE AS (Transact-SQL)

sp_procoption (Transact-SQL)

sp_recompile (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.parameters (Transact-SQL)

sys.procedures (Transact-SQL)

sys.sql_expression_dependencies (Transact-SQL)

sys.assembly_modules (Transact-SQL)

sys.numbered_procedures (Transact-SQL)

sys.numbered_procedure_parameters (Transact-SQL)

OBJECT_DEFINITION (Transact-SQL)

sys.dm_sql_referenced_entities (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

Conceptos

Cursores

Procedimientos almacenados (motor de base de datos)

Crear un procedimiento almacenado

Usar parámetros con valores de tabla (motor de base de datos)