SQL : Stored Procedure variables if returning 0 or null values do not run next steps

Question

Hi Team - If any of the 4 variables are returning 0 or NULL value do not run the next insert scripts. Could you please help how i can write a condition. TIA

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/scripts 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

Answer

Hi @kkran

Try something like this:

if isnull(@PeCO,0)=0 or isnull(@PeIB,0)=0 or isnull(@PeriodnLR,0)=0 or isnull(@PeriodnL,0)=0
print 'exist 0 or NULL value'
else 
 begin
  --your insert scripts
 end

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Share via

SQL : Stored Procedure variables if returning 0 or null values do not run next steps

1 answer