CREATE PROCEDURE (Transact-SQL)

Crea un procedimiento almacenado. Un procedimiento almacenado es una colección guardada de instrucciones de Transact-SQL o una referencia a un método de Common Language Runtime (CLR) de Microsoft .NET Framework que puede aceptar y devolver los parámetros proporcionados por el usuario. Los procedimientos se pueden crear para uso permanente o para uso temporal en una sesión, un procedimiento local temporal, o para su uso temporal en todas las sesiones, un procedimiento temporal global.

Los procedimientos almacenados también se pueden crear de modo que se ejecuten de forma automática al iniciarse una instancia de SQL Server.

Icono de vínculo a temasConvenciones de sintaxis de Transact-SQL

Sintaxis

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 { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

Argumentos

  • schema_name
    Es el nombre del esquema al que pertenece el procedimiento.

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

    Se recomienda no utilizar el prefijo sp_ en el nombre del procedimiento. SQL Server utiliza este prefijo para designar a los procedimientos almacenados del sistema. Para obtener más información, vea Crear procedimientos almacenados (motor de base de datos).

    Los procedimientos temporales locales o globales se pueden crear anteponiendo el signo de número (#) a procedure_name (#procedure_name) para los procedimientos temporales locales y dos signos de número para los procedimientos temporales globales (##procedure_name). No es posible especificar nombres temporales para los procedimientos almacenados CLR.

    El nombre completo de un procedimiento almacenado o un procedimiento almacenado temporal global, incluidas ##, no puede superar los 128 caracteres. El nombre completo de un procedimiento almacenado temporal local, incluidas #, no puede superar los 116 caracteres.

  • **;**number
    Es un entero opcional que se utiliza para agrupar procedimientos que tengan el mismo nombre. Estos procedimientos agrupados se pueden quitar juntos mediante una instrucción DROP PROCEDURE. Por ejemplo, una aplicación denominada orders puede utilizar procedimientos denominados orderproc;1, orderproc;2, etc. La instrucción DROP PROCEDURE orderproc quita el grupo completo. Si el nombre contiene identificadores delimitados, el número no debe incluirse como parte del identificador; sólo utilice el delimitador adecuado alrededor de procedure_name.

    Los procedimientos almacenados numerados tienen las siguientes restricciones:

    • No permiten el uso de tipos de datos xml o tipos definidos por el usuario CLR.

    • No permiten crear una guía de plan en un procedimiento almacenado numerado.

    [!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.

  • **@**parameter
    Es un parámetro del procedimiento. En una instrucción CREATE PROCEDURE se pueden declarar uno o más parámetros. El usuario debe proporcionar el valor de cada parámetro declarado cuando se llama al procedimiento, a menos que se haya definido un valor predeterminado para el parámetro o se haya establecido en el mismo valor que otro parámetro. Un procedimiento almacenado puede tener un máximo de 2.100 parámetros. Si un procedimiento contiene parámetros con valores de tabla y el parámetro no está en la llamada, se pasa un valor predeterminado de tabla vacía.

    Especifique un nombre de parámetro con un signo (@) como primer carácter. El nombre del parámetro se debe ajustar a las reglas de los identificadores. Los parámetros son locales para el procedimiento; los mismos nombres de parámetro se pueden utilizar en otros procedimientos. De manera predeterminada, los parámetros sólo pueden ocupar el lugar de expresiones constantes; no se pueden utilizar en lugar de nombres de tabla, nombres de columna o nombres de otros objetos de base de datos. Para obtener más información, vea EXECUTE (Transact-SQL).

    No es posible declarar parámetros si se ha especificado FOR REPLICATION.

  • [ type_schema_name**.** ] data_type
    Es el tipo de datos del parámetro y el esquema al que pertenece. Se pueden utilizar todos los tipos de datos como un parámetro de un procedimiento almacenado Transact-SQL. Puede utilizar un tipo de tabla definido por el usuario para declarar un parámetro con valores de tabla como parámetro para un procedimiento almacenado Transact-SQL. Los parámetros con valores de tabla sólo se pueden especificar como parámetros de entrada y deben ir acompañados de una palabra clave READONLY. El tipo de datos cursor sólo se puede usar en parámetros OUTPUT. Cuando se especifica un tipo de datos cursor, deben especificarse también las palabras clave VARYING y OUTPUT. Con el tipo de datos cursor, es posible especificar varios parámetros de salida.

    En los procedimientos almacenados CLR, no es posible especificar como parámetros los tipos char, varchar, text, ntext, image, cursor, table, ni tipos de tabla definidos por el usuario. 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).

    Si el tipo de datos del parámetro es un tipo definido por el usuario CLR, es necesario disponer de permiso EXECUTE en el tipo.

    Si no se especifica el parámetro type_schema_name, el SQL Server Database Engine (Motor de base de datos de SQL Server) hace referencia a type_name en el siguiente orden:

    • Los tipos de datos del sistema de SQL Server.

    • El esquema predeterminado del usuario actual en la base de datos actual.

    • El esquema dbo en la base de datos actual.

    En los procedimientos almacenados numerados, el tipo de datos no puede ser un tipo xml o definido por el usuario CLR.

  • VARYING
    Especifica el conjunto de resultados admitido como un parámetro de salida. Este parámetro es creado de forma dinámica por el procedimiento almacenado y su contenido puede variar. Sólo se aplica a los parámetros de tipo cursor.

  • default
    Es un valor predeterminado para el parámetro. Si se define un valor default, el procedimiento se puede ejecutar sin especificar un valor para ese parámetro. El valor predeterminado debe ser una constante o puede ser NULL. Si el procedimiento utiliza el parámetro con la palabra clave LIKE, puede incluir los siguientes caracteres comodín: % _ [] y [^].

    [!NOTA]

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

  • OUTPUT
    Indica que se trata de un parámetro de salida. El valor de esta opción puede devolverse a la instrucción EXECUTE que llama. Utilice los parámetros OUTPUT para devolver valores al autor de la llamada del procedimiento. Los parámetros text, ntext e image no se pueden utilizar como parámetros OUTPUT, a menos que se trate de un procedimiento CLR. Un parámetro de salida que utilice la palabra clave OUTPUT puede ser un marcador de posición de cursor, a menos que el procedimiento sea un procedimiento CLR. Un tipo de tabla definido por el usuario no se puede especificar como parámetro OUTPUT de un procedimiento almacenado.

  • 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 de tabla definido por el usuario, se debe especificar READONLY.

  • RECOMPILE
    Indica que el Database Engine (Motor de base de datos) no almacena en caché un plan para este procedimiento y que éste se compila en tiempo de ejecución. Esta opción no se puede utilizar cuando se especifica FOR REPLICATION. No es posible especificar RECOMPILE en los procedimientos almacenados CLR.

    Para indicar al Database Engine (Motor de base de datos) que descarte planes para las consultas individuales de un procedimiento almacenado, utilice la sugerencia de consulta RECOMPILE. Para obtener más información, vea Sugerencias de consulta (Transact-SQL). Utilice la sugerencia de consulta RECOMPILE cuando se utilicen valores atípicos o temporales en sólo un subconjunto de consultas que pertenece al procedimiento almacenado.

  • ENCRYPTION
    Indica que SQL Server convertirá el texto original de la instrucción CREATE PROCEDURE en un formato ofuscado. La salida de la ofuscación no es directamente visible en ninguna de las vistas de catálogo de SQL Server. Los usuarios que no disponen de acceso a las tablas del sistema o a los archivos de base de datos no pueden recuperar el texto protegido. 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 pueden 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 en los procedimientos almacenados 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 almacenado.

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

  • FOR REPLICATION
    Especifica que los procedimientos almacenados creados para la replicación no se pueden ejecutar en el suscriptor. Se utiliza un procedimiento almacenado creado con la opción FOR REPLICATION como filtro de procedimiento almacenado y sólo se ejecuta durante la replicación. No es posible declarar parámetros si se ha especificado FOR REPLICATION. No es posible especificar FOR REPLICATION en los procedimientos almacenados CLR. La opción RECOMPILE se pasa por alto en los procedimientos creados con FOR REPLICATION.

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

  • <sql_statement>
    Una o más instrucciones Transact-SQL que se van a incluir en el procedimiento. Para obtener más información sobre algunas limitaciones aplicables, vea la sección de Notas.

  • EXTERNAL NAME assembly_name**.class_name.method_name
    Especifica el método de un ensamblado de .NET Framework para que un procedimiento almacenado CLR haga referencia a él. class_name debe ser un identificador válido de SQL Server que 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 clase se debe delimitar utilizando corchetes ([** ]) o comillas (""). El método especificado debe ser un método estático de la clase.

    [!NOTA]

    De manera predeterminada, SQL Server no puede ejecutar código CLR. Es posible crear, modificar y quitar objetos de base de datos que hagan referencia a módulos CLR; no obstante, no es posible ejecutar esas referencias en SQL Server hasta que se haya habilitado la opción clr enabled. Para habilitar la opción, utilice sp_configure.

Notas

No hay un tamaño máximo predefinido para un procedimiento almacenado.

Un procedimiento almacenado definido por el usuario sólo se puede crear en la base de datos actual. Los procedimientos temporales constituyen una excepción a esta regla, puesto que siempre se crean en tempdb. Si no se especifica el nombre de esquema, se utiliza el esquema predeterminado del usuario que está creando el procedimiento. Para obtener más información acerca de los esquemas, vea Separación de esquemas de usuario.

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

De manera predeterminada, los parámetros admiten valores NULL. Si se pasa un valor de parámetro NULL y ese parámetro se utiliza en una instrucción CREATE TABLE o ALTER TABLE en la que la columna a la que se hace referencia no admite valores NULL, el Database Engine (Motor de base de datos) genera un error. Para impedir que se pase un valor NULL a una columna que no admite valores NULL, agregue lógica de programación al procedimiento o utilice un valor predeterminado para la columna mediante la palabra clave DEFAULT de CREATE TABLE o ALTER TABLE.

Se recomienda especificar de forma explícita NULL o NOT NULL para cada columna de una tabla temporal. Las opciones ANSI_DFLT_ON y ANSI_DFLT_OFF controlan la forma en la que el Database Engine (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 almacenado con valores distintos para estas opciones a los que utilizó la conexión que creó el procedimiento, las columnas de la tabla creada para la segunda conexión pueden ser tener distinta nulabilidad y exhibir diferentes comportamientos. Si se especifica NULL o NOT NULL explícitamente para cada columna, las tablas temporales se crean con la misma nulabilidad para todas las conexiones que ejecutan el procedimiento almacenado.

Utilizar las opciones de SET

El Database Engine (Motor de base de datos) guarda los valores de SET QUOTED_IDENTIFIER y de SET ANSI_NULLS cuando se crea o modifica un procedimiento almacenado Transact-SQL. Estos valores originales se utilizan cuando se ejecuta el procedimiento almacenado. Por tanto, cualquier valor de sesión de cliente de SET QUOTED_IDENTIFIER y SET ANSI_NULLS se omite durante la ejecución del procedimiento almacenado. 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 almacenado. Si la lógica del procedimiento almacenado 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 almacenado, el valor permanece en vigor sólo hasta que se completa la ejecución del procedimiento almacenado. A continuación, el valor se restaura al que tenía cuando se llamó al procedimiento almacenado. Esto permite a los clientes individuales establecer las opciones deseadas sin afectar a la lógica del procedimiento almacenado.

[!NOTA]

ANSI_WARNINGS no se respeta cuando se pasan parámetros en un procedimiento almacenado, una función definida por el usuario o cuando se declaran y establecen variables en una instrucción por lotes. Por ejemplo, si una variable se define como char(3) y, a continuación, se establece en un valor con más de tres caracteres, los datos se truncan al tamaño definido y la instrucción INSERT o UPDATE se ejecuta correctamente.

Utilizar parámetros con procedimientos almacenados CLR

Los parámetros de un procedimiento almacenado CLR pueden ser de cualquiera de los tipos de datos escalares del sistema de SQL Server.

Para que el Database Engine (Motor de base de datos) haga referencia al método correcto cuando está sobrecargado en .NET Framework, el método indicado en <method_specifier> debe tener las siguientes características:

  • Ser declarado un método estático.

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

  • No ser un constructor ni un destructor de su clase.

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

  • No devolver ningún valor o devolver un valor de tipo SQLInt32, SQLInt16, System.Int32 o System.Int16.

  • Devolver sus parámetros por referencia y no por valor siempre que se haya especificado OUTPUT para una declaración de parámetros específica.

Obtener información acerca de procedimientos almacenados

Para mostrar la definición de un procedimiento almacenado Transact-SQL, utilice la vista de catálogo sys.sql_modules de la base de datos en la que se encuentra el procedimiento.

Por ejemplo:

USE AdventureWorks;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';

[!NOTA]

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

Para obtener un informe sobre los objetos a los que hace referencia un procedimiento, consulte la vista de catálogo sys.sql_expression_dependencies o use sys.dm_sql_referenced_entities y sys.dm_sql_referencing_entities.

Para mostrar información acerca de los procedimientos almacenados CLR, utilice la vista de catálogo sys.assembly_modules de la base de datos en la que se encuentra el procedimiento.

Para mostrar información acerca de los parámetros definidos en un procedimiento almacenado, utilice la vista de catálogo sys.parameters de la base de datos en la que se encuentra el procedimiento.

Resolución diferida de nombres

Es posible crear procedimientos almacenados que hagan referencia a tablas que aún no existen. En el momento de la creación, sólo se realiza la comprobación de la sintaxis. El procedimiento almacenado no se compila hasta que se ejecuta por primera vez. Solamente durante la compilación se resuelven todos los objetos a los que se hace referencia en el procedimiento almacenado. Por tanto, se puede crear un procedimiento almacenado con la sintaxis correcta que haga referencia a tablas que todavía no existen; no obstante, el procedimiento almacenado provocará un error en tiempo de ejecución si las tablas a las que hace referencia no existen. Para obtener más información, vea Resolución diferida de nombres y compilación.

Ejecutar procedimientos almacenados

Cuando ejecute un procedimiento almacenado definido por el usuario, ya sea en un lote o en un módulo como un procedimiento almacenado definido por el usuario o una función, se recomienda calificar el nombre del procedimiento almacenado con un nombre de esquema.

Es posible proporcionar los valores de los parámetros si se escribe un procedimiento almacenado que los acepte. El valor proporcionado puede ser una constante o una variable. No es posible especificar un nombre de función como valor de un parámetro. Las variables pueden ser variables definidas por el usuario o del sistema, como @@SPID.

Para obtener más información, vea Ejecutar procedimientos almacenados (motor de base de datos).

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 almacenado se puede volver a utilizar el plan ya generado si aún permanece en la caché de planes del Database Engine (Motor de base de datos). Para obtener más información, vea Almacenar en caché y volver a utilizar un plan de ejecución.

Parámetros que utilizan el tipo de datos cursor

Los procedimientos almacenados Transact-SQL sólo pueden utilizar el tipo de datos cursor para los parámetros OUTPUT. Si se especifica el tipo de datos cursor para un parámetro, se requieren los parámetros VARYING y OUTPUT. Si se especifica la palabra clave VARYING para un parámetro, el tipo de datos debe ser cursor y se debe especificar la palabra clave OUTPUT. Para obtener más información, vea Usar el tipo de datos cursor en un parámetro OUTPUT.

Procedimientos almacenados temporales

Database Engine (Motor de base de datos) admite dos tipos de tablas temporales: locales y globales. Un procedimiento temporal local sólo es visible para la conexión que lo creó. Un procedimiento temporal global está disponible para todas las conexiones. Los procedimientos temporales locales se quitan automáticamente al final de la sesión actual. Los procedimientos temporales globales se quitan al final de la última sesión que utiliza el procedimiento. Para obtener más información, vea Crear procedimientos almacenados (motor de base de datos).

Ejecutar procedimientos almacenados automáticamente

Cuando se inicia SQL Server, se pueden ejecutar automáticamente uno o más procedimientos almacenados. Los procedimientos almacenados deben ser creados por el administrador del sistema en la base de datos maestra y ejecutados bajo la función fija del servidor sysadmin como un proceso en segundo plano. Los procedimientos no pueden tener ningún parámetro de entrada o salida. Para obtener más información, vea Ejecución automática de procedimientos almacenados.

Anidamiento de procedimientos almacenados

Los procedimientos almacenados se pueden anidar. Esto significa que un procedimiento almacenado puede llamar a otro. El nivel de anidamiento aumenta cuando el procedimiento llamado inicia la ejecución y disminuye cuando el procedimiento llamado finaliza la ejecución. Los procedimientos almacenados se pueden anidar hasta en 32 niveles. Para obtener más información, vea Anidar procedimientos almacenados.

Para estimar el tamaño de un procedimiento almacenado compilado, utilice 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 secuencias sql ad hoc, secuencias sql preparadas, procedimientos, desencadenadores, etc.

Para obtener más información, vea Plan Cache (objeto de SQL Server).

Limitaciones de <sql_statement>

En un procedimiento almacenado 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 almacenado y, después, vuelve a su valor anterior.

En un procedimiento almacenado, los nombres de objeto utilizados en todas las instrucciones de lenguaje de definición de datos (DDL), como las instrucciones CREATE, ALTER o DROP, las instrucciones DBCC, las instrucciones EXECUTE y de SQL dinámico, deben completarse con el nombre del esquema de objetos si usuarios distintos al propietario del procedimiento almacenado van a utilizar dicho procedimiento. Para obtener más información, vea Diseñar procedimientos almacenados (motor de base de datos).

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.

Para los procedimientos almacenados CLR, requiere la propiedad del ensamblado al que se hace referencia en <method_specifier> o el permiso REFERENCES en ese ensamblado.

Ejemplos

A. Utilizar un procedimiento simple

El siguiente procedimiento almacenado devuelve todos los empleados (nombre y apellidos), sus puestos y los nombres de sus departamentos a partir de una vista. Este procedimiento almacenado no utiliza ningún parámetro.

USE AdventureWorks;
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 almacenado 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. Utilizar un procedimiento simple con parámetros

El siguiente procedimiento almacenado sólo devuelve el empleado especificado (nombre y apellidos), su puesto y el nombre de su departamento a partir de una vista. Este procedimiento almacenado acepta coincidencias exactas de los parámetros pasados.

USE AdventureWorks;
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 almacenado 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';

C. Utilizar un procedimiento simple con parámetros comodín

El siguiente procedimiento almacenado sólo devuelve los empleados especificados (nombre y apellidos), sus puestos y los nombres de sus departamentos a partir de una vista. Este patrón de procedimiento almacenado coincide con los parámetros pasados o, si éstos no se proporcionan, utiliza los valores predeterminados (apellidos que comienzan por la letra D).

USE AdventureWorks;
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 almacenado uspGetEmployees2 se puede ejecutar en muchas combinaciones. Aquí se muestran sólo algunas de ellas:

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

D. Devolver más de un conjunto de resultados

El siguiente procedimiento almacenado devuelve dos conjuntos de resultados.

USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults 
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO

E. Utilizar parámetros OUTPUT

En el siguiente ejemplo se crea el procedimiento almacenado 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 AdventureWorks;
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. El nombre del parámetro y de la 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 utilice @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)))+'.'

