In this article, we will learn more about creation,updation,deletion and execution using stored procedure with practical examples. The stored procedure is used to execute set of sql statements like Select, Insert, Update and Delete.It also returns value.
Requirement:- This stored procedure will return list of users based on the username as input.Example 1 :
create procedure SP_GetUsers
(
@username varchar(200)
)
as
begin
select * from Users where username=@username
End
Requirement:-
This stored procedure will insert user details into the database, based on the given input.
Example 2 :
create procedure SP_InsertUsers
(
@username varchar(200),
@password varchar(100),
@email varchar(100),
@designation varchar(100),
@status varchar(50)
)
as
begin
insert into Users(username,
password,email,designation,status)
values(@username,@password,@email,@designation,@status)
End
Requirement:-
This stored procedure will Update user details into the database, based on the given input "@pusername".
Example 3 :
create procedure SP_UpdateUsers
(
@pusername varchar(200),
@pemail varchar(100),
@pdesignation varchar(100)
)
as
begin
update users
set @email=@pemail,
@designation = @pdesignation
WHERE @username =@pusername;
End
Requirement:-
This stored procedure will Delete user details from the database, based on the given input "@pusername".
Example 4 :
create procedure SP_DeleteUsers
(
@pusername varchar(200)
)
as
begin
DELETE from users WHERE username =@pusername;
End
Execute Stored Procedure
SP_UpdateUsers 'Suresh','Suresh@gmail.com','Software Engineer'