Los procedimientos almacenados de Microsoft SQL Server son similares a los procedimientos de otros lenguajes de programación en el sentido de 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 realiza una llamada para indicar si la operación se ha realizado correctamente o se han producido errores (y el motivo de éstos).

Puede utilizar la instrucción EXECUTE de Transact-SQL para ejecutar un procedimiento almacenado. Los procedimientos almacenados difieren de las funciones en que no devuelven valores en lugar de sus nombres ni pueden utilizarse directamente en una expresión.

Utilizar procedimientos almacenados en SQL Server en vez de programas Transact-SQL almacenados localmente en equipos cliente presenta las siguientes ventajas:

Se registran en el servidor. Pueden incluir atributos de seguridad (como permisos) y cadenas de propiedad;

además se les pueden asociar certificados.Los usuarios pueden disponer de permiso para ejecutar un procedimiento almacenado sin necesidad de contar con permisos directos en los objetos a los que se hace referencia en el procedimiento.

Mejoran la seguridad de la aplicación.

Permiten una programación modular. Puede crear el procedimiento una vez y llamarlo desde el programa tantas veces como desee. Así, puede mejorar el mantenimiento de la aplicación y permitir que las aplicaciones tengan acceso a la base de datos de manera uniforme.

Constituyen código con nombre que permite el enlace diferido.Esto proporciona un nivel de direccionamiento indirecto que facilita la evolución del código.

Pueden reducir el tráfico de red. Una operación que necesite centenares de líneas de código Transact-SQL puede realizarse mediante una sola instrucción que ejecute el código en un procedimiento, en vez de enviar cientos de líneas de código por la red.

n Microsoft SQL Server 2005 hay disponibles varios tipos de procedimientos almacenados. En este tema se describen de forma resumida los tipos de procedimientos almacenados y se incluyen ejemplos de cada uno de ellos.

Procedimientos almacenados definidos por el usuario

Los procedimientos almacenados son módulos o rutinas que encapsulan código para su reutilización. Un procedimiento almacenado puede incluir parámetros de entrada, devolver resultados tabulares o escalares y mensajes para el cliente, invocar instrucciones de lenguaje de definición de datos (DDL) e instrucciones

de lenguaje de manipulación de datos (DML), así como devolver parámetros de salida. En SQL Server 2005 existen dos tipos de procedimientos almacenados: Transact-SQL o CLR.

Transact-SQL Un procedimiento almacenado Transact-SQL es una colección guardada de

instrucciones Transact-SQL que puede tomar y devolver los parámetros proporcionados por el usuario. Por ejemplo, un procedimiento almacenado puede contener las instrucciones necesarias para insertar una nueva fila en una o más tablas según la información suministrada por la aplicación cliente o es posible que el procedimiento almacenado devuelva datos de la base de datos a la aplicación cliente. Por ejemplo, una aplicación Web de comercio electrónico puede utilizar un procedimiento almacenado para devolver información acerca de determinados productos en función de los criterios de búsqueda especificados por el usuario en línea.

CLR Un procedimiento almacenado CLR es una referencia a un método Common

Language Runtime (CLR) de Microsoft .NET Framework que puede aceptar y devolver parámetros suministrados por el usuario. Se implementan como métodos públicos y estáticos en una clase de un ensamblado de .NET Framework. Para obtener más información, vea Procedimientos almacenados CLR (en inglés).

Procedimientos almacenados extendidos

Importante: 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. En su lugar, utilice la integración CLR.

Los procedimientos almacenados extendidos le permiten crear sus propias rutinas externas en un lenguaje de programación como pueda ser C. Los procedimientos almacenados extendidos son DLL que una instancia de Microsoft SQL Server puede cargar y ejecutar dinámicamente. Los procedimientos almacenados extendidos se ejecutan directamente en el espacio de dirección de una instancia de SQL Server y se programan con la API Procedimiento almacenado extendido de SQL Server.

Nota: La integración CLR es una alternativa más consolidada y segura para escribir procedimientos almacenados extendidos.

Procedimientos almacenados del sistema

