USE [AIADatabaseV5] GO /****** Object: StoredProcedure [dbo].[UpdateLicenseModule] Script Date: 2/1/2018 12:15:55 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Magic Software -- Create date: 10/12/2010 -- Description: update the module status of a license -- ============================================= if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateLicenseModule]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[UpdateLicenseModule] GO CREATE PROCEDURE [dbo].[UpdateLicenseModule] @iLicenseId INT, @sModuleTitle VARCHAR(50), @iStatus INT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION DECLARE @sErrorStatus CHAR(2) = 'ok' DECLARE @iModuleId INT SELECT @iModuleId = ResourceModule.Id FROM ResourceModule INNER JOIN ModuleToLicense ON ResourceModule.Id = ModuleToLicense.ModuleId WHERE ResourceModule.Title = @sModuleTitle AND ModuleToLicense.LicenseId = @iLicenseId IF @@ROWCOUNT > 0 BEGIN UPDATE ModuleToLicense SET Status = @iStatus WHERE LicenseId = @iLicenseId AND ModuleId = @iModuleId END ELSE BEGIN INSERT INTO ModuleToLicense (LicenseId, ModuleId, Status) SELECT @iLicenseId, ResourceModule.Id, @iStatus FROM ResourceModule WHERE ResourceModule.Title = @sModuleTitle END COMMIT TRANSACTION SELECT @sErrorStatus as SPStatus END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION SELECT Error_Message() as SPStatus END CATCH END GO