Creación de Procedures para la aplicación de Escritorio 2

Creador del post: Edward Ovares Villegas.
Inicio: 15:30 Fin: 20:00
Horas trabajadas: 4.5

Se crearon los procedures de AddInterprete,DeleteInterprete,GetInterprete,GetInterpreteA,UpdateInterprete.

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

CREATE PROCEDURE [dbo].[MC_SP_AddInterprete]
@Nombre VARCHAR(50),
@POST_BY VARCHAR(20),
@POST_IN VARCHAR(20),
@ReturnValue INT OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
IF(EXISTS (SELECT * FROM [dbo].[MC_INTERPRETE] AS I WHERE [Nombre]=@NOMBRE))
BEGIN
PRINT 'EL INTERPRETE YA EXISTE.';
SET @ReturnValue = 0;
END
ELSE
BEGIN
INSERT INTO [MC_INTERPRETE]
([Nombre],[Activo],[POST_BY],[POST_IN],[POST_DATE])
VALUES(@Nombre,1,@POST_BY,@POST_IN,GETDATE());
PRINT 'SE HA INGRESADO EL INTERPRETE.';
SET @ReturnValue = 1;
END
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
PRINT 'Error: Rolling back transaction MC_SP_AddInterprete';
SET @ReturnValue = -1;
RETURN @@ERROR*-1;
END CATCH;
END

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

CREATE PROCEDURE [dbo].[MC_SP_DeleteInterprete]
@ID INT,
@Activo INT,
@ReturnValue INT OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE [dbo].[MC_INTERPRETE]
SET [Activo]=@ACTIVO
WHERE [ID]=@ID;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION; -- Commit transaction
PRINT 'Commit transaction.';
SET @ReturnValue = 1;
RETURN 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_DeleteInterprete';
SET @ReturnValue = -1;
RETURN @@ERROR*-1;
END CATCH;
END

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

CREATE PROCEDURE [dbo].[MC_SP_GetInterprete]
@ret int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY --Inicio del TRY
BEGIN TRANSACTION
SELECT * FROM [dbo].[MC_INTERPRETE];
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_GetInterprete';
--RETURN @@ERROR*-1;
SET @ret = -1;
END CATCH;
END;

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

CREATE PROCEDURE [dbo].[MC_SP_GetInterpreteA]
@ret int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY --Inicio del TRY
BEGIN TRANSACTION
SELECT * FROM [dbo].[MC_INTERPRETE] WHERE [Activo] = 1;
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_GetInterpreteA';
--RETURN @@ERROR*-1;
SET @ret = -1;
END CATCH;
END;

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

CREATE PROCEDURE [dbo].[MC_SP_UpdateInterprete]
@ID INT,
@Nombre VARCHAR(50),
@ReturnValue INT OUTPUT
AS
SET @ReturnValue = 0;
BEGIN
BEGIN TRY
BEGIN TRANSACTION
IF(EXISTS (SELECT * FROM [dbo].[MC_INTERPRETE] AS I
WHERE I.[Nombre] = @Nombre
))
BEGIN
PRINT 'YA EXISTE UN INTERPRETE CON ESOS DATOS.';
SET @ReturnValue = 0;
END
ELSE
BEGIN
UPDATE [dbo].[MC_INTERPRETE]
SET [Nombre] = @Nombre
WHERE [ID]=@ID;
PRINT 'SE HA ACTUALIZADO EL INTERPRETE';
SET @ReturnValue = 1;
END
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
PRINT 'Error: Rolling back transaction MC_SP_UpdateInterprete';
SET @ReturnValue = -1;
RETURN @@ERROR*-1;
END CATCH;
END

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