25 Dec 2017

What is Store procedure in SQL server

What is Store procedure in SQL server :-

Stored procedures are database objects used to store any type of T-SQL statements.

Advantage: we code the T-SQL script once. Compile it and store the same in the
Stored procedure Database object.

Whenever we execute the procedure, above compiled code is auto executed.

Compilation means converting high level language code to machine understand format.

               
Purpose of stored procedures:
                                1. Used for data validations
                                2. Used for dynamic SQL programs
                                3. Used for data Formatting and reporting

Types of stored procedures:
                                1. System predefined stored procedures
                                2. User defined stored procedures

                                3. Extended stored procedures

How to create insert procedure :-

EX-         

create table Employee
(
Empid int primary key identity(1,1),
Fname varchar(50),
Lname varchar(50),
Gender int,
Country int,
State int,
DOB datetime,
Email varchar(50),
Password varchar(50),
Salary float,
Hobbies varchar(100),
File_Upload varchar(100),
)

Value insert procedure:-

create proc usp_employee_insert
@Fname varchar(50),
@Lname varchar(50),
@Gender int,
@Country int,
@State int,
@DOB datetime,
@Email varchar(50),
@Password varchar(50),
@Salary float,
@Hobbies varchar(100),
@File_Upload varchar(100)
as
begin
insert into Employee(Fname,Lname,Gender,Country,State,DOB,Email,Password,Salary,Hobbies,File_Upload) values(@Fname,@Lname,@Gender,@Country,
@State,@DOB,@Email,@Password,@Salary,@Hobbies,@File_Upload)
end

How to create get procedure:-

create proc usp_employee_get
as
begin
select * from Employee
end

How to create update procedure:-

create proc usp_employee_update
@Empid int,
@Fname varchar(50),
@Lname varchar(50),
@Gender int,
@Country int,
@State int,
@DOB datetime,
@Email varchar(50),
@Password varchar(50),
@Salary float,
@Hobbies varchar(100),
@File_Upload varchar(100)
as
begin
update Employee set Fname=@Fname,Lname=@Lname,Gender=@Gender,Country=@Country,State=@State,DOB=@DOB,Email=@Email,Password=@Password,Salary=@Salary
,Hobbies=@Hobbies,File_Upload=@File_Upload where Empid=@Empid
end

How to create Edit Procedure:-


create proc usp_employee_edit
@Empid int
as
begin
select * from Employee where Empid=@Empid
end

No comments:
Write comments

Contact Form

Name

Email *

Message *

© 2014 Rock Programmer . Designed by Bloggertheme9
Powered by Rock Programmer.