Stored Procedure – Basic

What Is A Stored Procedure?
.It is a best way to implement the business logic programming.
.Group of TSQL statements compiled into a single execution plan.
It means one can write the series of SQL statement that will be executed with one name in one shot.
Whenever we execute the SQL statement five steps are performed by SQL:
1) Security Checks.
2) Syntax.
3) Compilation checks.
4) Execution Plan and so forth…

.Provides encapsulation of logic & action.
.Provides additional security (Update table to which we don’t want to allow updates from other location).
.Simplifies management/administration.

Creating/Executing Stored Procedures
.Create Procedure.
.Create Proc.
.Basic Syntax:
– Create Proc
– As –Indicate that from next line TSQL statements starts.

create procedure stp_test
select * from test

execute stp_test
Altering And Dropping SP
.Drop stored procedures using the DROP PROCEDURE statement.
.Alter (change) stored procedures using the ALTER PROCEDURE statement.
.Preserves security settings.

alter procedure stp_test
select name,phone from test

When we use Drop command to drop procedure
drop procedure stp_test
Then SQL create another id for the project (object) hence loses the security setting of the procedure.
Input Parameters
.Allow the caller to pass a data value to the stored procedure for processing.
.Can be set to a constant or assigned to a variable.
create procedure stp_test
@name as varchar(20)
select name,phone from test
where name = @name

execute stp_test ‘Test’
Output Parameters
.A stored procedure can return information to the calling procedure.
.To use an output parameter you specify the OUTPUT keyword in the CREATE PROC and EXEC statements.
create Proc Addition
@Num1 int,
@Num2 int,
@Sum int output
Set @Sum = @Num1 + @Num2

declare @Sum int
exec Addition 1,2, @Sum output
Select @Sum
Recompiling Stored Procedures
.First time execution:
– Resolution : Syntax checking and parsing.
– Optimization : Best way to execute underlying query.
– Compilation : Machine Code Conversion.
– Execution
.Subsequent executions:
– Execution.

When we create a procedure it takes all the indexes of all the objects in the query.
and if any changes occurs in the table aur database structure, procedure doesn’t take that.

.Stored procedures are compiled:
– First time stored procedure is executed (each time SQL Server is restarted).
– If an underlying table changes.
– Manually
. exec sp_recompile
– Sp will be recompiled on the next execution.
– WITH RECOMPILE at build.

#procedure-statement, #stored-procedure

Code Refactoring – Part 3

Making Conditions Easier to Read: There are many ways to do this, we will discuss on few out of them

1) Decompose Conditional: It means though conditions can be read easily even if they are complex in nature but can be difficult in future to read by another developer so break them in the form of method.

In first article, we have seen the below example

if((order.getTotal() > 99 && (stock.CheckStatus(order.getItem()) > order.getQuantity())) || _balance > 4000){
        Console.WriteLine(“Everything is fine.”);

and we have organised it by replacing the temporary variables.

This can be form as per below

         Console.WriteLine(“Everything is fine.”);

public bool checkOrderCustomerStatus(){
     if((order.getTotal() > 99 && (stock.CheckStatus(order.getItem()) > order.getQuantity())) || _balance > 4000){
         return true;
     return false;

2) Consolidate Conditional Expression: If you have code like below where you are checking multiple conditions and returning the common output then you can club those together.

if _balance > 0
return true;

return true;

Then what you can do, you can create a common method which will check these conditions and return the output.

Public bool Validate(){
  if(_balance > 0 || _isEmployee){
   return true;
  else {
   return false;

3)Consolidate Duplicate Conditional Fragments: It deals with the duplicate code, for example assume that you are performing some conditional checks, if condition meet then you are doing some calculation and the assigning the calculated value to a variable and then value you are passing to a function and same code you have return multiple time, then you can take out that code out and write only once.

#code-refactoring, #conditional-refactoring

Code Refactoring – Part 2

Move Method Refactoring:  Move method can be apply with help of below steps

1) Scan the code

2) Check the superclass/subclass

3) Create the new method and rename if necessary

4) Copy the code

5) Replace the original call

Code smell for move method are

