Updating multiple rows using single stored proc
The situation arises when we are displaying data in a grid or some other control at front end level, where the user is making corresponding changes in the grid and at last when the user click for update button, the whole changes should be posted back to the database. This can be achieved by two ways
• For each and every updation into the table we have to call a stored procedure.
• To call a stored procedure which can handle every updation to a table
Well if we are going for the first method, for each update we have to call the stored procedure. For example if we have 100 rows to update 100 times we have to call the stored procedure. In the second case, only once we will be calling the stored procedure, which will update all the rows.
Please follow the inline documentation provided inbetween the sql statements
--Creating a table Employee for updating multiple rows
CREATE TABLE Employee
(
EmpID INT PRIMARY KEY,
FirstName VARCHAR(30),
LastName VARCHAR(30),
emailID VARCHAR(30)
)
--Populating the table Employee
INSERT INTO Employee VALUES(1, 'Roji', 'Thomas', 'thomasroji@hotmail.com')
INSERT INTO Employee VALUES(2, 'Subodh', 'Sasidharan', 'subodhs77@hotmail.com')
INSERT INTO Employee VALUES(3, 'Rejin', 'Sudhakaran', 'rejintvm@yahoo.com')
CREATE PROCEDURE [spUpdateEmployee]
/*
Procedure Name :spUpdateEmployee
Input Parameter :strUpdateList - Contains the Employee Update Information
Each column is seperated by ~ and Each row is seperated by ^
(Eg: '1~Rojip~T~rojipt@yahoo.com^2~Subu~S~subodhs77@yahoo.com^3~Reji~S~rejin_s@hotmail.com')
1~Rojip~T~rojipt@yahoo.com - stands for one row to update
Description :Follow the inline documentation
*/
@strUpdateList TEXT
AS
-- @EmpID - To store Employee ID
Declare @EmpID INT
-- @FirstName - To store First Name
Declare @FirstName VARCHAR(30)
-- @LastName - To store First Name
Declare @LastName VARCHAR(30)
-- @emailID - To store email ID
Declare @emailID VARCHAR(30)
-- @strRowItem - To store a single record eg: 1~Rojip~T~rojipt@yahoo.com
Declare @strRowItem VARCHAR(200)
-- @intStartPos is used to mark the begining of the single row
Declare @intStartPos INT
-- @intEndPos is used to mark the end of the single row
Declare @intEndPos INT
-- @intSPos is used to mark the begining of the column value in a single row
Declare @intSPos INT
-- @intEPos is used to mark the end of the column value in a single row
Declare @intEPos INT
Select @intStartPos = 1
While(1 = 1)
Begin
--Find the occurence of ^ From the starting position to mark the
--end position of the single record
Select @intEndPos = CharIndex('^', @strUpdateList, @intStartPos)
--If there are no occurence of ^ means only one record is there to update
If @intEndPos = 0
Begin
-- Extracts a single record and stores in the @strRowItem eg: 1~Rojip~T~rojipt@yahoo.com
Select @strRowItem = Substring(@strUpdateList, @intStartPos, DataLength(@strUpdateList))
--Extracts the first item(Employee ID) stores it in @EmpID
Select @intSPos = CharIndex('~', @strRowItem, 0)
Select @EmpID = SubString(@strRowItem, 1, @intSPos - 1)
--Extracts the second item(First Name) stores it in @FirstName
Select @intSPos = @intSPos + 1
Select @intEPos = CharIndex('~', @strRowItem, @intSPos)
Select @FirstName = SubString(@strRowItem, @intSPos, (@intEPos - @intSPos))
--Extracts the third item(Last Name) stores it in @LastName
Select @intSPos = @intEPos + 1
Select @intEPos = CharIndex('~', @strRowItem, @intSPos)
Select @LastName = SubString(@strRowItem, @intSPos, (@intEPos - @intSPos))
--Extracts the last item(emailID) stores it in @emailID
Select @intSPos = @intEPos + 1
Select @emailID = (SubString(@strRowItem, @intSPos, (Len(@strRowItem) - @intSPos) + 1))
IF EXISTS(SELECT 'X' FROM Employee WHERE EmpID = @EmpID)
BEGIN
UPDATE Employee
SET FirstName = @FirstName,
LastName = @LastName,
emailID = @emailID
WHERE EmpID = @EmpID
END
Break
End
Else
Begin
-- Extracts a single record and stores in the @strRowItem eg: 1~Rojip~T~rojipt@yahoo.com
Select @strRowItem = Substring(@strUpdateList, @intStartPos, (@intEndPos - @intStartPos))
--Extracts the first item(Employee ID) stores it in @EmpID
Select @intSPos = CharIndex('~', @strRowItem, 0)
Select @EmpID = SubString(@strRowItem, 1, @intSPos - 1)
--Extracts the second item(First Name) stores it in @FirstName
Select @intSPos = @intSPos + 1
Select @intEPos = CharIndex('~', @strRowItem, @intSPos)
Select @FirstName = SubString(@strRowItem, @intSPos, (@intEPos - @intSPos))
--Extracts the third item(Last Name) stores it in @LastName
Select @intSPos = @intEPos + 1
Select @intEPos = CharIndex('~', @strRowItem, @intSPos)
Select @LastName = SubString(@strRowItem, @intSPos, (@intEPos - @intSPos))
--Extracts the last item(emailID) stores it in @emailID
Select @intSPos = @intEPos + 1
Select @emailID = SubString(@strRowItem, @intSPos, (Len(@strRowItem) - @intSPos) + 1)
IF EXISTS(SELECT 'X' FROM Employee WHERE EmpID = @EmpID)
BEGIN
UPDATE Employee
SET FirstName = @FirstName,
LastName = @LastName,
emailID = @emailID
WHERE EmpID = @EmpID
END
Select @intStartPos = @intEndPos + 1
End
End
--Executing the stored proc
EXEC spUpdateEmployee '1~Rojip~T~rojipt@yahoo.com^2~Subu~S~subodhs77@yahoo.com^3~Reji~S~rejin_s@hotmail.com'