Éste 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.

F. Utilizar 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 caché o en memoria.

USE AdventureWorks;
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.VendorID = pv.VendorID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

G. Utilizar la opción WITH ENCRYPTION

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

USE AdventureWorks;
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 EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

La opción WITH ENCRYPTION evita que se devuelva la definición del procedimiento almacenado, como se muestra en los siguientes ejemplos.

Ejecute sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';

Éste es el conjunto de resultados.

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

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

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

Éste es el conjunto de resultados.

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

(1 row(s) affected)

H. Utilizar la resolución diferida de nombres

En el siguiente ejemplo se crea el procedimiento uspProc1. Utiliza la resolución diferida de nombres. El procedimiento almacenado se crea aunque la tabla a la que se hace referencia no exista en el momento de la compilación. Sin embargo, la tabla debe existir cuando se ejecute el procedimiento.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SET NOCOUNT ON;
    SELECT column1, column2 FROM table_does_not_exist
GO

Para comprobar que el procedimiento almacenado se ha creado, ejecute la siguiente consulta:

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

Éste es el conjunto de resultados.

definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

I. Utilizar la cláusula EXECUTE AS

En el siguiente 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 almacenado. En el ejemplo, la opción CALLER especifica que el procedimiento se puede ejecutar en el contexto del usuario que lo llama.

USE AdventureWorks;
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 'Credit Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.VendorID = pv.VendorID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

J. Crear un procedimiento almacenado CLR

En el siguiente ejemplo se crea el procedimiento almacenado GetPhotoFromDB que hace referencia al método GetPhotoFromDB de la clase LargeObjectBinary del ensamblado HandlingLOBUsingCLR . Antes de que se cree el procedimiento almacenado, 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

K. Utilizar un parámetro de cursor OUTPUT

Los parámetros de cursor OUTPUT se utilizan para volver a pasar un cursor que es local en un procedimiento almacenado al lote, procedimiento almacenado o desencadenador que llama.

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

USE AdventureWorks;
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, recupera las filas desde el cursor.

USE AdventureWorks;
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

Vea también

Tasks

Referencia

Conceptos

Otros recursos