Introduction: In this article i
will explain what is stored procedure in Sql server and how to create basic stored
procedure for insert/save, update, delete, search and bind operations on sql
server database table that are required in every web application.
In previous article i explained 20 main differences between Stored procedures and Functions and Example to explain Transaction in Sql server using Asp.net and What is cursor? Advantages and disadvantages,when to use and example to explain and Return data through Output Parameter in stored procedure in asp.net? and Create Sql server database script and Create database from that script and Bind and implement search gridview records in asp.net using If Else If in Sql server
In previous article i explained 20 main differences between Stored procedures and Functions and Example to explain Transaction in Sql server using Asp.net and What is cursor? Advantages and disadvantages,when to use and example to explain and Return data through Output Parameter in stored procedure in asp.net? and Create Sql server database script and Create database from that script and Bind and implement search gridview records in asp.net using If Else If in Sql server
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
|
Data Type
|
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 [dbo].[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 [dbo].[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 [dbo].[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 [dbo].[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 [dbo].[BindBookDetails_Sp]
AS
BEGIN
SELECT * FROM tbBooks
END
>> Stored procedure to search any
book based on BookId from tbBooks table
CREATE PROCEDURE [dbo].[SearchBookRecord_Sp]
(
@BookId int
)
AS
SELECT *
FROM tbBooks WHERE
@BookId=@BookId
Now over to you:
" I hope you have got the way to create basic stored procedures that are required in every web application and
If you like my work; you can appreciate by leaving your comments, hitting
Facebook like button, following on Google+, Twitter, Linked in and Pinterest,
stumbling my posts on stumble upon and subscribing for receiving free updates
directly to your inbox . Stay tuned and stay connected for more technical
updates."
If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..