Muchas de las actividades administrativas en SQL Server 2005 se realizan mediante un tipo especial de procedimiento conocido como procedimiento almacenado del sistema. Por ejemplo, sys.sp_changedbowner es un procedimiento almacenado del sistema. Los procedimientos almacenados del sistema se almacenan físicamente en la base de datos Resource e incluyen el prefijo sp_. Los procedimientos almacenados del sistema aparecen de forma lógica en el esquema sys de cada base de datos definida por el usuario y el sistema. En SQL Server 2005, los permisos GRANT, DENY y REVOKE se pueden aplicar a los procedimientos almacenados del sistema. Para obtener una

lista completa de los procedimientos almacenados del sistema, vea Procedimientos almacenados del sistema (Transact-SQL).

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

Casi cualquier código Transact-SQL que puede escribirse como un lote puede utilizarse para crear un procedimiento almacenado.

Reglas para diseñar procedimientos almacenados

Entre las reglas para diseñar procedimientos almacenados se incluyen las siguientes:

La propia definición de CREATE PROCEDURE puede incluir cualquier número y tipo de instrucciones SQL, excepto las indicadas a continuación. No pueden utilizarse en ninguna parte de un procedimiento almacenado.CREATE AGGREGATE CREATE RULECREATE DEFAULT CREATE SCHEMACREATE o ALTER FUNCTION CREATE o ALTER TRIGGERCREATE o ALTER PROCEDURE CREATE o ALTER VIEWSET PARSEONLY SET SHOWPLAN_ALLSET SHOWPLAN_TEXT SET SHOWPLAN_XMLUSE database_name  

Puede crear otros objetos de base de datos dentro de un procedimiento almacenado. Puede hacer referencia a un objeto creado en el mismo procedimiento almacenado, siempre que se haya creado antes de hacer referencia a él.

Puede hacer referencia a tablas temporales dentro de un procedimiento almacenado.

Si crea una tabla temporal local dentro de un procedimiento almacenado, ésta existirá únicamente para los fines del procedimiento y desaparecerá cuando éste finalice.

Si ejecuta un procedimiento almacenado que llama a otro procedimiento almacenado, este último puede tener acceso a todos los objetos creados por el primero, incluidas las tablas temporales.

Si ejecuta un procedimiento almacenado remoto que realiza cambios en una instancia remota de Microsoft SQL Server 2005, los cambios no se pueden revertir. Los procedimientos almacenados remotos no intervienen en las transacciones.

El número máximo de parámetros en un procedimiento almacenado es de 2100.

El número máximo de variables locales en un procedimiento almacenado está limitado únicamente por la memoria disponible.

En función de la memoria disponible, el tamaño máximo de un procedimiento almacenado es de 128 megabytes (MB).

Calificar nombres dentro de procedimientos almacenados

Dentro de un procedimiento almacenado, los nombres de los objetos utilizados en instrucciones (por ejemplo, SELECT o INSERT), que no se califican por esquema adoptan el valor predeterminado del esquema del procedimiento en cuestión. Si un usuario que crea un procedimiento almacenado no califica el nombre de las tablas o las vistas a las que se hace referencia en las instrucciones SELECT, INSERT, UPDATE o DELETE dentro del mismo procedimiento, el acceso a esas tablas a través del procedimiento se restringe, de forma predeterminada, al creador de este último.

Si otros usuarios van a utilizar el procedimiento almacenado, los nombres de objetos utilizados con las instrucciones de lenguaje de definición de datos (DDL) como instrucciones CREATE, ALTER o DROP, instrucciones DBCC, instrucciones EXECUTE y SQL dinámicas, deben calificarse con el nombre del esquema de objeto. Si se especifica el nombre de esquema en estos objetos, se garantiza que el nombre indica el mismo objeto, independientemente de quién llama al procedimiento almacenado. Si no se especifica un nombre de esquema, SQL Server intenta resolver el nombre del objeto utilizando en primer lugar el esquema predeterminado del usuario que realiza la llamada o del usuario especificado en la cláusula EXECUTE AS y, en segundo lugar, el esquema de dbo.