>> Feature envy: Method is more related to another class than the class in which it is defined.

>> Inappropriate intimacy: Two classes are not doing more than just calling each others properties and method to server the purpose, so it is better to apply the move method refactoring by looking into the code.

>> Shotgun surgery: This we can notice when we apply the code changes, for example when we make the changes in class A then we need to make some changes in class B and then in class C and so on.

Extract Class Refactoring: As we see that as the project is growing class is also growing with the new properties and methods, even though those are related to that class but can be split into another class and then use in the parent class.

Code smell: large class

Example: Customer class with Address.

Inline Class Refactoring: This refactoring is apply only when we lets say when we have applied the move method, move fields and move class refactoring on a class and there hardly something left, so it will be better to move that content into the another class which we see more related to it.




#code-refactoring, #code-smell, #move-method, #move-method-refactoring, #refactoring

Code Refactoring – Part 1

Refactoring means making the code better, better doesn’t mean faster, better here means better structured / better built / more readable and more understandable to make it easier to work, easier to identify the bugs and fix them, easier to add new features and stop code gradually getting out of control.

Please remember that there are techniques which we can learn from books and stuff on internet but how you can refactor your code that you can do by scanning your code for large methods, large objects, use of temp variables, use of conditions and at last code smell.

In this article we will discuss few ways

-> Extract Method: Extract method is a technique which allows you to create a method for the block of code which you have written for a specific purpose.

Code smell:
Large method >> Large doesn’t mean length it
Comment >> If you have comment for the section of code then that code can be form as method and that method name (Verb Noun combination to create method name) can itself explain the purpose.

-> Inline Method: Inline method  refactoring is exactly opposite of extract method. Inline method is finding a call to a method that adds no value, no additional clarity and no additional modularity and then remove that method and replace with the code.

static double calculateAreaOfCircle(double Radius) {
return (getValueOfPI() * Math.Pow(Radius,2));

static double getValueOfPI(){
return Math.PI;

Above method “calculateAreaOfCircle” can be replaced with below code

static double calculateAreaOfCircle(double Radius) {
return (Math.PI * Math.Pow(Radius,2));

-> Replace the temp with the Query refactoring – Inline Temp Refactoring
This technique consists of two part

1) Extract the expression and create method
2) Replace the method name wherever temp variable is used.

Please remember that before applying this technique make sure that in the code you are not modifying the value of temp variable.

static bool CheckWithdrawalStatus(int WithdrawalAmount) {
double availableBalance = _balance – _charges;
if (availableBalance > WithdrawalAmount)
return true;
else {
Console.WriteLine(“You have available ” +  availableBalance + ” balance.”);
return false;

This can be rewrite like below

static bool CheckWithdrawalStatus(int WithdrawalAmount)
if (availableBalance() > WithdrawalAmount)
return true;
Console.WriteLine(“You have available ” + availableBalance() + ” balance.”);
return false;

static double availableBalance(){
return _balance – _charges;

Refactoring That Add Temps
>> Split Temporary Variable: Don’t use the same temp for different reason.

double Price = 100.50;
int Quantity = 10;
double t = Price * Quantity;

double TotalAmount = 5000;
double DiscountAmount = 100;
t = TotalAmount – DiscountAmount;

>> Introduce Explaining Variable: Variable should be self explanatory
Example: Below example is easy to understand but after a week, a month or a year it will take time to understand if new person is trying to debug

if((order.getTotal() > 99 && (stock.CheckStatus(order.getItem()) > order.getQuantity())) || _balance > 4000){
Console.WriteLine(“Everything is fine.”);

This code can be then rewritten by introducing new temp variables

bool freeShipping = order.getTotal() > 99;
bool stockAvailable = stock.CheckStatus(order.getItem()) > order.getQuantity();
bool balanceAvailable = _balance > 4000;

if((freeShipping  && stockAvailable) || balanceAvailable ){
Console.WriteLine(“Everything is fine.”);

>> Remove Assignments to Parameters: This simply means don’t assign the value to the parameters passed as input to the method and then use in the method,instead of doing this introduce new temp variables and use them.