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’);
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
set nocount on
selelct * from Company
Create a stored procedure with input and output
Create PROCEDURE samplewith@input text ,@employeecount int outputas