Ofuscar las definiciones de procedimientos

Para ofuscar el texto original de la instrucción CREATE PROCEDURE, utilice la opción WITH ENCRYPTION. La salida de la protección no se puede ver directamente en ninguna de las vistas o tablas del sistema de SQL Server 2005. Los usuarios sin acceso a las tablas o vistas del sistema, o a los archivos de base de datos no pueden recuperar el texto protegido. Sin embargo, podrán tener acceso al texto los usuarios con privilegios que tengan acceso directo a los archivos de base de datos. Estos usuarios pueden aplicar ingeniería inversa a la ofuscación para recuperar el texto original de la definición del procedimiento almacenado.

El uso de la opción WITH ENCRYPTION impide que el procedimiento almacenado se publique como parte de la réplica de SQL Server.

Opciones de la instrucción SET

Database Engine (Motor de base de datos) guarda los valores de SET QUOTED_IDENTIFIER y de SET ANSI_NULLS cuando se crea o se altera 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 omitirá durante la ejecución del procedimiento almacenado. Las instrucciones SET QUOTED_IDENTIFIER y SET ANSI_NULLS que se producen en el procedimiento almacenado no afectan a la funcionalidad del mismo.

Otras opciones de SET, como SET ARITHABORT, SET ANSI_WARNINGS o SET ANSI_PADDINGS no se guardan cuando se crea o se altera 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 asegurar el valor adecuado. Cuando una instrucción SET se ejecuta desde un procedimiento almacenado, el valor permanece activo sólo hasta que se completa el procedimiento almacenado. A continuación, la configuración vuelve al valor 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: No se respeta ANSI_WARNINGS al pasar parámetros de un procedimiento almacenado o de una función definida por el usuario o al declarar y establecer variables en una instrucción de lote. 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.

Puede crear procedimientos almacenados mediante la instrucción CREATE PROCEDURE de Transact-SQL.

Antes de crearlos, tenga en cuenta lo siguiente:

Las instrucciones CREATE PROCEDURE no se pueden combinar con otras instrucciones SQL en el mismo lote.

Para crear procedimientos, debe disponer del permiso CREATE PROCEDURE en la base de datos y del permiso ALTER en el esquema donde se crea el procedimiento. En procedimientos almacenados CLR, debe ser propietario del ensamblado al que se hace referencia en <method_specifier> o disponer del permiso REFERENCES en dicho ensamblado.

Los procedimientos almacenados son objetos de ámbito de esquema y sus nombres deben ajustarse a las reglas para los identificadores.

Sólo puede crear un procedimiento almacenado en la base de datos actual.

Cuando cree un procedimiento almacenado, deberá especificar lo siguiente:

Todos los parámetros de entrada y de salida del lote o del procedimiento que realiza la llamada.

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

El valor de estado devuelto al lote o al procedimiento que realiza la llamada, a fin de indicar que la operación se ha realizado correctamente o que se ha producido un error (y el motivo del mismo).

Las instrucciones de control de errores necesarias para detectar y administrar posibles errores. Microsoft SQL Server 2005 presenta nuevas funciones de control de errores como ERROR_LINE y ERROR_PROCEDURE que se pueden especificar en el procedimiento almacenado. Para obtener más información, vea Usar TRY...CATCH en Transact-SQL.

Asignar nombre a los procedimientos almacenados

Se recomienda que no cree procedimientos almacenados con el prefijo sp_. SQL Server utiliza el prefijo sp_ para indicar procedimientos almacenados del sistema. El nombre que elija puede entrar en conflicto con algún procedimiento futuro del sistema. Si la aplicación utiliza referencias de nombre completo que no son de esquema y el nombre de su procedimiento entra en conflicto con un procedimiento del sistema, la aplicación generará un error ya que el nombre se enlaza con el procedimiento del sistema, no con el suyo.

