我正在创建用于在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;
