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:
Post a Comment