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'

Rate

Share

Share

Rate