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 instant 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 Work: 

Step 1:

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

createdatabase

Fig: Create Database

OR below is the following query

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

table

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Fig: InvStockMaster

OR below is the following query

Our Database and Table have created so we will add some demo data for get result after execute stored procedure

Here some demo Data

Now we will create Store procedure using ELSE IF Statement

Above code we can see that there are code repeat. It’s is not good. In this scenario the question is arise what is code repeating

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

Let’s go for code explaination

Above codes we can see that we have used ELSE-IF statement which are some condition

And we have Selected ItemID,UnitPrice and CurrentStock with some condition with Where clause

Finally we will create Store procedure without ELSE-IF Statement

Let’s go for code explaination

Above codes are just Select query.

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 to use ELSE-IF Statment.

 

Happy Coding 🙂

 

Leave a Reply

Your email address will not be published. Required fields are marked *