Creación de Procedures para la aplicación de escritorio

Creador del post: Edward Ovares Villegas.
Inicio: 10:00 Fin: 15:15
Horas trabajadas: 5

Se crearon los procedures de CheckAdministrador, AddAdministrador, CheckParametro, GetParametros, UpdateParametro,GetErrorInfo.

--------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[MC_SP_AddAdministrador]
@UserName VARCHAR(50), @Password VARCHAR(30), @ret int OUTPUT
AS
-- Declaraciones de variables
DECLARE @ReturnValue INT;
BEGIN
SET NOCOUNT ON;
BEGIN TRY --Inicio del TRY
BEGIN TRANSACTION

SET @ReturnValue = (SELECT COUNT(MC_ADMINISTRADOR.USUARIO)
FROM [dbo].[MC_ADMINISTRADOR]
WHERE MC_ADMINISTRADOR.USUARIO = @UserName);

IF @ReturnValue = 0 --IF: Usuario no existe
BEGIN
INSERT INTO MC_ADMINISTRADOR --Insert of administrator
VALUES(@UserName,@UserName);
SET @ret = 1;
PRINT 'Insert Administrador successfully.';
END; --END IF
ELSE --ELSE: Usuario existe
BEGIN
PRINT 'WARNING: The user exist.';
SET @ret = 0;
END; --END ELSE
IF @@TRANCOUNT > 0
COMMIT TRANSACTION; -- Commit transaction
PRINT 'Commit transaction.';
RETURN 1;
END TRY
BEGIN CATCH --ERROR in the transaction
EXEC MC_SP_GetErrorInfo;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION; -- ROLLBACK of trasaction
SET @ret = 0;
PRINT 'Error: Rolling back transaction AddAdministrador';
RETURN @@ERROR*-1;
END CATCH;
END;

--------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[MC_SP_CheckAdministrador]
@UserName VARCHAR(50), @Password VARCHAR(30),
@IsValid int OUTPUT,@ID int OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SET @IsValid = (SELECT COUNT(MC_ADMINISTRADOR.USUARIO)
FROM [dbo].[MC_ADMINISTRADOR]
WHERE MC_ADMINISTRADOR.USUARIO = @UserName AND MC_ADMINISTRADOR.CLAVE = @Password);
IF(@IsValid>=1)
BEGIN
PRINT 'Administrador ID.';
SET @ID = (SELECT ID FROM [dbo].[MC_ADMINISTRADOR]
WHERE MC_ADMINISTRADOR.USUARIO = @UserName AND MC_ADMINISTRADOR.CLAVE = @Password);
END
COMMIT TRANSACTION;
PRINT 'Commit transaction of CheckAdministrador.';
RETURN 1;
END TRY
BEGIN CATCH
EXEC MC_SP_GetErrorInfo;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT 'Error: Rolling back transaction of CheckAdministrador.';
RETURN @@ERROR*-1;
END CATCH;
END
GO

--------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[MC_SP_CheckParametro]
@NOMBRE VARCHAR(50)
--,@VALOR MONEY OUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
SELECT *
FROM [dbo].[MC_PARAMETRO] AS P
WHERE P.[Nombre]=@NOMBRE;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION; -- Commit transaction
PRINT 'Commit transaction.';
RETURN 1;
END TRY
BEGIN CATCH
EXEC MC_SP_GetErrorInfo;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT 'Error: Rolling back transaction of MC_SP_CheckParametro.';
RETURN @@ERROR*-1;
END CATCH;
END;
GO

--------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[MC_SP_GetParametro]
@ret int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY --Inicio del TRY
BEGIN TRANSACTION
SELECT * FROM MC_PARAMETRO;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION; -- COMMIT of transaction
PRINT 'Commit transaction.';
SET @ret = 1;
END TRY
BEGIN CATCH --ERROR in the transaction
EXEC MC_SP_GetErrorInfo;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION; -- ROLLBACK of trasaction
PRINT 'Error: Rolling back transaction MC_SP_GetParametro';
--RETURN @@ERROR*-1;
SET @ret = -1;
END CATCH;
END;

--------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[MC_SP_UpdateParametro]
@ID INT,
@VALOR VARCHAR(80)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DECLARE @MON MONEY
SET @MON = (SELECT PARSE(@VALOR AS MONEY));
UPDATE [dbo].[MC_Parametro]
SET [dbo].[MC_Parametro].[VALOR] = @MON
WHERE [dbo].[MC_Parametro].[ID] = @ID;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION; -- Commit transaction
PRINT 'Commit transaction.';
RETURN 1;
END TRY
BEGIN CATCH
EXEC MC_SP_GetErrorInfo;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT 'Error: Rolling back transaction of MC_SP_CheckParametro.';
RETURN @@ERROR*-1;
END CATCH;
END;