A community member has associated this post with a similar question:
SQL : Stored Procedure variables if returning 0 or null values do not run next steps
Only moderators can edit this content.
SQL : Stored Procedure variables if returning 0 or null values do not run next steps
Hi Team - I have a below Procedure which is running fine but i have a requirement where if any of the 4 variables are returning 0 or NULL value do not run the next scripts/steps.
I have highlighted the script not below.
Could you please help on how i add the condition where it shouldn't execute next insert scripts.Create PROCEDURE [FIN].[Test] @PeriodToAllocate varchar(8) /* */ AS SET NOCOUNT ON DECLARE @ErrorMessage varchar(255)
DECLARE @AllocationId int = ISNULL( (SELECT AID FROM CD.[FIN].[Test] WHERE DT_DELETE IS NULL AND NAME = 'Test'), 0 )
IF @AllocationId = 0 BEGIN -- if ID can not be located, raise error SET @ErrorMessage = N'usp_nL_Allo: Unable to locate Allocation ID for I.' RAISERROR(@ErrorMessage, 10, 1) END ELSE BEGIN -- if processing for a period that already has been written to STG, raise error IF EXISTS( SELECT TOP 1 NAME FROM [F].[PnL_NES] WHERE _NAME = @PeriodToAllocate AND DT_DELETE IS NULL AND ALLOCATION_ID = @AllocationId ) BEGIN SET @ErrorMessage = N'usp_nL_Allo: Trying to process period where period data already exists. Period: ' + @PeriodToAllocate + '.' RAISERROR(@ErrorMessage, 10, 1) END ELSE BEGIN DECLARE @PeCO numeric(19,5) DECLARE @PeIB numeric(19,5) DECLARE @PeriodnLR numeric(19,5) DECLARE @PeriodnL numeric(19,5)
--4 variables **Set @PeCO = ( SELECT money FROM FI_MONTHLY WHERE _MONTH = @PeriodToAllocate AND DT_DELETE IS NULL )** **Set @PeIB = ( SELECT REV FROM FIMONTHLY WHERE MONTH = @PeriodToAllocate AND DT_DELETE IS NULL )** **Set @PeriodnLR = ( SELECT SUM(AMOUNT) FROM FIN.PNES L JOIN CDW.S_RAW] GL ON L.GCOUNT = GUNT WHERE PERIOD_NAME = @PeriodToAllocate AND DT_DELETE IS NULL )** **Set @PeriodnL = ( SELECT SUM(AMOUNT) FROM FIN.PNES L JOIN CDW.TS_RAW] GL ON L.GCCOUNT = GUNT WHERE PERIOD_NAME = @PeriodToAllocate AND DT_DELETE IS NULL )** **--IF ANYONE OF THE 4 VARIABLES ABOVE VALUE IS RETURNING 0 or NULL value, DO NOT PROCESS/RUN THE STEPS BELOW.** Declare @GugCOS varchar (12)= 'OS' DECLARE @CosDiff numeric(19,5) = 0 SET @CosDiff = @PeCO - @PeIB IF @CosDiff <> 0 BEGIN INSERT INTO [F].[PINES] ( [ID], [NAME], [COSR], [GLNT], [AMSD], [ORAER], [S_ID], [LN], [OTE] ) VALUES ( @AllocationId, @PeriodToAllocate, '-', @GugCOS, @CosDiff, -1, -1, 0, 'Ig' ) END Declare @GEV varchar (12)= 'IREV' DECLARE @RevDiff numeric(19,5) = 0 SET @RevDiff = @PeriodnLR - @PeriodnL IF @RevDiff <> 0 BEGIN INSERT INTO [F].[PINES] ( [ID], [NAME], [COSR], [GLNT], [AMSD], [ORAER], [S_ID], [LN], [OTE] ) VALUES ( @AllocationId, @PeriodToAllocate, '-', @GEV, @RevDiff, -1, -1, 0, 'Ig' ) END END
END SET NOCOUNT OFF