Un procedimiento almacenado definido por el usuario, con el mismo nombre que un procedimiento almacenado del sistema y que no esté calificado o se encuentre en el esquema dbo no se ejecutará nunca; siempre se ejecutará el procedimiento almacenado del sistema. En el ejemplo siguiente se muestra este comportamiento.

CopiarUSE AdventureWorks;GOCREATE PROCEDURE dbo.sp_whoAS SELECT FirstName, LastName FROM Person.Contact;GOEXEC sp_who;EXEC dbo.sp_who;GODROP PROCEDURE dbo.sp_who;GO

Si se utiliza un calificador de esquema explícito también se consigue una ligera mejora del rendimiento. La resolución de nombres es un poco más rápida si Database Engine (Motor de base de datos) no tiene que buscar en varios esquemas para encontrar el procedimiento. Para obtener más información, vea Ejecutar procedimientos almacenados (motor de base de datos).

Procedimientos almacenados temporales

De forma similar a las tablas temporales, los procedimientos almacenados temporales (tanto privados como globales) se pueden crear agregando los prefijos # y ## delante del nombre del procedimiento. # denota un procedimiento almacenado temporal local; ## denota un procedimiento almacenado temporal global. Estos procedimientos dejan de existir cuando se cierra SQL Server.

Los procedimientos almacenados temporales son útiles para conectarse a versiones anteriores de SQL Server que no permiten volver a utilizar los planes de ejecución para instrucciones o lotes de Transact-SQL. Las aplicaciones que se conecten a SQL Server 2000 y versiones posteriores deben utilizar el procedimiento almacenado del sistema sp_executesql, en vez de procedimientos almacenados temporales. Sólo puede ejecutar un procedimiento temporal local la conexión que lo creó; el procedimiento se elimina automáticamente cuando se cierra la conexión.

Cualquier conexión puede ejecutar un procedimiento almacenado temporal global. Éste existe hasta que se cierra la conexión que el usuario utilizó para crearlo, y hasta que se completan todas las versiones del procedimiento que se estuvieran ejecutando mediante otras conexiones. Una vez cerrada la conexión que se utilizó para crear el procedimiento, éste ya no se puede volver a ejecutar. Sólo podrán finalizar las conexiones que hayan empezado a ejecutar el procedimiento.

Si se crea directamente en la base de datos tempdb un procedimiento almacenado sin el prefijo # o ##, el procedimiento en cuestión se eliminará automáticamente cuando se cierre SQL Server, ya que tempdb se vuelve a crear cada vez que se inicia SQL Server. Los procedimientos creados directamente en tempdb existen incluso después de haber finalizado la conexión en que se crearon.

Nota: Utilizar excesivamente los procedimientos almacenados temporales puede causar conflictos en las tablas del sistema de tempdb y afectar de forma negativa al rendimiento. Se recomienda que utilice en su lugar sp_executesql. El procedimiento sp_executesql no almacena datos en las tablas del sistema y, por tanto, evita el problema.

Los procedimientos almacenados CLR no se pueden crear como procedimientos almacenados temporales.

Ejemplos

A. Utilizar un procedimiento sencillo con una instrucción SELECT compleja

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.

CopiarUSE AdventureWorks;GOIF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetAllEmployees;GOCREATE PROCEDURE HumanResources.uspGetAllEmployeesAS SET NOCOUNT ON; SELECT LastName, FirstName, JobTitle, Department

FROM HumanResources.vEmployeeDepartment;GO

El procedimiento almacenado uspGetEmployees se puede ejecutar de estas formas:

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

En este tema se describe cómo crear un procedimiento almacenado de Transact-SQL mediante el Explorador de objetos de SQL Server Management Studio y se ofrece un ejemplo en el que se crea un procedimiento almacenado simple en la base de datos AdventureWorks.

Para crear un procedimiento almacenado

1. En el Explorador de objetos, conéctese a una instancia de SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) y expándala.

2. Expanda Bases de datos, la base de datos a la que pertenece el procedimiento almacenado y, por último, Programación.

3. Haga clic con el botón secundario en Procedimientos almacenados y, a continuación, haga clic en Nuevo procedimiento almacenado.

