A Stored Procedure is the assortment of sensible gatherings of SQL Statements. Put away Procedures worker a significant reason in playing out a particular assignment. The principle benefit of Stored Procedures is for expanding the presentation of the information base. In this article I will utilize a Stored Procedure and do the addition, refresh and erase tasks utilizing a Stored Procedure. In this article I won't utilize the front end, all the undertaking I will do in the backend.
Step 1
Start the SQL Server and create a table.
CREATE TABLE employee1
(
id INTEGER PRIMARY KEY,
first_name VARCHAR(10),
last_name VARCHAR(10),
salary DECIMAL(10,2),
country VARCHAR(20),
)
Step 2
Now we need to insert the data in the table using the insert query.
INSERT INTO emp
VALUES
(
1, 'Yaten', 'Sharma', 15000, 'India'
);
INSERT INTO emp
VALUES
(
2, 'Shiva', 'Sharma', 12000, 'Australia'
);
INSERT INTO emp
VALUES
(3, 'Reenna', 'Gupta', 18000, 'USA');
INSERT INTO emp
VALUES
(
4, 'Priyanka', 'Updhayay', 16000, 'India'
);
INSERT INTO emp
VALUES
(
5, 'Rahul', 'Updhayay', 17000, 'India'
);
We can check whether the data was inserted or not using the select query.
select * from emp;
Figure 1: Select table
Step 2
After inserting the data, now we will create the Stored Procedure.
create procedure InsertUpdate
(
@id INTEGER,
@first_name VARCHAR(10),
@last_name VARCHAR(10),
@salary varchar(20),
@country VARCHAR(20),
@StatementType nvarchar(20) = ''
)
AS
BEGIN
IF @StatementType = 'Insert'
BEGIN
insert into emp(id,first_name,last_name,salary,country) values( @id, @first_name, @last_name, @salary, @country)
END
IF @StatementType = 'Select'
BEGIN
select * from emp
END
IF @StatementType = 'Update'
BEGIN
UPDATE emp SET
First_name = @first_name, last_name = @last_name, salary = @salary,
country = @country
WHERE id = @id
END
end
For performing the delete operation we also need to create another procedure for performing the delete operation.
Delete Stored Procedure
create procedure deleted
(
@id integer,
@StatementType nvarchar(20) = ''
) as begin IF @StatementType = 'Delete' BEGIN
DELETE FROM
emp
WHERE
id = @id END end
Step 3
Now we will perform the insert operation using a Stored Procedure, for that we need to write the execute query.
Query for the insert using execute the Stored Procedure:
exec InsertUpdateDelete @id = 6,
@first_name = 'Shobit',
@last_name = 'Pandey',
@salary = 32000,
@country = 'Canada',
@StatementType = 'Insert'
Now we check whether the data was inserted.
Select * from emp;
Figure 2: Insert procedure
Step 4
Now we will perform the update operation, for that we write the query to update the execute query.
The following is the Query to update using execute in a Stored Procedure:
exec InsertUpdateDelete @id = 3,
@first_name = 'Shobit',
@last_name = 'Pandey',
@salary = 32000,
@country = 'Canada',
@StatementType = 'Update'
Now we will check that the data is updated or not.
Select * from emp;
Figure 3: Updated table
Step 5
Now we need to perform the delete operation using a Stored Procedure.
exec deleted @id = 5,
@StatementType = 'Deleted'
Now we need to check that the data is deleted.
Select * from emp;
Rundown
This article disclosed how to compose a Stored Procedure for addition, refresh and erase tasks and how to execute them utilizing boundaries.
I trust this article is useful for novices in the event that they need to utilize Stored Procedures in SQL Server and play out the supplement, refresh and erase activities.
#erdurgeshsingh
#durgeshbooks
#sqlnoteswithhintsandtricks
.gif)
0 Comments