Tarea Programada 2

Este blog contiene la bitacora de progreso para la segunda tarea programada del curso

Se quema el disco con la tarea

Se quema el disco con todos los datos del proyecto. Ademas, por cuestión de evitar la desconfianza del profesor, se imprimirá en un PDF una copia de todo blog hasta el momento.

Resumen de experiencias

Este es el resumen final de la segunda tarea programada del curso de bases de datos.

Cantidad de horas invertidas:

  • Boris Beck: 49 horas aproximadamente
  • Manuel Calderon: 44 horas aproximadamente
  • Grupo: 32 horas aproximadamente

Análisis de los resultados:

  • Se logro cumplir con todo lo que la tarea programada pide, aunque aun quedan muchas dudas y algunas confusiones con respecto a como funcionan algunos procedimientos almacenados. No estamos seguros si nuestra interpretación de como debe funcionar ciertas operaciones (ejemplo: Calculo de Intereses Moratorios) es como se debía hacer. Al parecer todo lo que probamos funciona bastante bien, no encontramos fallas en la ejecución aunque en la lógica se puede esperar alguna.
  • El problema más grande que tuvimos fue el de hacer funcionar la conexión entre Office y SQL ya que en los sistemas operativos más nuevos de 64 bits hay bastantes problemas de compatibilidad y tuvimos que realizar varias re instalaciones del SQL y ademas nos toco descargar algunos controladores adicionales. Si no fuera por este inconveniente, posiblemente hubiéramos terminado antes. Solo en tratar de resolver este problema consumimos casi 6 horas incluyendo las re instalaciones(ver entrada sobre el problema).
  • Otra dificultad importante de mencionar es la gran cantidad de datos que había que manejar a la vez, los múltiples cálculos que había que hacer solo para sacar un estado de cuenta. Al principio fue un poco abrumador porque simplemente parecía ser mucho, pero ya luego de a poco logramos ir sacándolo.
  • Tuvimos que diseñar y modificar las tablas varias veces porque siempre olvidamos alguna columna o nos dábamos cuenta que faltaba alguna tabla hasta el momento de diseñar el procedimiento almacenado que la necesitaba. Esto también nos atraso un poco porque la mayoría de las tablas que tuvimos que modificar ya tenían otros procedimientos almacenados. Esto implico que tuvimos que modificar algunos procedimientos almacenados junto con las tablas.
  • Quedamos también con la duda sobre el uso de triggers en vez de variables tabla o las actualizaciones manuales luego de insertar algún movimiento. Sin embargo al final optamos por hacerlo de forma masiva sin los triggers.

Motivaciones:

  • Luego de ver que la tarea programada avanzaba relativamente bien (mejor que la primera) nos ayudo a ponerle más ganas a esta, en realidad la mayor parte del proyecto se realizo en un par de días mientras estábamos reunidos en una jornada de 4 a 5 horas.
  • Luego de que la migración funciono sabíamos que no teníamos que dar por perdido esa parte de los puntos.

Desmotivaciones:

  • Entrega del examen

Aprendizajes:

  • Aprendimos como usar el SQL de muchas maneras que ni siquiera conocíamos en la primera tarea y lo cual en retrospectiva nos hubiese ahorrado mucho tiempo.
  • El manejo de grandes bloques de datos en SQL (cantidades masivas) fue lo que más se investigo y trabajo en este segundo proyecto.
  • Las ventajas de usar procedimientos almacenados en vez de escribir código directamente.
  • Aprendimos lo importante que es ordenar el código, ya que en esta tarea los procedimientos almacenados eran más complejos y grandes. A diferencia de en la tarea anterior que teníamos lineas de código en un solo reglón,  en esta tarea escalonamos mejor el código para hacerlo más entendible y fácil de leer.

 

Por el momento eso sería todo… Es posible que anotemos más cosas que hayamos pasado por alto al momento de escribir esto a la 1 de la mañana lo cual no ayuda mucho a la memoria.

