Sql Server - How To Write a Stored Procedure in SQL Server

Stored Procedure: Stored Procedure in SQL Server can be defined as the set of logical group of SQL statements which are grouped to perform a specific task. There are many benefits of using a stored procedure. The main benefit of using a stored procedure is that it increases the performance of the database.The other benefits of using the Stored Procedure are given below.

Benefits of Using the Stored Procedure

1. One of the main benefits of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become a more important benefit when the bandwidth of the network is less. Since if we send the SQL query (statement) which is executing in a loop to the server through network and the network gets disconnected, then the execution of the SQL statement doesn't return the expected results, if the SQL query is not used between Transaction statement and rollback statement is not used.
2. Compilation step is required only once when the stored procedure is created. Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan whereas the SQL statements need to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time.
3. It helps in re usability of the SQL code because it can be used by multiple users and by multiple clients since we need to just call the stored procedure instead of writing the same SQL statement every time. It helps in reducing the development time.
4. Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving permission on the tables used in the Stored procedure.
5. Sometimes, it is useful to use the database for storing the business logic in the form of stored procedure since it makes it secure and if any change is needed in the business logic, then we may only need to make changes in the stored procedure and not in the files contained on the web server.

How to Write a Stored Procedure in SQL Server

1. Simple Procedure
2. With parament
3. Optional Parameter

• Simple store Procedure

Create Procedure SpGetEmployeeList

as

begin

select name, gender, salary from tblEmployee

end 

• With parameter store Procedure

Create Procedure SpGetEmployeeList

@name nvarchar(100),

@gender nvarchar(10),

@Salary decimal(18,2)

as

begin

insert into tbl_employee(name, gender, salary)

values(@name, @gender, @salary)

end 

• Optional parameter Store Procedure

Create Procedure SpGetEmployeeList

@name nvarchar(100),

@gender nvarchar(10),

@Salary decimal(18,2) = null

as

begin

insert into tbl_employee(name, gender, salary)

values(@name, @gender, @salary)

end 

SQL Server Update with joining two or more table | SQL Server | Jangli C...

C# Enum Example

An enumeration is a set of named integer constants. An enumerated type is declared using the enum keyword.

C# enumerations are value data type. In other words, enumeration contains its own values and cannot inherit or cannot pass inheritance.

Declaring enum Variable

The general syntax for declaring an enumeration is:

enum <enum_name> 
{
   enumeration list 
};

Where,

• The enum_name specifies the enumeration type name.
• The enumeration list is a comma-separated list of identifiers.

Each of the symbols in the enumeration list stands for an integer value, one greater than the symbol that precedes it. By default, the value of the first enumeration symbol is 0. For example:

enum Days { Sun, Mon, tue, Wed, thu, Fri, Sat };

Example

The following example demonstrates use of enum variable:

using System;
namespace EnumApplication
{
   class EnumProgram
   {
      enum Days { Sun, Mon, tue, Wed, thu, Fri, Sat };

      static void Main(string[] args)
      {
         int WeekdayStart = (int)Days.Mon;
         int WeekdayEnd = (int)Days.Fri;
         Console.WriteLine("Monday: {0}", WeekdayStart);
         Console.WriteLine("Friday: {0}", WeekdayEnd);
         Console.ReadKey();
      }
   }
}

When the above code is compiled and executed, it produces the following result:

Monday: 1
Friday: 5

How to update with joining two or more table in SQL Server

Hello friend today we lean about update with joining two or more table.

First we create a database db_company

then we create two table 

then we insert some value in both table 

insert into tbl_Department(name) values('IT Department')

insert into tbl_Department(name) values('HR Department')

insert into tbl_Department(name) values('Marketing')

insert into tbl_employee(DPID, Name, Gender, Salary) values(1, 'JAMES', 'Male', 2000)

insert into tbl_employee(DPID, Name, Gender, Salary) values(1, 'PAUL', 'Male', 2000)

insert into tbl_employee(DPID, Name, Gender, Salary) values(2, 'SARA', 'Female', 2000)

insert into tbl_employee(DPID, Name, Gender, Salary) values(2, 'MARK', 'Male', 2000)

insert into tbl_employee(DPID, Name, Gender, Salary) values(3, 'TOMMY', 'Male', 2000)

insert into tbl_employee(DPID, Name, Gender, Salary) values(3, 'MARY', 'Feale', 2000)

all Employee have same salary 2000. Requirement is Update salary IT Department Salary 2000 to 2500   

update emp

set emp.salary = 2500

from tbl_Employee as emp

left join         tbl_Department as dep

on dep.id = emp.dpid

where dep.name = 'IT Department'

again we update salary Marketing Department Employee 2000 to 3000

update emp

set emp.salary = 3000

from tbl_Employee as emp

left join         tbl_Department as dep

on dep.id = emp.dpid

where dep.name = 'Marketing'

Thank You

Please Like , Share and Subscribe our Youtube channel