What is Stored Procedure?

A stored procedure is a pre-compiled group of Transact-SQL statements .We can say a stored procedure is a prepared SQL code that we save so that we can reuse the code over and over again. If a repetitive T-SQL task has to be executed within an application, then the best way for it is to create stored procedure.

It is always recommended to create Stored Procedure instead of writing Inline queries so that we can just call the Stored Procedures whenever required instead of writing Inline queries again and again each time.

You can also pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed to it.

Implementation:
Let's create the table and the basic stored procedure to perform Save, Update, Delete, Bind and Search operation on Sql server database table.

First of all create a database in Sql server and name it "BooksDb" or whatever you want.

Column Name datatype
BookId Int(Primary Key and set Is Identity=true)
BookName varchar(100)
Author varchar(100)
Publisher varchar(200)
Price decimal(18, 2)

Create a table with the columns and data type as shown above and name it "tbBooks" using the script below.

CREATE TABLE tbBooks
(
BookId int IDENTITY(1,1) NOT NULL,
BookName varchar(100) NULL,
Author varchar(100) NULL,
Publisher varchar(200) NULL,
Price decimal(18, 2) NOT NULL
)

Now let's create the basic stored procedure to perform Save, Update, Delete, Bind and Search operation on Sql server database table.

>> Stored procedure to insert book details in tbBooks table

CREATE PROCEDURE InsertBookDetails_Sp
@BookName VARCHAR(100),
@Author VARCHAR(100),
@Publisher VARCHAR(200),
@Price DECIMAL(18,2),
AS
BEGIN
INSERT INTO tbBooks
(BookName,Author,Publisher,Price)
VALUES
(@BookName,@Author,@Publisher,@Price)
END

>> Stored procedure to update book details in tbBooks table

CREATE PROCEDURE UpdateBookRecords_Sp
(
@BookId INT,
@BookName VARCHAR(100),
@Author VARCHAR(100),
@Publisher VARCHAR(200),
@Price DECIMAL(18,2)
)
AS
BEGIN
UPDATE tbBooks SET
BookName=@BookName,
Author=@Author,
Publisher=@Publisher,
Price=@Price
WHERE BookId=@BookId
END

>> Stored procedure to delete book details in tbBooks table

CREATE PROCEDURE DeleteBookRecords_Sp
(
@BookId INT
)
AS
BEGIN
DELETE FROM tbBooks WHERE BookId=@BookId
END

>> Stored procedure to get the records from tbBooks table to bind in any data control e.g. GridView, DataList, Repeater etc.

CREATE PROCEDURE BindBookDetails_Sp
AS
BEGIN
SELECT * FROM tbBooks
END

>> Stored procedure to search any book based on BookId from tbBooks table

CREATE PROCEDURE SearchBookRecord_Sp
(
@BookId int
)
AS
Begin
SELECT * FROM tbBooks WHERE @BookId=@BookId
End