Se crean procedimientos almacenados

Se crean los procedimientos almacenados de:

  • Crédito por pago de contado
  • Crédito de IM a SR
  • Rebito de cargos administrativos
  • Calculo de pago mínimo
  • Calculo de fecha de pago mínimo
  • Cargos automáticos por periodo
  • Calculo de intereses diarios
  • Generar estado de cuenta
  • Cargar últimos 6 estados de cuenta
  • Cargar movimientos de un estado de cuenta

 

Procedimiento Almacenado Comisión por retiros

Se crea el procedimiento almacenado que calcula la comisión por retiros para cada cuenta siempre y cuando dicha cuenta este en su fecha de corte.

Lo que hace:

  • Calcula la comisión generada por la suma de todos los movimientos en MSR que sean ‘Retiro’ durante el periodo.
  • Toma todos esos datos y los guarda en una variable tabla.
  • Crea los movimientos basándose en los datos guardados en la variable tabla.
  • Actualiza el saldo real de las cuentas con respecto a la comisión indicada en la variable tabla.

Columna de Descripción para los movimientos

Se agrega una columna adicional a todas las tablas de movimientos MSR, MIN y MIM para mejorar la estética de la lista de movimientos y ademas hacerlo coincidir mejor con los archivos de prueba.

[SOLUCIONADO] Problema con la migración de datos

De alguna forma extraña logramos arreglar el problema. Aunque en realidad no estamos seguros de si esta funcionando en su totalidad, logra importar los datos correctamente y se puede trabajar con ellos.

Lo que hicimos:

  • Desinstalamos SQL Server 2012 x64
  • Instalamos limpiamente SQL Server 2012 x86
  • Instalamos driver “AccessDatabaseEngine” x86
  • Volvimos a crear la base de datos usando los scripts
  • Corrimos las siguientes consultas de configuración:

EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
GO
–***********************************************************************
EXEC sp_configure Ad Hoc Distributed Queries’, 1;
RECONFIGURE;
GO
–***********************************************************************
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1;
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1;
GO
–***********************************************************************
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 0;
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 0;
GO
–***********************************************************************

  • Ejecutamos el asistente para importar/exportar datos una sola vez.
  • Creamos un servidor enlazado con el siguiente código:

EXECsp_addlinkedserver
@server = ‘ExcelServer’,
@srvproduct = ‘Excel’,
@provider = ‘Microsoft.ACE.OLEDB.12.0’,
@datasrc = ‘D:\pruebas.xlsx’,
@provstr = ‘Excel 12.0;IMEX=1;HDR=YES;’

  • Por ultimo usamos el código para importar básico:

SELECT * INTO archivoprueba
FROM OPENROWSET
(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;
Database=D:\pruebas.xlsx;
HDR=YES’,
‘SELECT * FROM [pruebas$]’)

Procedimiento almacenado para estado de cuenta

Creamos el procedimiento almacenado que crea los estados de cuenta para cada fecha de corte o, si la cuenta es nueva, genera un estado de cuenta nuevo para dicha cuenta que se tomara como el estado inicial de apertura.

Sin embargo nos surgieron algunas dudas sobre las fechas de pago. ¿Se puede dar la situación en la que la cantidad de días supere el mes? En estos casos no deberíamos renovar la fecha, sino copiarla al siguiente estado de cuenta. Esto a su vez causa otro inconveniente, debemos verificar cuando dicha fecha se cumple para renovarla en medio de los estados de cuenta.

Estaremos analizando posibles soluciones.

Investigando forma de separar strings por medio de un caracter

Procedimiento almacenado para conectarse

Creamos el procedimiento almacenado para iniciar sesión en la aplicación web. Decidimos utilizar como usuario el número de cédula y la contraseña una de libre elección para el usuario, sin embargo esta contraseña debe tener letras en mayúscula, minúscula y números.

