Introduction

In this article we will discuss about how to avoid ELSE-IF Statement in stored procedure in SQL Server. As we all know that stored procedure is faster than LINQ, so we will fetch data from database using stored procedure.

Background

Here we have to use condition with where clause instance of ELSE-IF Statement in stored procedure. Consider we will fetch Unit Price and Current Stock of values from InvStockMaster table in Database by @CompanyID,@DepartmentID, @ItemID, @GradeID and Other Condition we will fetch same values from Same table and Database by @CompanyID, @ItemID, @DepartmentID, @SupplierID, @BatchID,@GradeID,@LotID. Now firstly we will use ELSE-IF Statement and secondly we will avoid ELSE-IF Statement in stored procedure.

Let’s go for it.

Step 1

Right button click on Databases and Click New Database and give database named InventoryDB. Below is the following picture.

Create Database
Fig: Create Database

OR below is the following query,

  1. CREATE DATABASE InventoryDB;
Step 2

Now we will create table named InvStockMaster.

Right button click on Tables and click New and once Click Table… Below is the following picture.

InvStockMaster
Fig: InvStockMaster

OR below is the following query,
  1. CREATE TABLE [dbo].[InvStockMaster](
  2. [StockID] [int] IDENTITY(1,1) NOT NULL,
  3. [SupplierID] [int] NULL,
  4. [ItemID] [bigint] NOT NULL,
  5. [ItemTypeID] [int] NULL,
  6. [LotID] [bigint] NULL,
  7. [GradeID] [int] NULL,
  8. [BatchID] [bigint] NULL,
  9. [UOMID] [int] NOT NULL,
  10. [ReceiveQty] [decimal](18, 6) NULL,
  11. [ReceiveValue] [decimal](18, 0) NULL,
  12. [LastReceiveDate] [datetime] NULL,
  13. [IssueQty] [decimal](18, 6) NOT NULL,
  14. [IssueValue] [decimal](18, 6) NULL,
  15. [LastIssueDate] [datetime] NULL,
  16. [CurrentRate] [decimal](18, 6) NOT NULL,
  17. [CurrentStock] AS ([ReceiveQty]-[IssueQty]),
  18. [CurrentValue] AS ([ReceiveValue]-[IssueValue]),
  19. [SalesRate] [decimal](18, 6) NOT NULL,
  20. [SalesValue] [decimal](18, 6) NOT NULL,
  21. [LastSalesDate] [datetime] NULL,
  22. [IsActive] [bit] NULL,
  23. [CompanyID] [int] NULL,
  24. [DepartmentID] [int] NOT NULL,
  25. CONSTRAINT [PK_InvStockMaster] PRIMARY KEY CLUSTERED
  26. (
  27. [StockID] ASC
  28. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  29. ) ON [PRIMARY]
  31. GO
Our Database and Table have been created so we will add some demo data for getting results after executing stored procedure.

Here is some demo Data.
  1. GO
  2. INSERT [dbo].[InvStockMaster] ([StockID], [SupplierID], [ItemID], [ItemTypeID], [LotID], [GradeID], [BatchID], [UOMID], [ReceiveQty], [ReceiveValue], [LastReceiveDate], [IssueQty], [IssueValue], [LastIssueDate], [CurrentRate], [SalesRate], [SalesValue], [LastSalesDate], [IsActive], [CompanyID], [DepartmentID]) VALUES (1, 222, 7478, 1, 0, 1, 0, 3, CAST(1000.000000 AS Decimal(18, 6)), CAST(10000 AS Decimal(18, 0)), CAST(N'2016-09-19 17:02:22.820' AS DateTime), CAST(10.000000 AS Decimal(18, 6)), CAST(100.000000 AS Decimal(18, 6)), CAST(N'2016-10-05 00:00:00.000' AS DateTime), CAST(10.000000 AS Decimal(18, 6)), CAST(0.000000 AS Decimal(18, 6)), CAST(0.000000 AS Decimal(18, 6)), NULL, 1, 1, 10)
  3. GO
  4. INSERT [dbo].[InvStockMaster] ([StockID], [SupplierID], [ItemID], [ItemTypeID], [LotID], [GradeID], [BatchID], [UOMID], [ReceiveQty], [ReceiveValue], [LastReceiveDate], [IssueQty], [IssueValue], [LastIssueDate], [CurrentRate], [SalesRate], [SalesValue], [LastSalesDate], [IsActive], [CompanyID], [DepartmentID]) VALUES (2, 225, 7475, 1, 6, 0, 15, 3, CAST(54000.000000 AS Decimal(18, 6)), CAST(54000 AS Decimal(18, 0)), CAST(N'2016-09-20 10:36:55.440' AS DateTime), CAST(10.000000 AS Decimal(18, 6)), CAST(100.000000 AS Decimal(18, 6)), CAST(N'2016-10-05 00:00:00.000' AS DateTime), CAST(10.000000 AS Decimal(18, 6)), CAST(0.000000 AS Decimal(18, 6)), CAST(0.000000 AS Decimal(18, 6)), NULL, 1, 1, 10)
  5. GO
  6. INSERT [dbo].[InvStockMaster] ([StockID], [SupplierID], [ItemID], [ItemTypeID], [LotID], [GradeID], [BatchID], [UOMID], [ReceiveQty], [ReceiveValue], [LastReceiveDate], [IssueQty], [IssueValue], [LastIssueDate], [CurrentRate], [SalesRate], [SalesValue], [LastSalesDate], [IsActive], [CompanyID], [DepartmentID]) VALUES (3, 984, 7481, 4, 12, 0, 10, 3, CAST(1000.000000 AS Decimal(18, 6)), CAST(10000 AS Decimal(18, 0)), CAST(N'2016-09-20 00:00:00.000' AS DateTime), CAST(10.000000 AS Decimal(18, 6)), CAST(100.000000 AS Decimal(18, 6)), CAST(N'2016-10-05 00:00:00.000' AS DateTime), CAST(10.000000 AS Decimal(18, 6)), CAST(0.000000 AS Decimal(18, 6)), CAST(0.000000 AS Decimal(18, 6)), NULL, 1, 1, 10)
  7. GO
Now we will create Stored procedure using ELSE IF Statement.
  1. USE [InventoryDB]
  2. GO
  3. /****** Object: StoredProcedure [dbo].[Get_LSCurrentStock] Script Date: 10/14/2016 2:36:14 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Author: <Author,,Name>
  10. -- Create date: <Create Date,,>
  11. -- Description: <Description,,>
  12. -- =============================================
  13. --EXEC [Get_LSCurrentStock] 1,7474,10,0,0,0,0
  14. Create PROCEDURE [dbo].[Get_LSCurrentStockWithELSEIF]
  16. @CompanyID INT=0
  17. ,@ItemID BIGINT=0
  18. ,@DepartmentID INT=0
  19. ,@SupplierID BIGINT=0
  20. ,@BatchID INT=0
  21. ,@GradeID INT=0
  22. ,@LotID INT=0
  23. AS
  24. BEGIN
  27. IF(@CompanyID!=0 AND @ItemID!=0 AND @DepartmentID!=0 AND @SupplierID!=0 AND @BatchID!=0 AND @LotID!=0)
  28. BEGIN
  29. SELECT TOP 1 ISNULL(ItemID,0) ItemID, ISNULL(CurrentRate,0) UnitPrice, ISNULL(CurrentStock,0) CurrentStock
  30. from InvStockMaster
  31. WHERE ItemID=@ItemID AND DepartmentID=@DepartmentID AND CompanyID=@CompanyID
  32. AND BatchID=@BatchID AND SupplierID=@SupplierID AND LotID=@LotID
  33. END
  35. ELSE IF(@CompanyID!=0 AND @ItemID!=0 AND @DepartmentID!=0 AND @GradeID!=0)
  36. BEGIN
  38. SELECT TOP 1 ISNULL(ItemID,0) ItemID, ISNULL(CurrentRate,0) UnitPrice, ISNULL(CurrentStock,0) CurrentStock
  39. from InvStockMaster
  40. WHERE ItemID=@ItemID AND DepartmentID=@DepartmentID AND CompanyID=@CompanyID
  41. AND GradeID=@GradeID
  42. END
  44. END
In the above code we can see that there are code repeats. It’s not good. In this scenario the question arises, what is code repeating?

In software engineering, don't repeat yourself (DRY) is a principle of software development

Let's go for code explanation.

In the above codes we can see that we have used ELSE-IF statements which are some condition.
  1. IF(@CompanyID!=0 AND @ItemID!=0 AND @DepartmentID!=0 AND @SupplierID!=0 AND @BatchID!=0 AND @LotID!=0)
  2. BEGIN
  3. END
  4. ELSE IF(@CompanyID!=0 AND @ItemID!=0 AND @DepartmentID!=0 AND @GradeID!=0)
  5. BEGIN
And we have Selected ItemID,UnitPrice and CurrentStock with some condition with Where clause.
  1. SELECT TOP 1 ISNULL(ItemID,0) ItemID, ISNULL(CurrentRate,0) UnitPrice, ISNULL(CurrentStock,0) CurrentStock
  2. from InvStockMaster
  3. WHERE ItemID=@ItemID AND DepartmentID=@DepartmentID AND CompanyID=@CompanyID
  4. AND BatchID=@BatchID AND SupplierID=@SupplierID AND LotID=@LotID
Finally we will create Store procedure without ELSE-IF Statement.
  1. USE [InventoryDB]
  2. GO
  3. /****** Object: StoredProcedure [dbo].[Get_LSCurrentStock] Script Date: 10/14/2016 2:36:14 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. -- =============================================
  9. -- Author: <Author,,Name>
  10. -- Create date: <Create Date,,>
  11. -- Description: <Description,,>
  12. -- =============================================
  14. Create PROCEDURE [dbo].[Get_LSCurrentStock]
  16. @CompanyID INT=0
  17. ,@ItemID BIGINT=0
  18. ,@DepartmentID INT=0
  19. ,@SupplierID BIGINT=0
  20. ,@BatchID INT=0
  21. ,@GradeID INT=0
  22. ,@LotID INT=0
  23. AS
  24. BEGIN
  25. SELECT TOP 1 ISNULL(ItemID,0) ItemID, ISNULL(CurrentRate,0) UnitPrice, ISNULL(CurrentStock,0) CurrentStock
  26. from InvStockMaster
  27. WHERE ItemID=@ItemID AND DepartmentID=@DepartmentID AND CompanyID=@CompanyID
  29. And Convert(varchar, BatchID) Like Case When @BatchID = 0 Then '%' Else CONVERT(varchar, @BatchID) End
  30. And Convert(varchar, SupplierID) Like Case When @SupplierID = 0 Then '%' Else CONVERT(varchar, @SupplierID) End
  31. And Convert(varchar, GradeID) Like Case When @GradeID = 0 Then '%' Else CONVERT(varchar, @GradeID) End
  32. And Convert(varchar, LotID) Like Case When @LotID = 0 Then '%' Else CONVERT(varchar, @LotID) End
  33. END
Let’s go for code explanation.
  1. SELECT TOP 1 ISNULL(ItemID,0) ItemID, ISNULL(CurrentRate,0) UnitPrice, ISNULL(CurrentStock,0) CurrentStock
  2. from InvStockMaster
Above codes are just Select query.
  1. WHERE ItemID=@ItemID AND DepartmentID=@DepartmentID AND CompanyID=@CompanyID
  2. And Convert(varchar, BatchID) Like Case When @BatchID = 0 Then '%' Else CONVERT(varchar, @BatchID) End
  3. And Convert(varchar, SupplierID) Like Case When @SupplierID = 0 Then '%' Else CONVERT(varchar, @SupplierID) End
  4. And Convert(varchar, GradeID) Like Case When @GradeID = 0 Then '%' Else CONVERT(varchar, @GradeID) End
  5. And Convert(varchar, LotID) Like Case When @LotID = 0 Then '%' Else CONVERT(varchar, @LotID) End
Here we have used Convert, Like and Case Statement

Convert

The CONVERT() function is a general function that converts an expression of one data type to another.

Ex Convert(varchar, BatchID)

Like

The LIKE operator is used to search for a specified pattern in a column.

Ex Like Case When @BatchID = 0 Then '%' Else CONVERT(varchar, @BatchID) End

Case Statement
The CASE statement has the functionality of an IF-THEN-ELSE statement. You can use the CASE statement within a SQL statement.

Ex Case When @BatchID = 0 Then '%' Else CONVERT(varchar, @BatchID) End

Conclusion

Code repeat is problem in software engineering. In this article we have learned how to avoid code repeat and always we will try to avoid using ELSE-IF Statement.