我有一个存储过程,负责一次插入或更新多个记录.为了提高性能,我想在我的存储过程中执行此操作.
此存储过程采用逗号分隔的许可ID列表和状态.许可证ID存储在名为@PermitIDs的变量中.状态存储在名为@Status的变量中.我有一个用户定义的函数,将这个以逗号分隔的许可ID列表转换为表.我需要遍历每个ID,并在名为PermitStatus的表中插入或更新.
如果不存在具有许可ID的记录,我想添加记录.如果确实存在,我想用给定的@Status值更新记录.我知道如何为单个ID执行此操作,但我不知道如何为多个ID执行此操作.对于单个ID,我执行以下操作：
-- Determine whether to add or edit the PermitStatus
DECLARE @count int
SET @count = (SELECT Count(ID) FROM PermitStatus WHERE [PermitID]=@PermitID)
-- If no records were found,insert the record,otherwise add
IF @count = 0
BEGIN
INSERT INTO
PermitStatus
(
[PermitID],[UpdatedOn],[Status]
)
VALUES
(
@PermitID,GETUTCDATE(),1
)
END
ELSE
UPDATE
PermitStatus
SET
[UpdatedOn]=GETUTCDATE(),[Status]=@Status
WHERE
[PermitID]=@PermitID
解决方法
创建一个split函数,并使用它：
SELECT
*
FROM YourTable y
INNER JOIN dbo.splitFunction(@Parameter) s ON y.ID=s.Value
I prefer the number table approach
要使此方法起作用,您需要执行以下一次性表设置：
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
设置Numbers表后,创建此功能：
CREATE FUNCTION [dbo].[FN_ListToTableAll]
(
@SplitOn char(1) --required,the character to split the @List string on,@List varchar(8000)--required,the list to split apart
)
RETURNS TABLE
AS
RETURN
(
----------------
--SINGLE QUERY-- --this WILL return empty rows
----------------
SELECT
ROW_NUMBER() OVER(ORDER BY number) AS RowNumber,LTRIM(RTRIM(SUBSTRING(ListValue,number+1,CHARINDEX(@SplitOn,ListValue,number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS ListValue
) AS InnerQuery
INNER JOIN Numbers n ON n.Number < LEN(InnerQuery.ListValue)
WHERE SUBSTRING(ListValue,number,1) = @SplitOn
);
GO
您现在可以轻松地将CSV字符串拆分为表格并加入其中：
select * from dbo.FN_ListToTableAll(',','1,2,3,4,5,6777,')
OUTPUT：
RowNumber ListValue ----------- ---------- 1 1 2 2 3 3 4 5 6 4 7 5 8 6777 9 10 11 (11 row(s) affected)
要使您需要的工作,请执行以下操作：
--this would be the existing table
DECLARE @OldData table (RowID int,RowStatus char(1))
INSERT INTO @OldData VALUES (10,'z')
INSERT INTO @OldData VALUES (20,'z')
INSERT INTO @OldData VALUES (30,'z')
INSERT INTO @OldData VALUES (70,'z')
INSERT INTO @OldData VALUES (80,'z')
INSERT INTO @OldData VALUES (90,'z')
--these would be the stored procedure input parameters
DECLARE @IDList varchar(500),@StatusList varchar(500)
SELECT @IDList='10,20,30,40,50,60',@StatusList='A,B,C,D,E,F'
--stored procedure local variable
DECLARE @InputList table (RowID int,RowStatus char(1))
--convert input prameters into a table
INSERT INTO @InputList
(RowID,RowStatus)
SELECT
i.ListValue,s.ListValue
FROM dbo.FN_ListToTableAll(',@IDList) i
INNER JOIN dbo.FN_ListToTableAll(',@StatusList) s ON i.RowNumber=s.RowNumber
--update all old existing rows
UPDATE o
SET RowStatus=i.RowStatus
FROM @OldData o WITH (UPDLOCK,HOLDLOCK) --to avoid race condition when there is high concurrency as per @emtucifor
INNER JOIN @InputList i ON o.RowID=i.RowID
--insert only the new rows
INSERT INTO @OldData
(RowID,RowStatus)
SELECT
i.RowID,i.RowStatus
FROM @InputList i
LEFT OUTER JOIN @OldData o ON i.RowID=o.RowID
WHERE o.RowID IS NULL
--display the old table
SELECT * FROM @OldData order BY RowID
OUTPUT：
RowID RowStatus ----------- --------- 10 A 20 B 30 C 40 D 50 E 60 F 70 z 80 z 90 z (9 row(s) affected)
编辑感谢@Emtucifor click here关于竞争条件的提示,我在我的答案中包含了锁定提示,以防止在高并发性时出现竞争条件问题.