Procedimientos almacenados para EC y CA

Todos los procedimientos almacenados para las tablas EC y CA fueron creados (Insertar, Modificar, Borrar y Seleccionar). Ahora vamos a realizar pruebas para asegurarnos que todos los procedimientos estén funcionando correctamente.

Creación de las tablas para Estado Cuenta y Cargos Automaticos

Se crean las tablas ST_EC y ST_CA para poder realizar los procedimientos almacenados que se solicitan. No los habíamos creado anteriormente porque nos habíamos concentrado en los datos que especificaban en las tablas mencionadas antes de los procedimientos almacenados.

 

Este es el diseño de la base de datos:

Diagrama V1.2

Diseño preliminar del job de intereses normales

Estamos trabajando en el procedimiento almacenado para el job que realiza el calculo de los intereses ganados cada día, el cual hace lo siguiente:

  1. Calcula el interés que gana la cuenta (ST_Cuentas.SR * ST_Tipos.TIN) donde ST_Cuentas.FK_Tipos = ST_Tipos.T_ID
  2. Inserta en ST_MIN ST_Cuentas.C_ID(ST_Cuentas.SR * ST_Tipos.TIN), CURRENT_TIMESTAMP
  3. Actualiza el valor de ST_Cuentas.IA = (ST_Cuentas.SR * ST_Tipos.TIN)

Lo que estamos considerando:

  1. ¿Vale la pena usar una tabla temporal para realizar la inserción de movimientos?
  2. ¿Es correcto actualizar el valor de los intereses acumulados y luego insertar el movimiento?
  3. ¿Sería mejor un trigger luego de cada actualización de los intereses acumulados?
  4. ¿Sería mejor insertar y actualizar fila por fila en vez de primero insertar todos los movimientos y luego actualizar todas las filas?

Este es el código:

DECLARE@TempTableTABLE (ID bigint, Montofloat, Fecha datetime)

INSERT INTO@TempTable
SELECTC.C_ID, C.SR * T.TINAS Monto, CURRENT_TIMESTAMP ASFecha
FROMST_Cuentas C
INNER JOINST_Tipos T
ONC.FK_Tipo = T.T_ID

SELECT * FROM@TempTable

INSERT INTOST_MIN
(
FK_Cuenta,
Monto,
Fecha
)

UPDATE C
SET C.IA = (C.SR * T.TIN) + C.IA
FROM C INNER JOIN T
ON C.FK_Tipo = T.T_ID

Procedimientos almacenados de cargar

Ya se crearon todos los procedimientos almacenados para cargar las tablas.

Este es el código:

–Comienza el TRY
BEGIN TRY
SET @ReturnValue = 0
–Si todos los datos estan bien borramos y retornamos 0
IF(@ReturnValue = 0)
BEGIN
BEGIN TRANSACTION
SET @ReturnValue = 0
SET @Mensaje = ‘Tabla cargada exitosamente’
SELECT * FROM ST_Tipos
COMMIT TRANSACTION
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
RETURN
END
–Termina el TRY
END TRY

–Comienza el CATCH
BEGIN CATCH
ROLLBACK TRANSACTION
SET @ReturnValue = ERROR_NUMBER()
SET @Mensaje = ERROR_MESSAGE()
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
RETURN
–Termina el CATCH
END CATCH

Pruebas para arreglar el problema de migración de datos

Estamos realizando algunos cambios en el SQL para poder utilizar la migración de datos por medio de Microsoft.ACE.OLEDB.12.0.

Lo que intentamos:

  • Instalar SQL Server 32 bits y Access Database Engine 32 bits.
  • Instalar SQL Server 64bits y Access Database Engine 64 bits.
  • Dar acceso total a la ruta del archivo de pruebas y moverlo a la carpeta del SQL.
  • Probar el funcionamiento del asistente de importar/exportar datos en las distintas configuraciones (32 y 64 bits).
  • Crear un linked server para Microsoft.ACE.OLEDB.12.0.

