SQL 存储过程 NULL 参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5198657/
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
Stored Procedure NULL Parameter
提问by rpf3
I have a database table I am trying to do an UPDATE/INSERT to with a stored procedure. Let's define the table like so:
我有一个数据库表，我正在尝试使用存储过程对其进行更新/插入。让我们像这样定义表：
CREATE TABLE Foo
(
Id INT IDENTITY(1, 1),
Name VARCHAR(256) NOT NULL,
ShortName VARCHAR(32),
Sort INT
);
I have written a stored procedure similar to the following:
我编写了一个类似于以下内容的存储过程：
CREATE PROCEDURE Put_Foo
(
@Id INT = NULL OUTPUT,
@Name VARCHAR(256),
@ShortName VARCHAR(32) = NULL,
@Sort INT = NULL
)
AS
BEGIN
SET NOCOUNT ON;
SELECT
@Id = F.Id
FROM
Foo AS F
WHERE
F.Name = @Name;
IF (@Id IS NOT NULL)
BEGIN
UPDATE
Foo
SET
ShortName = @ShortName,
Sort = @Sort
WHERE
Id = @Id;
END
ELSE
BEGIN
INSERT
INTO Foo
(
Name,
ShortName,
Sort
)
VALUES
(
@Name,
@ShortName
@Sort
);
SET @Id = SCOPE_IDENTITY();
END
RETURN;
END;
I've greatly simplified the data structures I am dealing with but I hope this serves my point. My question is in regards to how the parameters are processed. Is there a way to determine within the procedure if @Sort was passed in as NULL or set NULL by the default declaration in the parameter list?
我已经大大简化了我正在处理的数据结构，但我希望这对我的观点有用。我的问题是关于如何处理参数。有没有办法在过程中确定@Sort 是作为 NULL 传入还是通过参数列表中的默认声明设置 NULL？
EDIT:
编辑：
The purpose of this is that I don't want NULL parameters to override any columns in the UPDATE statement unless they are explicitly passed in that way.
这样做的目的是我不希望 NULL 参数覆盖 UPDATE 语句中的任何列，除非它们以这种方式显式传递。
回答by Tony Casale
No, you can't detect how @Sort became NULL. If your goal is to capture when it is explicitly set versus it being set by the default, I would suggest using a different default value (maybe one that wouldn't normally be used, like -1). Then you can assume that if @Sort is NULL, it was explicitly passed in, but if it is -1, you know it was set by default.
不，您无法检测 @Sort 是如何变为 NULL 的。如果您的目标是在显式设置而不是默认设置时进行捕获，我建议使用不同的默认值（可能通常不会使用的默认值，例如 -1）。然后你可以假设如果@Sort 是NULL，它是显式传入的，但如果它是-1，你知道它是默认设置的。
回答by John
I think this is what your looking for. If one of the parameters is null, it will updatedit with the value in the database. The other option is update one column at a time.
我想这就是你要找的。如果其中一个参数为空，它将用数据库中的值更新它。另一种选择是一次更新一列。
UPDATE Foo
SET
ShortName = ISNULL(@ShortName, ShortName)
, Sort = ISNULL(@Sort, Sort)
WHERE Id = @Id;
回答by HLGEM
Take out the default value and and then the code calling the proc must provide a value (either a real value or NULL)
取出默认值，然后调用proc的代码必须提供一个值（真实值或NULL）
