vba T-SQL 开发人员插入和合并存储过程能够修改为 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13370810/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL developer insert & merge stored procedure able to modify to NULL
提问by Bertie
Is there a better SQL Server 2008 R2 technique to code the following combined INSERT& UPDATEprocedure that allows NULL INSERT?
是否有更好的 SQL Server 2008 R2 技术来编写以下允许 NULL 的组合INSERT&UPDATE过程INSERT？
I am very interested in seeing how other developers write INSERT& UPDATEprocedures that can handle NULL inserts (imagine a user wanting to undo an entry). I appreciate there will be more sophisticated and elegant solutions using MERGEor some transaction rollback technique which I am interested in seeing, however, I do ask that you build up your example from first principles as this may result in the post having a wider appeal no matter what the readers T-SQL level.
我在看到其他开发人员如何编写非常有兴趣INSERT与UPDATE能够处理NULL插入程序（想象一个用户想要取消的条目）。我很感激会有更复杂和优雅的解决方案使用MERGE或一些我有兴趣看到的事务回滚技术，但是，我确实要求您从第一原则构建您的示例，因为这可能会导致帖子具有更广泛的吸引力什么读者T-SQL水平。
The basis of this simplistic example is an Orderstable tracking stock purchases. The procedure should only allow UPDATESwhen the OrderStatusis the same or increasing..
这个简单示例的基础是一个Orders跟踪股票购买的表格。该过程应该只允许UPDATES在OrderStatus相同或增加时..
OrderStatus Explanation
-------------------------
0 Creation
1 Checking
2 Placement
3 Execution
...
8 Settlement
Table structure:
表结构：
CREATE TABLE Orders(
OrderID INT IDENTITY,
Ticker VARCHAR(20) NOT NULL,
Size DECIMAL(31,15) NULL,
Price DECIMAL(31,15) NULL,
OrderStatus TINYINT NOT NULL)
Further, let's imagine we have the following data so that we can test modifying the data:
此外，让我们假设我们有以下数据，以便我们可以测试修改数据：
SET IDENTITY_INSERT [dbo].[Orders] ON
INSERT INTO [dbo].[Orders] ([OrderID], [Ticker], [Size], [Price], [OrderStatus])
VALUES (1, N'MSFT', CAST(1 AS Decimal(31, 15)), NULL, 0)
,(2, N'GOOG', CAST(2 AS Decimal(31, 15)), CAST(523 AS Decimal(31, 15)), 5)
,(3, N'AAPL', CAST(1 AS Decimal(31, 15)), NULL, 0)
SET IDENTITY_INSERT [dbo].[Orders] OFF
You should have the following data:
您应该有以下数据：
OrderID Ticker Size Price OrderStatus
-----------------------------------------------
1 MSFT 1.000 NULL 0
2 GOOG 2.000 523.000 5
3 AAPL 1.000 NULL 0
Now for the interesting part. This is my best effort to design a combined INSERT& UPDATEprocedure that can deal with NULL inserts (i.e. allow the user to undo an entry). Notice that I need an input parameter to differentiate whether the input value of NULL is intentional and needs to be written into the table vs. the NULL that appears as a missing input parameter. Hopefully it's very clear why I am asking this question as I find my technique very verbose.
现在是有趣的部分。这是我设计一个可以处理 NULL 插入（即允许用户撤消条目）的组合INSERT&UPDATE过程的最大努力。请注意，我需要一个输入参数来区分 NULL 的输入值是否是有意的，是否需要写入表中，而 NULL 则作为缺失的输入参数出现。希望很清楚为什么我会问这个问题，因为我发现我的技术非常冗长。
CREATE PROCEDURE [dbo].[Upsert_Orders] @isNullInsert BIT = 0
,@OrderID INT = NULL
,@Ticker VARCHAR(20) = NULL
,@Size VARCHAR(100) = NULL
,@Price VARCHAR(100) = NULL
,@OrderStatus TINYINT = NULL
AS
BEGIN
IF (@OrderID IS NOT NULL)
-- First check if @OrderID exists
IF (
SELECT OrderID
FROM dbo.Orders
WHERE OrderID = @OrderID
) IS NULL
BEGIN
-- @OrderID does not exist therefore replace with NULL
SET @OrderID = NULL
PRINT 'spUO. Replaced OrderID ' + CAST(@OrderID AS VARCHAR) + ' input parameter with NULL.'
END
IF @OrderID IS NULL
BEGIN
-- @OrderID IS NULL so INSERT a new record
PRINT 'spUO Inserting a new record into the Orders'
INSERT INTO Orders (
-- OrderID not needed as IDENTITY new record.
Ticker
,Size
,Price
,OrderStatus
)
VALUES (
-- @OrderID not needed as IDENTITY new record
@Ticker
,@Size
,@Price
,@OrderStatus
)
END
ELSE
BEGIN
-- @OrderID IS NOT NULL therefore UPDATE the record @OrderID
PRINT 'spUO Modifying existing record with OrderID ' + CAST(@OrderID AS VARCHAR)
-- Declare CurrentVariables for @OrderID
DECLARE -- @CurrentOrderID INT not needed as @OrderID Found
@CurrentTicker VARCHAR(20)
,@CurrentSize DECIMAL(31, 15)
,@CurrentPrice DECIMAL(31, 15)
,@CurrentOrderStatus TINYINT
-- Populate Current Variables from Table Orders
SELECT -- @CurrentOrderID = OrderID not needed as @OrderID Found
@CurrentTicker = Ticker
,@CurrentSize = Size
,@CurrentPrice = Price
,@CurrentOrderStatus = OrderStatus
FROM Orders
WHERE OrderID = @OrderID
IF ISNULL(@OrderStatus, @CurrentOrderStatus) >= @CurrentOrderStatus
BEGIN
-- Update @OrderID if not moving backwards
IF @isNullInsert = 0
BEGIN
-- We are not updating the record with NULL
PRINT 'spUO NULL Parameter Input Values get replaced with the existing entries'
UPDATE Orders
SET -- OrderID = ISNULL(@OrderID, @CurrentOrderID) not needed as @OrderID Found
Ticker = ISNULL(@Ticker, @CurrentTicker)
,Size = ISNULL(@Size, @CurrentSize)
,Price = ISNULL(@Price, @CurrentPrice)
,OrderStatus = ISNULL(@OrderStatus, @CurrentOrderStatus)
WHERE OrderID = @OrderID
END
ELSE
BEGIN
-- We are potentially overwritting the record with NULL
PRINT 'spUO Old entries may be overwritten with NULL'
UPDATE Orders
SET -- OrderID = ISNULL(@OrderID, @CurrentOrderID) not needed as @OrderID Found
Ticker = @Ticker
,Size = @Size
,Price = @Price
,OrderStatus = @OrderStatus
WHERE OrderID = @OrderID
END
END
ELSE
-- User is trying to re-write hostory. Do Nothing
PRINT 'spUO You do not have permissions to roll back the OrderStatus.'
END
END
Now that we have an UPSERT procedure, let me illustrate its usage:
现在我们有了一个 UPSERT 过程，让我来说明它的用法：
Step 1: insert a new row to show the intention to buy some Ford shares:
步骤 1：插入一个新行以显示购买一些福特股票的意图：
EXEC dbo.Upsert_Orders @Ticker = 'F',
@Size = 1,
@Price = 10,
@OrderStatus = 2
Step 2: let's show that the OrderStatuscan't be wound back
第2步：让我们证明OrderStatus不能回卷
EXEC dbo.Upsert_Orders @OrderID = 4,
@Ticker = 'F',
@Size = 1,
@Price = 10,
@OrderStatus = 1
This produces the desired output:
这会产生所需的输出：
spUO Modifying existing record with OrderID 4
spUO You do not have permissions to roll back the OrderStatus.
The data now looks like:
数据现在看起来像：
OrderID Ticker Size Price OrderStatus
-----------------------------------------------
1 MSFT 1.000 NULL 0
2 GOOG 2.000 523.000 5
3 AAPL 1.000 NULL 0
4 F 1.000 10.000 2
Step 3: finally, let's assume the user wants to delete the shares of the first order, then the unfortunate method under my procedure requires the other default parameters be passed and the @isNULLInsert BITneeds to be set to 1.
第三步：最后，假设用户要删除第一个订单的份额，那么不幸的是我的程序下的方法需要传递其他默认参数，@isNULLInsert BIT需要设置为1。
EXEC dbo.Upsert_Orders @isNullInsert = 1,
@OrderID = 1,
@Ticker = 'MSFT',
@Size = NULL,
@Price = NULL,
@OrderStatus = 0
Hopefully this complete example illustrates the concept in adding a new record, updating an existing record and deleting a field of a record. Apologies for the length of this post, but this is the most concise code I was able to produce!
希望这个完整的示例说明了添加新记录、更新现有记录和删除记录字段的概念。为这篇文章的长度道歉，但这是我能够生成的最简洁的代码！
Final data:
最终数据：
OrderID Ticker Size Price OrderStatus
------------------------------------------------
1 MSFT NULL NULL 0
2 GOOG 2.000 523.000 5
3 AAPL 1.000 NULL 0
4 F 1.000 10.000 2
Thanks All,
谢谢大家，
Bertie.
伯蒂。
p.s. This will be called from Excel VBA.
ps 这将从 Excel VBA 调用。
回答by Laurence
Here's an answer using merge.
这是使用合并的答案。
Create Procedure [dbo].[Upsert_Orders2]
@IsNullInsert Bit = 0,
@OrderID Int = Null,
@Ticker Varchar(20) = Null,
@Size Decimal(31,15) = Null,
@Price Decimal(31,15) = Null,
@OrderStatus Tinyint = Null
As
Declare @OrderStatusChange Table(Oldstatus int, NewStatus int)
Begin Transaction
Merge
dbo.Orders As target
Using
(Select @OrderID As OrderID) As source
On
(target.OrderID = source.OrderID)
When Matched Then
Update Set
Ticker = Case When @IsNullInsert = 0 Then IsNull(@Ticker, target.Ticker) Else @Ticker End,
Size = Case When @IsNullInsert = 0 Then IsNull(@Size, target.Size) Else @Size End,
Price = Case When @IsNullInsert = 0 Then IsNull(@Price, target.Price) Else @Price End,
OrderStatus = Case When @IsNullInsert = 0 Then IsNull(@OrderStatus, target.OrderStatus) Else @OrderStatus End
When Not Matched Then
Insert
(Ticker, Size, Price, OrderStatus)
Values
(@Ticker, @Size, @Price, @OrderStatus)
Output
deleted.OrderStatus, inserted.OrderStatus into @OrderStatusChange;
If Exists (Select 'x' From @OrderStatusChange Where NewStatus < OldStatus)
-- Evil History Changer!
Rollback Transaction
Else
Commit Transaction