Los resultados hasta el momento son los siguientes:

  • Para la configuración de 64 bits, ni siquiera con el asistente se logra importar los datos del archivo de prueba, nos indica el Microsoft.ACE.OLEDB.12.0. no es compatible para SQL  de 64 bits.
  • Para la configuración de 32 bits, el asistente parece funcionar sin problemas, sin embargo nos aparece el mismo error de “Microsoft.ACE.OLEDB.12.0″ for linked server “(null)”.
  • El darle acceso o cambiar la ruta del archivo no parece cambiar las cosas, ya que en primer lugar las rutas que utilizamos ya permitían el acceso al SQL.

Lo que parece funcionar:

EXECsp_addlinkedserver
@server = ‘ExcelServer’,
@srvproduct = ‘Excel’,
@provider = ‘Microsoft.ACE.OLEDB.12.0’,
@datasrc = ‘D:\pruebas.xlsx’,
@provstr = ‘Excel 12.0;IMEX=1;HDR=YES;’

 

SELECT * INTO archivoprueba FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;Database=D:\pruebas.xlsx;HDR=YES’,
‘SELECT * FROM [pruebas$]’)

 

Estaremos realizando más pruebas hasta asegurarnos de que el problema este resuelto y no falle justo en el momento de la revisión.

Olvidamos los procedimientos almacenados para cargar las tablas

Por alguna razón no hicimos los procedimientos almacenados de SELECT de las tablas. Ya estamos trabajando en eso.

Problema con la migración de datos

Por alguna razón desconocida no logramos ejecutar el comando para la migración de datos desde un archivo de Excel a la base de datos.

El mensaje de error que aparece es el siguiente:

The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered.

 

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Error no especificado”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

Enlaces que estamos usando para intentar resolver el problema:

 

Forma más sencilla de modificar tablas

Encontramos una forma mucho más fácil de modificar tablas sin tener que usar códigos o haciendo DROP y CREATE nuevamente para aplicar los cambios.

Simplemente es ir a Tools -> Options -> Designers y quitar el check en la opción de Prevent saving changes that require table re-creation.

Image

 

Olvidamos los cálculos de montos en procedimientos almacenados

Olvidamos hacer los cálculos al insertar, modificar o borrar un movimiento que afecte el balance de la cuenta a la que se le aplica el movimiento. Esto con el fin de que los datos tengan una consistencia y un impacto verdadero sobre los valores de las cuentas.

Por ahora no nos vamos a enfocar en solucionar eso, sino más bien en realizar los procedimientos almacenados que se solicitan para probarlo en jobs.

Procedimientos almacenados para borrar

Se crearon todos los procedimientos almacenados para borrar elementos de todas las tablas, sin embargo aun no incluimos los borrados o modificaciones masivas que deben darse al borrar ciertos elementos en ciertas tablas. Por ejemplo al borrar un tipo de la tabla ST_Tipos, se debe cambiar todos los valores de FK_Tipo en la tabla de cuentas que coincidan con ese valor para que no quede NULL o sin referencia.

Esto aparece como retorno del TRY/CATCH pero la idea es que todo lo realice el procedimiento almacenado.

Copiar y pegar no es la solución a todo

Por querer ahorrarnos tiempo usando copiar y pegar para los procedimientos almacenados faltantes terminamos gastando más tiempo modificando lo copiado de lo que hubiéramos consumido escribiendo el código desde cero.

Modificaciones a la tabla clientes

Se agregaron las columnas de Teléfono, Dirección y Contraseña a la tabla de clientes, ya que en la especificación se menciona la modificación de estos valores en los clientes. Ademas habíamos olvidado que necesitábamos hacer que el cliente se conectar a a su cuenta por medio de la aplicación web.