4. En el menú Consulta, haga clic en Especificar valores para parámetros de plantilla.

5. En el cuadro de diálogo Especificar valores para parámetros de plantilla, la columna Valor contiene valores recomendados para los parámetros. Acepte los valores o reemplácelos con nuevos valores y, a continuación, haga clic en Aceptar.

6. En el editor de consultas, reemplace la instrucción SELECT por las instrucciones para el procedimiento.

7. Para probar la sintaxis, en el menú Consulta, haga clic en Analizar.

8. Para crear el procedimiento almacenado, en el menú Consulta, haga clic en Ejecutar.

9. Para guardar la secuencia de comandos, en el menú Archivo, haga clic en Guardar. Acepte el nombre de archivo o reemplácelo por un nombre nuevo y, a continuación, haga clic en Guardar.

Nota de seguridad: Valide toda entrada de usuario. No concatene ninguna entrada de usuario antes de que se valide. No ejecute nunca un comando creado a partir de una entrada de usuario no

validada. Para obtener más información, vea Inyección de código SQL.Para crear un ejemplo de procedimiento almacenado

1. En el Explorador de objetos, conéctese a una instancia de SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) y expándala.

2. Expanda Bases de datos, la base de datos AdventureWorks y, por último, Programación.

3. Haga clic con el botón secundario en Procedimientos almacenados y, a continuación, haga clic en Nuevo procedimiento almacenado.

4. En el menú Consulta, haga clic en Especificar valores para parámetros de plantilla.

5. En el cuadro de diálogo Especificar valores para parámetros de plantilla, especifique los siguientes valores para los parámetros mostrados.

Parámetro Valor Author Su nombre.Create Date La fecha de hoy.Description Devuelve datos de empleado.Procedure_name HumanResources.uspGetEmployees@Param1 @LastName@Datatype_For_Param1 nvarchar(50)Default_Value_For_Param1 NULL@Param2 @FirstName@Datatype_For_Param2 nvarchar(50)Default_Value_For_Param2 NULL

6. Haga clic en Aceptar.

7. En el editor de consultas, reemplace la instrucción SELECT por la siguiente instrucción:

Copiar

SELECT FirstName, LastName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName = @FirstName AND LastName = @LastName;

8. Para probar la sintaxis, en el menú Consulta, haga clic en Analizar. Si se devuelve un mensaje de error, compare las instrucciones con la información anterior y corrija lo que sea necesario.

9. Para crear el procedimiento almacenado, en el menú Consulta, haga clic en Ejecutar.

10. Para guardar la secuencia de comandos, en el menú Archivo, haga clic en Guardar. Especifique un nuevo nombre de archivo y haga clic en Guardar.

11. Para ejecutar el procedimiento almacenado, en la barra de herramientas, haga clic en Nueva consulta.

12. En la ventana de consultas, especifique las siguientes instrucciones:

Copiar

USE AdventureWorks;GOEXECUTE HumanResources.uspGetEmployees @FirstName = N'Diane', @LastName = N'Margheim';GO

13. En el menú Consulta, haga clic en Ejecutar.

En este tema se describe cómo modificar un procedimiento almacenado de Transact-SQL mediante el Explorador de objetos en SQL Server Management Studio. Cuando cambie las instrucciones o los parámetros de un procedimiento almacenado mediante este método, los permisos definidos para el procedimiento almacenado se conservan y los procedimientos almacenados o desencadenadores dependientes no se ven afectados.

Importante: El hecho de cambiar el nombre o la definición de un procedimiento almacenado puede provocar errores en los objetos dependientes si no se actualizan con arreglo a los cambios realizados en el procedimiento almacenado. Para obtener más información, vea Cómo ver las dependencias de un procedimiento almacenado (SQL Server Management Studio). Para modificar un procedimiento almacenado

1. En el Explorador de objetos, conéctese a una instancia de SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) y expándala.

2. Expanda Bases de datos, a continuación, la base de datos a la que pertenece el procedimiento almacenado y, por último, Programación.

