處理儲存過程中的可選參數

我有一個儲存過程，比如帶有可選參數@Param1 int = NULL 的“Test”。在該過程中，@Param1 的值用於更新某個表中某個列的值，如果呼叫者提供了一個值。如果未提供參數，則不會更新該列。不幸的是，該列允許 NULL，因此呼叫者無法將列值設置為 NULL。所以，問題是：程序是否能夠區分以下兩個呼叫？

EXEC Test– 預期含義：不更新列

EXEC Test @Param1 = NULL– 預期含義：將列設置為NULL

當然，程序可以檢查是否@Param1 IS NULL. 但是它可以確定參數是否已經提供了嗎？

也許這樣的事情可能會起作用：-

CREATE PROC dbo.Test @Param1 INT = NULL
AS
BEGIN
   SET NOCOUNT ON;

   IF EXISTS
   (
       SELECT 1
       FROM sys.dm_exec_requests AS ER
           CROSS APPLY sys.dm_exec_input_buffer(ER.session_id, ER.request_id) AS IB
       WHERE ER.session_id = @@SPID
             AND IB.event_info LIKE '%@Param1%'
   )
   BEGIN
       IF @Param1 IS NULL
       BEGIN
           RAISERROR('@Param1 was supplied as NULL', 0, 1) WITH NOWAIT;
       END;
       ELSE
       BEGIN
           RAISERROR('@Param1 was supplied as a non-NULL value', 0, 1) WITH NOWAIT;
       END;
   END;
   ELSE
   BEGIN
       RAISERROR('@Param1 was not supplied and defaulted to NULL', 0, 1) WITH NOWAIT;
   END;
END;
GO

測試：-

EXEC dbo.Test @Param1 = NULL;
GO
EXEC dbo.Test @Param1 = 123;
GO
EXEC dbo.Test;
GO

結果（在 15.0.4102.2 上）：-

@Param1 was supplied as NULL
@Param1 was supplied as a non-NULL value
@Param1 was not supplied and defaulted to NULL

---

@@SPID並可CURRENT_REQUEST_ID用於向sys.dm_exec_input_buffer. 在目前會話中使用該 DMV 不需要特殊權限。需要更多權限才能查看其他會話的詳細資訊。

解答： 無法判斷被呼叫過程中的參數是否由使用者或系統設置​​為預設值。

如果該值是通過呼叫腳本傳遞的或由 SQL Server 分配的預設值，則過程中的值看起來完全相同，並且系統中沒有儲存任何資訊。

變通方法： 您可以使用第二個可選參數，如 @set_null bit = 0，並且僅當 @Param1 IS NOT NULL 或 @set_null = 1 時將 NULL 設置為列。

ALTER PROCEDURE Test
   @Param1   VARCHAR(10) = NULL,
   @set_null BIT         = 0
AS
BEGIN
   IF @Param1 IS NOT NULL OR @set_null = 1
       UPDATE my_table
       SET name = @Param1;
END;

引用自：https://dba.stackexchange.com/questions/295650