Nuestra idea es que se haga como en los bancos del país en donde el usuario ingresa con su número de cédula y con una contraseña de su preferencia.

Procedimientos almacenados para modificar

Se crearon todos los procedimientos almacenados para modificar los elementos de las tablas, no fue tan sencillo como copiar y pegar los de insertar y hacer unos pequeños cambios como pensábamos inicialmente. Tuvimos que crear condiciones adicionales para verificar que la fila con su respectivo identificador existían y verificar otros datos al modificar algún dato.

Este es el código:

SET @Comparador = (SELECT COUNT(*) FROM ST_Tabla WHERE Tabla_ID = @ID)

.

.

.

–Verificamos si el codigo esta registrado
IF(@Comparador != 1)
BEGIN
SET @ReturnValue = -700000
SET @Mensaje = ‘Error: El codigo del historial no esta registrado!’
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
RETURN
END

[SOLUCIONADO] Problema en que varias condiciones se cumplen

La solución fue solo poner un RETURN antes del END de cada IF/ELSE ya que el RETURN indica la fiscalización del procedimiento. Así que solo tuvimos que cambiar esto:

–Verificamos si la fecha de expiración es valida
IF(@Mes > 12 OR @Mes < 1 OR @Año < 1900 OR @Año > 2099)
BEGIN
SET @ReturnValue = -300000
SET @Mensaje = ‘Error: La fecha de expiración no es valida!’
SELECT @ReturnValue AS Retorno@Mensaje AS Mensaje
END

 

Por esto:

–Verificamos si la fecha de expiración es valida
IF(@Mes > 12 OR @Mes < 1 OR @Año < 1900 OR @Año > 2099)
BEGIN
SET @ReturnValue = -300000
SET @Mensaje = ‘Error: La fecha de expiración no es valida!’
SELECT @ReturnValue AS Retorno@Mensaje AS Mensaje
RETURN
END

 

Fuente de la información:

http://stackoverflow.com/questions/4580517/how-to-stop-the-execution-of-a-stored-procedure-using-sql-server

Problema en que varias condiciones se cumplen

En todos los procedimientos almacenados en los que existen varias sentencias de IF/ELSE sucede que el procedimiento almacenado verifica todas las situaciones en vez de detenerse en el primero que encuentre.

Este es el código:

–Verificamos si la fecha de expiración es valida
IF(@Mes > 12 OR @Mes < 1 OR @Año < 1900 OR @Año > 2099)
BEGIN
SET @ReturnValue = -300000
SET @Mensaje = ‘Error: La fecha de expiración no es valida!’
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
END
–Verificamos que el nombre introducido no sea muy corto
IF(LEN(@Dueño) < 5)
BEGIN
SET @ReturnValue = -400000
SET @Mensaje = ‘Error: El nombre del dueño es muy corto!’
SELECT @ReturnValue AS Retorno@Mensaje AS Mensaje
END
–Verificamos que la marca introducida no sea muy corto
IF(LEN(@Marca) < 4)
BEGIN
SET @ReturnValue = -500000
SET @Mensaje = ‘Error: El nombre de la marca es muy corto!’
SELECT @ReturnValue AS Retorno@Mensaje AS Mensaje
END
–Verificamos si la tarjeta ya existe en la base de datos
IF(@Comparador != 0)
BEGIN
SET @ReturnValue = -600000
SET @Mensaje = ‘Error: Esta tarjeta ya se encuentra registrada en la base de datos!’
SELECT @ReturnValue AS Retorno@Mensaje AS Mensaje
END

 

Un ejemplo de lo que retorna:

return multiple

Si bien esto es útil para detectar todos los errores básicos, no nos sirve al momento de querer extraer un valor específico ya que devuelve muchas tablas resultado y el programa solo podrá extraer una de las tablas resultado(la ultima).

 

Creamos la tabla de ST_Historial