3. Expanda Procedimientos almacenados, haga clic con el botón secundario en el procedimiento que desea modificar y, a continuación, haga clic en Diseño.

4. Modifique el texto del procedimiento almacenado.

5. Para probar la sintaxis, en el menú Consulta, haga clic en Analizar.

6. Para modificar el procedimiento almacenado, en el menú Consulta, haga clic en Ejecutar.

7. Para guardar la secuencia de comandos, en el menú Archivo, haga clic en Guardar. Acepte el nombre de archivo o sustitúyalo por uno nuevo y,a continuación, haga clic en Guardar.

Nota de seguridad: Valide toda entrada de usuario. No concatene ninguna entrada de usuario antes de validarla. No ejecute nunca un comando creado a partir de una entrada de usuario no validada. Para obtener más información, vea Inyección de código SQL.

En este tema se describe cómo eliminar un procedimiento almacenado mediante el Explorador de objetos en SQL Server Management Studio.

Importante: Eliminar un procedimiento almacenado puede hacer que los objetos y secuencias de

comandos dependientes produzcan un error cuando los objetos y secuencias de comandos no se han actualizado para reflejar los cambios realizados en el procedimiento almacenado. Para obtener más información, vea Cómo ver las dependencias de un procedimiento almacenado (SQL Server Management Studio). Para eliminar un procedimiento almacenado

1. En el Explorador de objetos, conéctese a una instancia de SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) y expándala.

2. Expanda Bases de datos, a continuación, la base de datos a la que pertenece el procedimiento almacenado y, por último, Programación.

3. Expanda Procedimientos almacenados, haga clic con el botón secundario en el procedimiento que desee eliminar y, a continuación, haga clic en Eliminar.

4. Para ver los objetos que dependen del procedimiento almacenado, haga clic en Mostrar dependencias.

5. Confirme que haya seleccionado el procedimiento almacenado correcto y haga clic en Aceptar.

6. Quite el nombre del procedimiento almacenado de los objetos y secuencias de comandos dependientes.

En este tema se describe cómo ver las dependencias de un procedimiento almacenado mediante el Explorador de objetos en SQL Server Management Studio.

Antes de modificar, cambiar el nombre o eliminar un procedimiento almacenado, es importante conocer qué objetos dependen de él. Por ejemplo, el hecho de cambiar el nombre o la definición de un procedimiento almacenado puede provocar errores en los objetos dependientes si no se actualizan con arreglo a los cambios realizados en el procedimiento almacenado.

Para ver las dependencias de un procedimiento almacenado

1. En el Explorador de objetos, conéctese a una instancia de SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) y expándala.

2. Expanda Bases de datos, luego la base de datos a la que pertenece el procedimiento almacenado y, por último, Programación.

3. Expanda Procedimientos almacenados, haga clic con el botón secundario en el procedimiento y, a continuación, haga clic en Ver dependencias.

4. Examine la lista de objetos que dependen del procedimiento almacenado.

5. Examine la lista de objetos de los cuales depende el procedimiento almacenado.

6. Haga clic en Aceptar.

En este tema se describe cómo conceder permisos en un procedimiento almacenado mediante el Explorador de objetos en SQL Server Management Studio. Puede conceder permisos a un usuario existente, una función de base de datos o una función de aplicación en la base de datos.

Procedimientos

Para conceder permisos en un procedimiento almacenado 1. En el Explorador de objetos, conéctese a una instancia de SQL Server 2005

Database Engine (Motor de base de datos de SQL Server 2005) y expándala.2. Expanda Bases de datos, a continuación, la base de datos a la que pertenece el

procedimiento almacenado y, por último, Programación.

3. Expanda Procedimientos almacenados, haga clic con el botón secundario en el procedimiento sobre el que desea conceder permisos y, a continuación, haga clic en Propiedades.

4. En Propiedades del procedimiento almacenado, seleccione la página Permisos.

5. Para conceder permisos a un usuario, una función de base de datos o una función de aplicación, haga clic en Agregar.

6. En Seleccionar usuarios o funciones, haga clic en Tipos de objeto para agregar o borrar los usuarios y las funciones que desee.

