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 1

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".