Decidimos que agregar esta nueva tabla podría facilitar las consultas sobre movimientos y así saber con cual tarjeta se realizaron, la otra opción era la de agregar una llave foránea de número de tarjeta a la tabla de movimientos pero nos pareció mejor la tabla separada.

Esquema de la tabla:

PK_ID          bigint
FK_Cuenta  bigint
FK_Tarjeta  bigint
Fecha*         date

El campo de fecha puede que no sea necesario, pero lo vamos a dejar así por el momento.

Diagrama V1.1

Considerando una nueva tabla

Estamos considerando agregar una nueva tabla para el historial de tarjetas, la idea de esta tabla es ver las tarjetas que se fueron conectando a una cuenta, para mantener un registro de todas las tarjetas asociadas a la cuenta incluyendo la asociada actualmente.

Creando procedimientos almacenados de borrar y modificar

Empezamos a crear los procedimientos almacenados para borrar y modificar, básicamente es copiar la mayoría de los procedimientos almacenados para insertar (en especial las verificaciones). Esto con el fin de ya poder realizar pruebas con las aplicaciones web y de administrador.

Procedimientos almacenados para insertar

Terminamos todos los procedimientos almacenados para insertar,  todos tienen TRY/CATCH y algunas verificaciones básicas para ayudarnos a detectar errores comunes fácilmente.

Entre ellos están:

  • Faltan parámetros
  • Llaves foráneas inexistentes
  • Números de llaves primarias de tamaño incorrecto
  • Formato de parámetros incorrecto

[SOLUCIONADO]Problema con la verificación del correo

Encontramos un codigo muy simple pero efectivo que nos ayuda a revisar que el correo tenga por lo menos un formato valido <string>@<string>.<string>.

Este es el código:

IF(@Correo NOT LIKE ‘%[a-z,0-9,_,-]%@%[a-z,0-9,_,-]%.%[a-z]%’)

 

Fuente de la información:

http://stackoverflow.com/questions/801166/sql-script-to-find-invalid-email-addresses

Problema con la verificación del correo

Estamos tratando de verificar si una dirección de correo registrada por el usuario es valida desde el SQL (sin que la aplicación tenga que hacerlo) pero no sabemos que cosas podemos usar para manipular las variables de tipo nvarchar.

Este es el codigo:

–Verificamos si el correo es valido
IF(@Correo no es valido)
BEGIN
SET @ReturnValue = -400000
SET @Mensaje = ‘Error: El formato del correo no es valido!’
SELECT@ReturnValue AS Retorno, @Mensaje AS Mensaje
END

Estamos buscando información sobre como hacerlo.

[SOLUCIONADO] No esta detectando NULL

La solución al problema era simplemente cambiar esto:

IF((@NumTarjeta = NULL) OR (@Mes = NULL) OR (@Año = NULL) OR (@Dueño = NULL) OR (@Tipo= NULL) OR (@Marca= NULL) OR (@Saldo= NULL))

Por esto:

IF((@NumTarjeta IS NULL) OR (@Mes IS NULL) OR (@Año IS NULL) OR (@Dueño IS NULL) OR (@Tipo IS NULL) OR (@Marca IS NULL) OR (@Saldo IS NULL))

Fuente de la información:

http://msdn.microsoft.com/en-us/library/ms188795.aspx

Problema con la detección NULL

Estamos verificando en los procedimientos almacenados si alguno de los datos que entran como parámetros es NULL pero al parecer nunca cae en la condición.

Este es el código:

CREATE PROCEDURE [dbo].[SP_InsertarTarjeta]
@NumTarjeta bigint = NULL,
@Mes tinyint = NULL,
@Año smallint = NULL,
@Dueño nvarchar(50) = NULL,
@Tipo bit = NULL,
@Marca nvarchar(50) = NULL,
@Saldo float = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @ReturnValue int
DECLARE @Mensaje nvarchar(MAX)
DECLARE @Comparador int

