瓦尔迪米尔的方法非常好,可以传递一个标志变量来识别是否传递了值,他关于任意选择值的注释也是正确的,但我想你可能永远不必担心一些任意的值。例如,当您不允许负数时,整数的-999或空字符串的“|||||||”。当然,当您确实想使用负数时,这种方法有些不可行,但是您可以尝试使用超出数据类型范围的数字作为参数默认值,例如BIGINT中的-9223372036854775808。
然而,如果您采用这种方法,我建议做两件事。1)不要从PHP传递值到SQL,而是将其设置为SQL中的默认值,并测试参数是否为默认值。2)在表中添加CHECK约束以确保不使用这些值并且不能在表中表示。
因此,代码示例:
ALTER TABLE dbo.UpdateTest
CHECK CONSTRAINT chk_IsNotNullStandInValue (Field1 <> '|||||||||||||||||||' AND Field2 <> -999)
CREATE PROCEDURE dbo.UpdateTest
@ParamId numeric(10,0)
,@ParamField1 NVARCHAR(250) = '|||||||||||||||||||'
,@ParamField2 INT = -99999
,@ParamField3 BIGINT = -9223372036854775808
AS
BEGIN
DECLARE @ParamField3Value INT
BEGIN TRY
IF ISNULL(@ParamField3,0) <> -9223372036854775808
BEGIN
SET @ParamField3Value = CAST(@ParamField3 AS INT)
END
END TRY
BEGIN CATCH
;THROW 51000, '@ParamField3 is not in range', 1
END CATCH
UPDATE dbo.Test
SET Field1 = IIF(@ParamField1 = '|||||||||||||||||||',Field1,@ParamField1)
,Field2 = IIF(@ParamField2 = -99999,Field2,@ParamField2)
,Field3 = IIF(@ParamField3 = -9223372036854775808, Field3, @ParamField3Value)
WHERE
ID = @ParamId
END
这种方法的真正问题在于数值数据字段允许负数,因为您没有适当的方法确定何时该值应为空,除非您选择一个始终超出范围的数字。
我确实意识到 BIGINT 用于 INT 的示例是多么糟糕,因为现在您的过程将接受不应该有的数字范围!
另一种方法/弱化 Vladimir 建议的变体是标记何时使字段为空,而不是何时更新。这需要您的 PHP 团队花费一些时间来记住使用,但因为这些标志也可以是可选的,所以它们不必总是包括类似以下内容:
CREATE PROCEDURE dbo.UpdateTest
@ParamId numeric(10,0)
,@ParamField1 NVARCHAR(250) = NULL
,@MakeField1Null BIT = 0
,@ParamField2 INT = NULL
,@MakeField2Null BIT = 0
,@ParamField3 INT = NULL
,@MakeField3Null BIT = 0
AS
BEGIN
UPDATE dbo.Test
SET Field1 = IIF(ISNULL(@MakeField1Null,0) = 1,NULL,ISNULL(@ParamField1,Field1))
,Field2 = IIF(ISNULL(@MakeField2Null,0) = 1,NULL,ISNULL(@ParamField2,Field2))
,Field3 = IIF(ISNULL(@MakeField3Null,0) = 1,NULL,ISNULL(@ParamField3,Field3))
WHERE
ID = @ParamId
END
基本上,如果您正在使用存储过程更新表格并且它具有可空字段,则我不建议将参数设置为可选,因为这会导致未来业务情况/情境变得混乱,尤其是涉及数值数据类型!
XML
进行包装并传递给程序?SQL Server可以解析XML,因此它允许传递和解析任何组合的参数。或许这很有趣?请参考:https://dev59.com/qW_Xa4cB1Zd3GeqPwQbP , http://www.itworld.com/article/2960645/development/tsql-how-to-use-xml-parameters-in-stored-procedures.html 和 https://www.simple-talk.com/blogs/using-xml-to-pass-lists-as-parameters-in-sql-server/。 - Ryan Vincent