sql-server – 用于INSERT和UPDATE的SQL Server存储过程,更好地

发布时间：2020-12-12 08:48:50 所属栏目：MsSql教程来源：网络整理

导读：我正在创建用于在SQL Server数据库中插入和更新数据的存储过程.起初我正在为Add / Set创建一个单独的过程,但后来我偶然发现了一个允许我将它们压缩成一个过程的查询.我想与SO社区核实任何可能的未来问题,这样做. 单独的程序 --INSERT ProcedureCREATE PROCEDU

我正在创建用于在SQL Server数据库中插入和更新数据的存储过程.起初我正在为Add / Set创建一个单独的过程,但后来我偶然发现了一个允许我将它们压缩成一个过程的查询.我想与SO社区核实任何可能的未来问题,这样做.

单独的程序

--INSERT Procedure
CREATE PROCEDURE [dbo].[AddDataType]
    @TypeName [nvarchar](255),@TypeProperty [nvarchar](255)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO DataType(TypeName,TypeProperty)
    VALUES(@TypeName,@TypeProperty)

    SELECT SCOPE_IDENTITY()
END

--UPDATE Procedure
CREATE PROCEDURE [dbo].[SetDataType]
    @ID [int],@TypeName [nvarchar](255),@TypeProperty [nvarchar](255)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE DataType SET TypeName = @TypeName,TypeProperty = @TypeProperty
    WHERE ID = @ID
 END

EXEC AddDataType @TypeName = 'Test Name',@TypeProperty = 'Test Property' --INSERT
EXEC SetDataType @ID = 42,@TypeName = 'Test Name',@TestProperty = 'Test Property' --UPDATE

综合

CREATE PROCEDURE [dbo].[SetDataType]
    @ID [int] = NULL,TypeProperty = @TypeProperty
    WHERE ID = @ID

    IF @@ROWCOUNT = 0
        INSERT INTO DataType(TypeName,TypeProperty)
        VALUES(@TypeName,@TypeProperty)

    IF @ID IS NULL
        SELECT SCOPE_IDENTITY()
END

EXEC SetDataType @TypeName = 'New Type Name',@TypeProperty = 'New Type Property' --INSERT
EXEC SetDataType @ID = 42,@TypeName = 'Updated Type Name',@TypeProperty = 'Updated Type Property' --UPDATE

到目前为止,我有15个类型表,我创建了程序,并试图减少创建的程序数,但我不想牺牲性能.我知道第二种方法是更多的处理,但它是否足以导致问题？我没有看到类型表保存大量数据,不超过100条记录,平均值大约为10-20.

任何想法或建议表示赞赏.

解决方法

什么版本的SQL Server？此信息始终有用,因此请养成使用特定版本标记问题的习惯.

如果是sql-server-2008或更高版本,您可能会考虑MERGE而不是单独的INSERT / UPDATE操作,但是自从写完这个答案后我肯定改变了我的观点并且更喜欢你提出的UPDATE / IF @@ ROWCOUNT = 0 / INSERT方法.有关更多信息,请参阅我写的这篇文章：

> Use Caution with SQL Server’s MERGE Statement

这是一个MERGE示例(在tempdb中运行它),但我再次建议不要使用它.

CREATE TABLE dbo.DataType
(
    ID             int IDENTITY(1,1),TypeName       nvarchar(255),[TypeProperty] nvarchar(255),CONSTRAINT PK_DataType PRIMARY KEY (ID)
);

INSERT dbo.DataType(TypeName,[TypeProperty]) VALUES (N'name 1',N'property 1');
GO

然后一个程序：

CREATE PROCEDURE dbo.MergeDataType
    @ID           int = NULL,@TypeName     nvarchar(255),@TypeProperty nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;

    WITH [source](ID,TypeName,[TypeProperty]) AS 
    (
        SELECT @ID,@TypeName,@TypeProperty
    )
    MERGE dbo.DataType WITH (HOLDLOCK) AS [target] 
      USING [source] ON [target].ID = [source].ID
    WHEN MATCHED THEN
        UPDATE SET [target].TypeName       = @TypeName,[target].[TypeProperty] = @TypeProperty
    WHEN NOT MATCHED THEN
        INSERT (TypeName,[TypeProperty]) 
        VALUES (@TypeName,@TypeProperty);
END
GO

现在让我们运行它并检查结果：

EXEC dbo.MergeDataType 
    @TypeName     = N'foo',@TypeProperty = N'bar';

EXEC dbo.MergeDataType 
    @ID           = 1,@TypeName     = N'name 1',@TypeProperty = N'new property';
GO

SELECT ID,[TypeProperty] FROM dbo.DataType;
GO

清理：

DROP TABLE dbo.DataType;
DROP PROCEDURE dbo.MergeDataType;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!