BEGIN TRY
SET @Comparador = (SELECT COUNT(*) FROM ST_Tarjetas WHERE Numero_Tarjeta = @NumTarjeta)
SET @ReturnValue = 0
–Verificamos si hay campos vacíos
IF((@NumTarjeta = NULL) OR (@Mes = NULL) OR (@Año = NULL) OR (@Dueño = NULL) OR (@Tipo= NULL) OR (@Marca= NULL) OR (@Saldo= NULL))
BEGIN
SET @ReturnValue = -100000
SET @Mensaje = ‘Error: Faltan parametros de entrada!’
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
END
–Verificamos si el número de tarjeta es valido
IF(@NumTarjeta < 1000000000000000 OR @NumTarjeta > 9999999999999999)
BEGIN
SET @ReturnValue = -200000
SET @Mensaje = ‘Error: El número de tarjeta no es valido!’
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
END
–Verificamos si la fecha de expiración es valida
IF(@Mes > 12 OR @Mes < 1 OR @Año < 1900 OR @Año > 2099)
BEGIN
SET @ReturnValue = -300000
SET @Mensaje = ‘Error: La fecha de expiración no es valida!’
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
END
–Verificamos que el nombre introducido no sea muy corto
IF(LEN(@Dueño) < 5)
BEGIN
SET @ReturnValue = -400000
SET @Mensaje = ‘Error: El nombre del dueño es muy corto!’
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
END
–Verificamos que la marca introducida no sea muy corto
IF(LEN(@Marca) < 4)
BEGIN
SET @ReturnValue = -500000
SET @Mensaje = ‘Error: El nombre de la marca es muy corto!’
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
END
–Verificamos si la tarjeta ya existe en la base de datos
IF(@Comparador != 0)
BEGIN
SET @ReturnValue = -600000
SET @Mensaje = ‘Error: Esta tarjeta ya se encuentra registrada en la base de datos!’
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
END
–Si todos los datos estan bien insertamos y retornamos 0
IF(@ReturnValue = 0)
BEGIN
BEGIN TRANSACTION
SET @ReturnValue = 0
SET @Mensaje = ‘Procedimiento ejecutado exitosamente’
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
INSERT INTO ST_Tarjetas VALUES(@NumTarjeta, @Mes, @Año, @Dueño, @Tipo, @Marca, @Saldo)
COMMIT
END
–Termina el TRY
END TRY
–Comienza el CATCH
BEGIN CATCH
ROLLBACK TRANSACTION
SET @ReturnValue = ERROR_NUMBER()
SET @Mensaje = ERROR_MESSAGE()
SELECT @ReturnValue AS Retorno, @Mensaje AS Mensaje
–Termina el CATCH
END CATCH

END

 

Hacemos énfasis en esta parte:

–Verificamos si hay campos vacíos
IF((@NumTarjeta = NULL) OR (@Mes = NULL) OR (@Año = NULL) OR (@Dueño = NULL) OR (@Tipo = NULL) OR (@Marca = NULL) OR (@Saldo = NULL))
BEGIN
SET @ReturnValue = -100000
SET @Mensaje = ‘Error: Faltan parametros de entrada!’
SELECT @ReturnValue AS Retorno@Mensaje AS Mensaje
END

Luego de correrlo, intenta insertar NULL y luego al CATCH

[SOLUCIONADO]Problema con el return en TRY/CATCH

La solución al problema era simplemente cambiar esto:

CREATE PROCEDURE [dbo].[SP_InsertarTarjeta]
@NumTarjeta bigint,
@Mes tinyint,
@Año smallint,
@Dueño nvarchar(50),
@Tipo bit,
@Marca nvarchar(50),
@Saldo float

 

Por esto:

CREATE PROCEDURE [dbo].[SP_InsertarTarjeta]
@NumTarjeta bigint = NULL,
@Mes tinyint = NULL,
@Año smallint = NULL,
@Dueño nvarchar(50) = NULL,
@Tipo bit = NULL,
@Marca nvarchar(50) = NULL,
@Saldo float = NULL

 

