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

Creador del post: Edward Ovares Villegas.
Inicio: 17:30 Fin: 23:00
Horas trabajadas: 5.5

Se crearon los procedures de AddCancion, GetCancion, GetCancionA, UpdateCancion, UpdateEstadoCancion.

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

CREATE PROCEDURE [dbo].[MC_SP_AddCancion]
@Interprete INT,
@Nombre VARCHAR(50),
@Logo IMAGE,
@FechaIngreso DATETIME,
@POST_BY VARCHAR(20),
@POST_IN VARCHAR(20),
@ReturnValue INT OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
IF(EXISTS (SELECT * FROM [dbo].[MC_CANCION] AS C
WHERE (C.FK_INTERPRETE = @Interprete AND
C.Nombre = @Nombre)
))
BEGIN
PRINT 'LA CANCION YA EXISTE';
SET @ReturnValue = 0;
END
ELSE
BEGIN
INSERT INTO [MC_CANCION]
([FK_INTERPRETE],[Nombre],[Logo],[FechaIngreso],[CantidadVendida],[Activo],[POST_BY],[POST_IN],[POST_DATE])
VALUES(@Interprete,@Nombre,@Logo, @FechaIngreso,0,1,@POST_BY,@POST_IN, GETDATE());
PRINT 'SE HA INGRESADO EL LA CANCION';
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_AddCancion';
SET @ReturnValue = -1;
RETURN @@ERROR*-1;
END CATCH;
END

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

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

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

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

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

CREATE PROCEDURE [dbo].[MC_SP_UpdateEstadoCancion]
@ID INT,
@ACTIVO BIT,
@ReturnValue INT OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE [dbo].[MC_CANCION]
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_UpdateEstadoCancion';
SET @ReturnValue = -1;
RETURN @@ERROR*-1;
END CATCH;
END

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

CREATE PROCEDURE [dbo].[MC_SP_UpdateCancion]
@ID INT,
@Interprete INT,
@Nombre VARCHAR(50),
@Logo IMAGE,
@ReturnValue INT OUTPUT
AS
SET @ReturnValue = 0;
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE [dbo].[MC_CANCION]
SET [FK_INTERPRETE] =
CASE
WHEN [FK_INTERPRETE]!=@Interprete THEN @Interprete
ELSE [FK_INTERPRETE]
END,
[Nombre] =
CASE
WHEN [Nombre]!=@Nombre THEN @Nombre
ELSE [Nombre]
END,
[Logo] =
CASE
WHEN [Logo]!=@Logo THEN @Logo
ELSE [Logo]
END
WHERE ID=@ID;

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_UpdateCancion';
SET @ReturnValue = -1;
RETURN @@ERROR*-1;
END CATCH;
END;

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