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

kkran 831 Reputation points
2024-06-24T03:18:24.7366667+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,817 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
97 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,639 questions
0 comments No comments
{count} votes