Please disable your adblock and script blockers to view this page
 

Sql server stored procedure for insert,update,delete,bind and search operation in table

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

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."
Previous
Next Post »

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.. ConversionConversion EmoticonEmoticon