Fuente de la información:

http://stackoverflow.com/questions/1929155/stored-procedure-expects-parameter-which-was-not-supplied

Problema con el return en TRY/CATCH

Estuvimos revisando como usar el TRY/CATCH en nuestros procedimientos almacenados y como obtener un valor de retorno en ellos en caso de que haya algún error pero no logramos que nos devuelva los datos que solicitamos cuando enviamos espacios nulos.

Este es el código:

CREATE PROCEDURE [dbo].[SP_InsertarTarjeta]
@NumTarjeta bigint,
@Mes tinyint,
@Año smallint,
@Dueño nvarchar(50),
@Tipo bit,
@Marca nvarchar(50),
@Saldo float
AS
BEGIN
SET NOCOUNT ON
DECLARE @ReturnValue int

BEGIN TRY
INSERT INTO ST_Tarjetas VALUES(@NumTarjeta, @Mes, @Año, @Dueño, @Tipo, @Marca, @Saldo)
SET @ReturnValue = 0

SELECT
@ReturnValue AS Retorno
END TRY

BEGIN CATCH
SET @ReturnValue = ERROR_NUMBER()

SELECT
@ReturnValue AS Retorno,
ERROR_MESSAGE() AS Mensaje
END CATCH

END

 

Esto es lo que retorna:

Msg 201, Level 16, State 4, Procedure SP_InsertarTarjeta, Line 0
Procedure or function ‘SP_InsertarTarjeta’ expects parameter ‘@NumTarjeta’, which was not supplied.

Lo anterior es exactamente lo mismo que nos devolvería el procedimiento si no estuviera el TRY/CATCH.

Fuente de la información:

http://msdn.microsoft.com/es-es/library/ms175976.aspx

Cambio de tipo de variable

Notamos que muchas de las funciones en las que hay que trabajar usan porcentajes, lo cual implica trabajar con valores no enteros. Por eso vamos a cambiar algunas variables de tipo bigint/int a float en las tablas para evitar problemas en el futuro.

Ejemplo de codigo:

USE [SistemaTarjetas]

GO

ALTER TABLE <Nombre de tabla>
ALTER COLUMN <Nombre de columna> FLOAT NOT NULL

Las tablas que vamos a cambiar:

  • ST_Tarjetas

 

Cambio de planes

Nos desviamos temporalmente del diseño de los procedimientos almacenados más complejos para crear primero los de insertar, borrar, modificar y cargar tablas para utilizarlos en las aplicaciones. Esto también nos sirve para ir revisando algunos datos aunque para esta tarea programada parece ser más sencillo.

Dato importante: Debemos recordar en agregar las sentencias de “TRY” y “CATCH” a los procedimientos almacenados ya que no los hicimos en la primera tarea programada.

Planeando los Stored Procedures

Revisamos una guía sobre cálculos con fechas ya que perdimos el enlace que usamos para la tarea programada anterior. La información más completa y detallada que encontramos fue en este enlace: http://www.codeproject.com/Articles/566542/Date-and-Time-Data-Types-and-Functions-SQL-Server

Creando el diseño de la base de datos

Empezamos a crear un diseño básico sobre como creemos que debe quedar la base de datos y hacemos un pequeño listado de lo que creemos que hay que empezar a hacer para la tarea programada. Creamos las tablas con las respectivas llaves y mas o menos la forma en que se van a conectar basándonos en los datos de la especificación.

Diagrama V1

Bitacora de Progreso

Se crea el blog en donde se almacenara la bitácora para la segunda tarea programada. La razón por la cual usamos este medio es debido a los problemas que el servicio de www.webs.com tiene en estos momentos. Recién estamos entendiendo como funciona esta pagina por lo cual se esperan muchas modificaciones y alteraciones en el contenido.

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.