Post
Ask Question

How To Avoid ELSE-IF Statement In Stored Procedure In SQL Server

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]  
  30.   
  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]  
  15.   
  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.   
  26.   
  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  
  34.   
  35.            ELSE IF(@CompanyID!=0 AND @ItemID!=0 AND @DepartmentID!=0 AND @GradeID!=0)  
  36.            BEGIN  
  37.   
  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  
  43.   
  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. -- =============================================  
  13.   
  14. Create PROCEDURE [dbo].[Get_LSCurrentStock]  
  15.   
  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  
  28.   
  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.

 
Next Recommended Reading Dropping Multiple Stored procedures at once in SQL SERVER 2008