7. En la cuadrícula Permisos explícitos, seleccione los permisos que desea conceder a la función o usuario especificados. Para obtener una descripción de los permisos, vea Permisos.

Al seleccionar Conceder, se indica que se concederá el permiso especificado al receptor. Al seleccionar WITH GRANT, se indica que el receptor también podrá conceder el permiso especificado a otras entidades de seguridad.

Nota: No se puede usar SQL Server Management Studio para conceder permisos sobre procedimientos almacenados del sistema o funciones del sistema. En su lugar, use GRANT (permisos de objeto de Transact-SQL).

Para ejecutar un procedimiento almacenado, utilice la instrucción EXECUTE de Transact-SQL. También puede ejecutar un procedimiento almacenado sin necesidad de utilizar la palabra clave EXECUTE si el procedimiento almacenado es la primera instrucción del lote.

Ejecutar procedimientos almacenados del sistema

Los procedimientos almacenados del sistema comienzan con los caracteres sp_. Se almacenan físicamente en la base de datos de recursos, pero aparecen lógicamente en el esquema sys de cada base de datos definida por el sistema y por el usuario en la instancia de Microsoft SQL Server. Los procedimientos almacenados del sistema se pueden ejecutar desde cualquier base de datos aunque el nombre del procedimiento almacenado no sea completo. Un nombre completo que no sea de esquema puede ser un nombre de una parte como sp_someproc o un nombre de tres partes como somedb..sp_someproc cuya segunda parte, es decir, el nombre del esquema, no esté especificada.

Se recomienda que certifique como de esquema todos los nombres de procedimientos almacenados del sistema con el nombre de esquema sys a fin de evitar conflictos de nombre. En el siguiente ejemplo se muestra el método recomendado para ejecutar un procedimiento almacenado del sistema.

CopiarEXEC sys.sp_who;

En los siguientes ejemplos se muestran algunos métodos para ejecutar procedimientos almacenados del sistema compatibles con versiones anteriores.

Nota: Los siguientes métodos para ejecutar procedimientos almacenados del sistema se eliminarán de las versiones futuras de SQL Server. Evite el uso de estos métodos en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente los emplean. CopiarEXEC sp_who;EXEC master.dbo.sp_who;EXEC mydatabase..sp_who;EXEC dbo.sp_who;EXEC mydatabase.dbo.sp_who;

Intercalar bases de datos coincidentes

Microsoft SQL Server 2005 usa la intercalación de base de datos de llamada al comparar los nombres de los procedimientos del sistema. Por lo tanto, en la aplicación, debe utilizar en todo momento las letras mayúsculas y minúsculas correctas de los nombres de los procedimientos del sistema. Por ejemplo, este código generará un error en el contexto de una base de datos que tenga una intercalación que distinga mayúsculas de minúsculas.

Copiarexec SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help

Utilice las vistas de catálogo sys.system_objects y sys.system_parameters para mostrar los nombres exactos de los procedimientos almacenados del sistema.

Ejecutar procedimientos almacenados extendidos del sistema

Los procedimientos almacenados extendidos del sistema comienzan con los caracteres xp_. Se almacenan físicamente en la base de datos de recursos, pero aparecen lógicamente en el esquema sys de cada base de datos definida por el sistema y por el usuario en la instancia de SQL Server. En el siguiente ejemplo se muestra el método recomendado para ejecutar un procedimiento almacenado extendido del sistema.

CopiarEXEC sys.xp_subdirs 'c:\';

Ejecutar procedimientos almacenados definidos por el usuario

Al ejecutar un procedimiento almacenado definido por el usuario (ya sea en un lote o dentro de un módulo, como lo es una función o un procedimiento almacenado definido por el usuario), se recomienda certificar el nombre de este procedimiento por lo menos con el nombre del esquema.

En el siguiente ejemplo se muestra el método recomendado para ejecutar un procedimiento almacenado definido por el usuario.

CopiarUSE AdventureWorks;GOEXEC dbo.uspGetEmployeeManagers 50;

