Como comenté, cambio de registro y dedico este blog a publicar artículos relacionados con mi trabajo, el desarrollo de aplicaciones web. Aclarar, que aunque intento que sirvan a aquellos desarrolladores sin amplios conocimientos, en este caso sobre procedimientos almacenados, lo publico como “copia de seguridad” de mis notas.
A la faena…
Voy a intentar mostraros como desarrollar un procedimiento almacenado, mostrando una serie de ejemplos y recordando algunas estructuras que se deberían conocer. La finalidad, que podaís crear vuestros procedimientos almacenados o resolver alguna duda que tengáis.
Durante la creación de los procedimientos declararemos parametros, cursores, variables y nombres de procedimientos, que si no es obligarorio, si deberían seguir una serie de reglas para los Identificadores de Base de Datos.
1.-Definición del procedimiento:
CREATE PROCEDURE [dbo].[pa_Rellena_Tabla]
“CREATE PROCEDURE”: Literal que indica que vamos a crear el procedimiento y por tanto es obligatorio.
[esquema]: 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.
[nombre_procedimiento]: 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. El nombre del procedimiento no puede superar los 128 caracteres.
[esquema].[nombre de procedimiento]:[miEsquema].[pa_miProceso]
CREATE PROCEDURE [miEsquema].[pa_miProceso]
Ya está creado el procedimiento almacenado.
Ahora intentemos acercarnos a la realidad, creando unos procedimiento más reales.
i. Definición de procedimiento que nos devuelva un reslultado:
CREATE PROCEDURE [miEsquema].[ pa_miProceso] @RespuestaEjecucion int output
ii. Definición de procedimiento que devuelva un resultado y reciba un parametro de entrada:
CREATE PROCEDURE [miEsquema].[pa_miProceso] @RespuestaEjecucion int output, @Primer_Parametro as varchar(9)
Si quieres que @Primer_Parametro sea opcional, has de asignarle un valor por defecto:
CREATE PROCEDURE [dbo].[sp_miProceso] @RespuestaEjecucion int output, @Primer_Parametro as varchar(9)=’valor1’
Si quieres que @Primer_parametro sea de tipo int, @Primer_Parametro int
2.-Definición de variables y cursores
declare @VarTexto nvarchar(max)
declare @VarInt int
declare @VarCursor as cursor
3.-Asignar valores
Asigna valor de texto: set @VarTexto = ‘texto a incluir’
Concatena valor a una variable: @VarTexto = @VarTexto + ‘ texto a concatenar’
Asigna valor de numérico: set @VarInt = 1
Asigna valor de una consulta sql, el campo ha de ser del mismo tipo que la variable: select @VarInt = id_campo from [tabla] where id_campo2 is null
Asigna valor de una consulta set @VarCursor = cursor for select valor from tabla where condición
4.-Ejemplo uso de un cursor
Declaración de variables
declare @MiCursor as cursor
declare @ID_uno int
declare @ID_dos int
Asigna valores
set @ID_dos = @Parametro_de_Entrada_Procedimiento_almacenado
El valor del cursor son los datos obtenidos de una consulta.
set @MiCursor = cursor for select campo1 from [esquema].[tabla] where id_tabla = @ID_dos
Abre el cursor para poder usarlo
open @MiCursor
Asigna los valores del primer registro a nuestra variable
fetch next from @MiCursor into @ID_uno
Si la consulta seleccionase campo1,campo2 se asignarían así a sus variables.
fetch next from @MiCursor into @VarCampo1, @VarCampo2
Mientras el estado sea 0 indica que no ha llegado a EOF
WHILE @@fetch_status = 0
BEGIN
Proceso a ejecutar con cada registro encontrado.
Una vez terminado el proceso, asigna a la variable el valor del siguiente registro.
FETCH next FROM @MiCursor INTO @ID_uno
END
Importante, si no vas a trabajar más con el cursor lo cierras y destruyes para liberar la memoria.
close @MiCursor
deallocate @MiCursor
5.-Ejecuciones dentro de un procedimiento almacenado.
Dentro de un procedimiento almacenado normal, no de ejemplos del tipo Hola Mundo, se puede dar la necesidad de tener que trabajar con variables, que ya hemos visto, con vectores, que también o la necesidad de evaluar una condición o ejecutar un procedimiento almacenado.
i. Ejecutar procedimientos almacenados
Empezaremos por lo más sencillo, invocar un procedimiento almacenado dentro de otro procedimiento.
Si no tiene parámetros: execute [nombre_procedimiento]
Si tiene parámetros: execute [nombre_procedimiento] @ Parametro1, @Parametro2
Esto se usa mucho, por ejemplo cuando queremos ejecutar una sentencia sql utilizamos este procedimiento almacenado del sistema, con el prefijo sp_ que no debemos usar:
exec sp_executesql @sql
En la variable @sql estaría la operación CRUD a realizar.
Otro procedimiento almacenado del sistema es OBJECT_ID, que como su nombre indica devuelve el id de un objeto ser tablas, así lo usaríamos:
SET @object_id = OBJECT_ID(N'[nombre_esquema].[nombre_tabla]’);
Si @object_id fuese 0 nos indicaría que no lo ha encontrado.
En la misma línea está DB_ID, en este caso devuelve el id de la base de datos:
SET @db_id = DB_ID(N’nombre_base_de_datos’);
Si @db_id fuese 0 nos indicaría que no lo ha encontrado.
ii. Evaluar condiciones
Recordando la estructura “IF”
IF condición
BEGIN
Sentencias en caso afirmativo.
END
Recordando la estructura “IF, ELSE”
IF condición
BEGIN
Sentencias en caso afirmativo.
END
ELSE
BEGIN
Sentencia en caso negativo.
END
Recordando la estructura “IF anidado”
IF condición
BEGIN
Sentencias en caso afirmativo.
END
ELSE IF Condición_Else_if
BEGIN
Sentencias en caso afirmativo.
END
ELSE
BEGIN
Sentencias en caso negativo.
END
Ejemplo de uso IF
Probemos evaluar la respuesta de una consulta sql.
IF (select count(*) from [tabla] where id_campo = @ID_uno and campo2 = @ID_dos) = 0
BEGIN
Sentencias en caso de que no se encuentre ninguno.
END
ELSE
BEGIN
Sentencias en caso de que encuentre alguno.
END
iii. Búcle WHILE
Recordando la estructura del “While”
WHILE condición
BEGIN
Proceso a ejecutar mientras se cumpla la condición.
IMPORTANTE: Una vez terminado el proceso, hay que reasignar el valor siguiente a la condición para evitar el “bucle infinito”.
Incremento de la variable a evaluar.
END
Ejemplo de uso del “while”;
Lo vamos a realizar con un cursor, que viene siendo lo más lógico.
Antes de evaluar la condición hay que asigar el primer valor.
fetch next from @MiCursor into @ID_uno
Mientras el estado del cursor sea 0 no habremos llegado a EOF
WHILE @@fetch_status = 0
BEGIN
Proceso a ejecutar con cada registro encontrado.
Terminado el proceso, asigna a la variable el valor del siguiente registro.
FETCH next FROM @MiCursor INTO @ID_uno
END
Creación de un proceso almacenado.
Vamos a crear un procedimiento que verifique que hay un usuario en la base de datos.
USE [mi_base_de_datos]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [mi_esquema].[nombre_procedimiento] @Nombre char(8), @Existe int output
AS
BEGIN
SET NOCOUNT ON;
IF ( select count(id_usuario) FROM usiario where nombre_usuario = @Nombre ) = 0
BEGIN
@Existe = 0
END
ELSE
BEGIN
@Existe = 1
END
return @Existe
END
Hasta aquí, el primer paso para realizar un procedimiento almacenado, espero os sea útil…
