Stored Procedure In SQL Server

Kajasumanie Kanapathipillai
2 min readDec 9, 2019
https://www.mindstick.com/Articles/202/the-detailed-concept-of-stored-procedure-in-sql-server

I have started to learn about stored procedure in SQL server .I have been worked in entity framework ,stored procedure is easy and past .In this article i will give you a basic idea about stored procedure .

The stored procedure is a set of stored SQL commands that are compiled on the database server.Once stored procedure saved ,applications can execute the stored procedure again and again without sending it to the database server again without compiling it again.And also, Stored procedures improve performance by reducing network traffic and CPU load.

This is used to perform tasks within the database, whether it be to INSERT, UPDATE, DELETE, SELECT, send return values, send output parameters, send e-mail, call command-line arguments, encapsulate business logic, enforce data integrity, or any combination thereof.

Blow I have created two table and wrote simple stored procedure.

Create two table name Employee and Company


CREATE TABLE Employee(ID integer PRIMARY KEY, Name text,salary int,company-name text);
CREATE TABLE Company(Id integer PRIMARY KEY, Name text,location text);

Create few records in this table

INSERT INTO Employee VALUES(1,’Tom’,8,’Jadon’);
INSERT INTO Employee VALUES(2,’Lucy’,6,’Jadon’);
INSERT INTO Employee VALUES(3,’Frank’,4,’Jadon’);
INSERT INTO Employee VALUES(4,’Jane’,3,’Jadon’);
INSERT INTO Company VALUES(5,’Jadon’,’nugegoda’);
COMMIT;

Display all the records from the table

SELECT * FROM Company;
SELECT * FROM Employee;

Create a stored procedure to get all employee

create procedure getallemployeeasset nocount onselect * from Companygo

Create a stored procedure to get all company

create procedure getallemployee
as
begin
set nocount on
selelct * from Company
end

Create a stored procedure with input and output

Create PROCEDURE samplewith@input text ,@employeecount int outputas

--

--