- O bien -

CopiarEXEC AdventureWorks.dbo.uspGetEmployeeManagers 50;GO

Si se especifica un procedimiento almacenado no calificado definido por el usuario, Database Engine (Motor de base de datos) busca el procedimiento siguiendo este orden:

El esquema sys de la base de datos actual. El esquema predeterminado del autor de la llamada se ejecuta en un lote o en

SQL dinámico. Si el nombre del procedimiento no calificado aparece dentro del cuerpo de otra definición de procedimiento, a continuación se busca el esquema que contiene este otro procedimiento. Para obtener más información acerca de los esquemas predeterminados, vea Separación de esquemas de usuario.

El esquema dbo de la base de datos actual.

Importante: Si un procedimiento almacenado creado por un usuario tiene el mismo nombre que un procedimiento almacenado del sistema, el procedimiento creado por el usuario no llegará a ejecutarse si utiliza una referencia de nombre certificado que no sea de esquema. Para obtener más información, vea Crear procedimientos almacenados (motor de base de datos).Especificar parámetros

Es posible suministrar los valores de los parámetros si se escribe un procedimiento almacenado que los acepte.

El valor suministrado debe ser una constante o una variable; no puede especificar un nombre de función como valor de parámetro. Las variables pueden ser definidas por el usuario o ser variables del sistema, como @@spid.

En los siguientes ejemplos se muestra cómo se pasan valores de parámetros al uspGetWhereUsedProductID del procedimiento almacenado. El procedimiento espera

valores para dos parámetros de entrada: un Id. de producto y una fecha. Los ejemplos muestran cómo pasar parámetros como constantes y variables y también cómo usar una variable para pasar el valor de una función.

CopiarUSE AdventureWorks;GO-- Passing values as constants.EXEC dbo.uspGetWhereUsedProductID 819, '20050225';GO-- Passing values as variables.DECLARE @ProductID int, @CheckDate datetime;SET @ProductID = 819;SET @CheckDate = '20050225';EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;GO-- Try to use a function as a parameter value.-- This produces an error message.EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();GO-- Passing the function value as a variable.DECLARE @CheckDate datetime;SET @CheckDate = GETDATE();EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;GO

Si desea especificar los parámetros en un orden distinto al orden en que están definidos en el procedimiento almacenado, debe ponerles nombre. Para obtener más información, vea Especificar un nombre de parámetro.

Para especificar que un parámetro debe devolver un valor al programa que hace la llamada, use la palabra clave OUTPUT. Para obtener más información, vea Especificar la dirección de un parámetro.

Especificar el orden de los parámetros

Si especifica los parámetros con el formato @parámetro =value, puede proporcionarlos en cualquier orden. También puede omitir los parámetros para los que se hayan especificado valores predeterminados. Si sólo especifica un parámetro con el formato @parámetro =value, deberá proporcionar todos los parámetros subsiguientes del mismo modo. Si no especifica los parámetros con el formato @parámetro =value, deberá especificarlos en el orden que se ha seguido en la instrucción CREATE PROCEDURE.

Cuando ejecute un procedimiento almacenado, el servidor rechazará todos los parámetros que no se incluyeron en la lista de parámetros durante la creación del procedimiento. No se aceptará ningún parámetro pasado por referencia (el nombre del parámetro se pasa explícitamente) si el nombre del parámetro no coincide.

Usar valores predeterminados en los parámetros

Aunque puede omitir los parámetros para los que se hayan especificado valores predeterminados, sólo puede truncar la lista de parámetros. Por ejemplo, si en un

procedimiento almacenado hay cinco parámetros, puede omitir el cuarto y el quinto, pero no puede omitir el cuarto e incluir el quinto a menos que suministre los parámetros con el formato @parámetro =value.

El valor predeterminado de un parámetro, si se ha definido para el parámetro del procedimiento almacenado, se utiliza cuando:

No existe ningún valor especificado para el parámetro en el momento de ejecutar el procedimiento almacenado.

Se especifica la palabra clave DEFAULT como valor para el parámetro.