Ejecución de procedimientos almacenados
Una vez se ha creado un
procedimiento almacenado, este ya se puede
ejecutar. Si en la primera línea de una secuencia de sentencias aparece
el nombre de un procedimiento almacenado, lo ejecutará. Si no es así es
necesario utilizar la sentencia EXECUTE.
La sentencia EXECUTE
Esta sentencia se utiliza para
ejecutar procedimientos almacenados, ya sean del sistema o creados por el
usuario. También permite la ejecución de una cadena de caracteres que contiene
una cierta sentencia de SQL. Su sintaxis es la siguiente:
[ { EXEC | EXECUTE } ]
{
[ @valor_retorno = ]
{ nombre_procedimiento_almacenado
[ ;number ] | @variable_del_procedimiento }
[
[ @parametro = ] { value
|
@variable [ OUTPUT ]
|
[ DEFAULT ]
}
]
[
,...n ]
[ WITH
<execute_option> [ ,...n ] ]
}
[;]
El procedimiento que se
ejecutará es el especificado en nombre_procedimiento_almacenado o
también puede estar contenido en una variable @variable.
A continuación se muestra un
ejemplo de cómo se hace una llamada a un procedimiento almacenado con tres parámetros,
el primero de tipo char, el segundo de tipo numérico y un último parámetro de
salida.
EXEC DBO.sp_Nombre_Procedimiento '12',2,OUTPUT.
Valor de retorno
En el ejemplo anterior el procedimiento
almacenado devuelve un valor. Si esto es así, si queremos saber el valor
retomado tendremos que recogerlo en una variable que en la sintaxis hemos
presentado como @valor_retorno.
DECLARE @strError varchar(255)
EXEC @strError = DBO.sp_Nombre_Procedimiento '12',2,OUT
SELECT @strError
Al ejecutar estas sentencias
hacen lo mismo que la anterior pero en este caso podemos consultar directamente
el valor retornado a la variable @strError.
Ejecución de procedimientos agrupados
Igual que es posible agrupar
procedimientos en el momento de su creación, poniéndoles el mismo nombre. También
es posible ejecutarlos para lo cual será preciso adjuntar su número de
identificación tal y como se explicó en la 1ª parte .
Paso de parámetros
La sintaxis de paso de parámetros
ya la hemos visto en los ejemplos anteriores. Es posible pasar los parámetros
sin necesidad de indicar sus nombres, en este caso será necesario pasarlos en
el mismo orden en el que fueros definidos. Si queremos pasarlos en un orden
distinto, tendremos que especificar sus nombres explicitamente.
DECLARE @strError varchar(255)
EXEC @strError = DBO.sp_Nombre_Procedimiento @strRecepcion
='12', @intModo = 2,@strRetorno= OUT
select @strError
Los valores de tipo cadena se
introducen entre comillas simples, si dicho valor es una palabra clave de SQL
Server deberá ir entre comillas dobles.
Valores por defecto
Es posible especificar valores
por defecto para algunos o todos los parámetros de un procedimiento almacenado.
Los valores podrán ser omitidos si se ha definido su valor por defecto. Si se desea
que un parámetro tome el valor por defecto debe utilizarse la siguiente
sintaxis:
@Parametro = DEFAULT
Omisión de parámetros en la llamada
Es posible especificar
acciones a realizar si se omite algunos de los parámetros en la llamada.
Por ejemplo, este
prodecimiento actúa en consecuencia si dejamos la variable de entrada vacía.
CREATE PROCEDURE SP_Variable_Vacia
@tb_Nombre_tabla varchar(30)
= NULL
AS
IF @tb_Nombre_tabla IS NULL PRINT 'Por favor proporcione
un nombre de tabla' ELSE
SELECT TABLE_NAME = sysobjects.name, INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes, sysobjects WHERE sysobjects.name = @tb_Nombre_tabla AND
sysobjects.id = sysindexes.id
Otro ejemplo de definición y
utilización de parámetros por defecto es la definición de un procedimiento
almacenado en el que se crean parámetros por defecto para dos de los tres
parámetros que admite.
CREATE PROCEDURE
SP_Valores_Defecto
@p1 SMALLINT = 1,
@p2 CHAR(1),
@p3 VARCHAR(3) = 'Tomás'
AS
Ahora podemos utilizar esta
serie de llamadas para ejecutar el procedimiento almacenado.
EXECUTE SP_Valores_Defecto @p2
= 'B'
EXECUTE SP_Valores_Defecto @p1
= DEFAULT, @p2 = 'A'
EXECUTE SP_Valores_Defecto DEFAULT, @p3 = 'Juan', @p2 = 'D'
EXECUTE SP_Valores_Defecto 35, 'B', @p3 = DEFAULT
EXECUTE SP_Valores_Defecto 28, 'H', DEFAULT
EXECUTE SP_Valores_Defecto
DEFAULT, 'I', DEFAULT
EXECUTE SP_Valores_Defecto
DEFAULT, 'J', @p3 = DEFAULT
Parámetros de retorno
Para los parámetros del
procedimiento que son creados como valor de retomo utilizando la cláusula OUTPUT
en la sentencia CREATE PROCEDURE, es necesario proporcionar en el momento de la
llamada una variable que almacene dicho valor en el momento de la ejecución.
Procedimientos de recompilación forzosa
Procedimientos de recompilación forzosa
Como ya se dijo en el artículo
anterior los procedimientos almacenados se compilan sólo la primera vez que son
ejecutados. Si en el momento de la llamada suponernos que dicha optimización no
será la más adecuada para, podemos forzar una recompilación con la cláusula
WITH RECOMPILE. La compilación realizada será la utilizada en las subsiguientes
ejecuciones.
Permisos de ejecución
El permiso de ejecución está
reservado al propietario del procedimiento almacenado, quien puede transferir
su permiso a los otros usuarios. Si tenemos los permisos de ejecución
correspondientes, es posible ejecutar procedimientos almacenados en otras bases
de datos e incluso en servidores remotos.
Información retornada por un procedimiento almacenado
Los procedimientos almacenados
pueden retornar información al procedimiento que los llamó o al usuario que
solicita su ejecución básica. Existen dos formas de obtener estos valores de
retorno:
Mediante el retorno de un
valor de estado.
Mediante parámetros de
retorno.
Valor de estado (status value)
Es una práctica común en los lenguajes
de programación que los procedimientos o funciones retornen un valor que permita
testear cual ha sido el resultado de la ejecución con el que la función ha terminado.
Normalmente estos valores son definidos de antemano de manera que un análisis
del valor de estado permite conocer cual ha sido el resultado de su ejecución.
Los procedimientos almacenados
en SQL Server devuelven también un valor de estado o status value. Este valor también puede almacenarse en una variable de forma que es posible examinarlo
posteriormente para valorar como ha ido ejecución del procedimiento o el motivo
de su fracaso si se ha producido un fallo.
DECLARE @return_status int;
EXEC @return_status = dbo.prueba;
SELECT 'Return Status' = @return_status;
GO
Valores de estado predefinidos
SQL Server predefine los
valores que puede tomar ese status value
y el significado que cada uno de dichos valores.
El valor 0 significa que la ejecución
ha tenido éxito. El resto de valores
negativos entre -1 y -99 están reservados.
Valores de retorno definidos por el usuario
La sentencia RETURN permite
que el procedimiento almacenado devuelva un valor predefinido por el usuario.
Un ejemplo: Este procedimiento
devuelve 1 si un determinado valor de una tabla cumple
una condición y 2 en caso contrario. No es posible utilizar valores de retorno
comprendidos entre -1 y -99 pues están reservados por SQL Server como acabamos
de ver.
CREATE PROCEDURE
sp_valor_retorno @valor_retornado varchar(20)
AS
IF (SELECT Campo1 FROM dbo.tbTabla WHERE Campo1
= @valor_retornado)
> 5
RETURN 1
ELSE
RETURN 2
Podríamos definir un
nuevo procedimiento almacenado que utilice la salida del primero.
Este procedimiento
presenta un mensaje indicando si un determinado valor toma el valor 1 o no, utilizando
el procedimiento anterior.
CREATE PROCEDURE
sp_toma_valor_retorno @valor_retornado varchar(20) AS DECLARE
@valorderetorno int
EXECUTE @valorderetorno =
sp_valor_retorno @valor_retornado IF (@valorderetorno = 1) PRINT 'Operacion 1'
ELSE PRINT 'Operacion 2'
Retorno de parámetros
En el momento crear un procedimiento
almacenado debemos indicar los parámetros de entrada que también será parámetro
de salida. Es posible especificar alguno,
o todos, los parámetros sean considerados parámetros de retorno.
Si especificamos la cláusula
OUTPUT en la ejecución de un procedimiento almacenado con EXECUTE también deberemos
indicarlo en la definición de CREATE PROCEDURE, si no lo hacemos así dará
error.
Por otro lado si no
especificamos la cláusula OUTPUT los cambios realizados sobre el parámetro de
retorno no serán accesibles una vez haya terminado la ejecución del
procedimiento.
Paso de parámetros por referencia
Cuando se utiliza la opción OUTPUT
en un parámetro, en las cláusulas CREATE PROCEDURE y EXECUTE, las
modificaciones que se producen en el interior del procedimiento almacenado sobre
los valores de los parámetros que se han pasado como argumentos se muestran en los
valores incluso una vez finalizada la ejecución del procedimiento. Esto es el paso
de parámetros por referencia.
Paso de parámetros por valor
Si no se utiliza la cláusula
OUTPUT estaremos realizando el paso de parámetros por valor, esto significa que
al pasar el parámetro al procedimiento almacenado se creará una copia del
parámetro que será tratada de forma local en el interior del procedimiento.
Esto significa que las modificaciones que se lleven a cabo sobre los parámetros
no se reflejarán en los valores de estos una vez que el procedimiento haya
finalizado su ejecución.
Como ejemplo este
procedimiento para sumar
CREATE PROCEDURE Sp_Suma
@Sum1 int,
@Sum2 int, @res
int OUTPUT
AS
SELECT @Res = @Sum1 + @Sum2
RETURN 0
Este procedimiento la suma de
dos variables enteras y devuelve una variable de salida.
DECLARE @Resultado int
DECLARE @Retorno int
EXECUTE @Retorno =
Sp_Suma 2,2, @resultado OUTPUT
En este punto @retorno valdrá
0 y @resultado tendrá el valor 4.
Procedimientos almacenados que proporcionan información de sistema
SQL Server dispone de varios
procedimientos almacenados del sistema que proporcionan cierta información
sobre sus tablas internas de sistema o sobre la base de datos en general. Entre
ellos tenemos:
sp_help: Muestra información sobre un objeto de base de datos, un tipo de
datos definido por el usuario o un tipo de datos.
sp_help 'sp_suma'
sp_help 'dbo.tbNombreTabla'
No hay comentarios:
Publicar un comentario