Update multiple rows (in a set) using a single SP
Here is another variation of processing multiple records with a single procedure call but allowing for set processing.
The helper functions make use of the sequencetable pattern. These helper functions can be used to parse strings into records, so one gets a list, or simply return the nth field.
There are two functions that will parse into records, delimitedtolist and delimitedtolist2. One parses strings up to varchar(8000) (delimitedtolist) and the other parses TEXT (delimitedtolist2). Delimitedtolist is faster.
I have used Sql4Subu's example as a starting point and expanded it to include set processing.
N.B. It is critical that sequcentable contains data (see script). If it contains less than 8000 rows then execute the T-SQL that is currently commented out.
CREATE TABLE [sequencetable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[id2] [int] NOT NULL ,
[amenddate] [datetime] NULL ,
[deletedate] [datetime] NULL ,
CONSTRAINT [pk_seqtable1] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
;
/*
If your syscolumns table does not contain many rows then you may wish to uncomment
and run the following
set nocount on
declare @i int
set @i = 1
while (@i <= 8000)
begin
insert into sequencetable (id2) values (1)
set @i = @i + 1
end
set nocount off
*/insert into sequencetable (id2)
select top 20000 1 from syscolumns
;
/*
** @(#) $Id: delimitedtolist.sql,v 300.1 2003/09/30 22:00:44 jinglis Exp $
**
** Version: $Revision: 300.1 $
** Date: $Date: 2003/09/30 22:00:44 $
** File: delimitedtolist
**
** Amendment History:
**
**1. 30/09/2003JNIRelease 300.1
**Script created.
**
*/
CREATE FUNCTION delimitedtolist (@delimstring VARCHAR(8000), @delim CHAR(1) = ' ')
RETURNS @delimitedlist TABLE (keyword VARCHAR(100), position int identity(1, 1))
AS
BEGIN
INSERT INTO @delimitedlist
SELECT NULLIF(SUBSTRING(@delimstring, id , CASE WHEN CHARINDEX(@delim , @delimstring, id) = 0 then LEN(@delimstring) ELSE CHARINDEX(@delim , @delimstring, id) - id END) , '') AS keyword
FROM sequencetable
WHERE id = 1 or 1 = CHARINDEX(@delim, SUBSTRING(@delimstring, id - 1, 1))
RETURN
END
;
/*
** @(#) $Id: delimitedtolist2.sql,v 300.1 2003/09/30 22:00:44 jinglis Exp $
**
** Version: $Revision: 300.1 $
** Date: $Date: 2003/09/30 22:00:44 $
** File: delimitedtolist2
**
** Amendment History:
**
**1. 30/09/2003JNIRelease 300.1
**Script created.
**
*/
CREATE FUNCTION delimitedtolist2 (@delimstring TEXT, @delim CHAR(1) = ' ')
RETURNS @delimitedlist TABLE (keyword TEXT, position int identity(1, 1))
AS
BEGIN
INSERT INTO @delimitedlist
SELECT NULLIF(SUBSTRING(@delimstring, id , CASE WHEN CHARINDEX(@delim , @delimstring, id) = 0 then DATALENGTH(@delimstring) ELSE CHARINDEX(@delim , @delimstring, id) - id END) , '') AS keyword
FROM sequencetable
WHERE id = 1 or 1 = charindex(@delim, SUBSTRING(@delimstring, id - 1, 1))
RETURN
END
;
/*
** @(#) $Id: delimitedtofield.sql,v 300.1 2003/09/30 22:00:44 jinglis Exp $
**
** Version: $Revision: 300.1 $
** Date: $Date: 2003/09/30 22:00:44 $
** File: csvtolist
**
** Amendment History:
**
**1. 30/09/2003JNIRelease 300.1
**Script created.
**
*/
CREATE FUNCTION delimitedtofield(@delimitedstring VARCHAR(8000), @delim CHAR(1), @position int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE@keywordVARCHAR(8000)
SELECT @keyword = keyword
FROM delimitedtolist(@delimitedstring, @delim)
WHERE position = @position
RETURN(@keyword)
END
;
--Creating a table Employee for updating multiple rows
CREATE TABLE Employee
(
EmpID INT PRIMARY KEY,
FirstNameVARCHAR(30),
LastNameVARCHAR(30),
emailIDVARCHAR(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
DECLARE @employee TABLE (
empidINT,
lastnameVARCHAR(30),
firstnameVARCHAR(30),
emailidVARCHAR(30)
PRIMARY KEY (empid)
)
INSERT INTO @employee
SELECTdbo.delimitedtofield(keyword, '~', 1),
dbo.delimitedtofield(keyword, '~', 2),
dbo.delimitedtofield(keyword, '~', 3),
dbo.delimitedtofield(keyword, '~', 4)
FROMdelimitedtolist2(@strUpdateList, '^')
UPDATEEmployee
SETFirstName= t.FirstName,
LastName= t.LastName,
emailID= t.emailID
FROMemployee e INNER JOIN @employee t ON
e.EmpID = t.EmpID
;
--Executing the stored proc
select * from employee
EXEC spUpdateEmployee '1~Rojip~T~rojipt@yahoo.com^2~Subu~S~subodhs77@yahoo.com^3~Reji~S~rejin_s@hotmail.com'
select * from employee
