在以后引发错误时看不到预期的打印或 RAISERROR 输出

Question

我在存储过程中收到一条错误消息，说我无法将 NULL 值插入表中，而如果值为 null，我应该在代码的前面收到错误。

下面是存储过程的相关部分：

CREATE PROCEDURE [dbo].[udp_AddUpdateStaffVariable] 
-- Add the parameters for the stored procedure here
@StaffID int=null,
@VariableTypeID int,
@VariableIntValue int=null,
@VariableVarcharValue varchar(max)=null,
@VariableDatetimeValue datetime=null,
@VariableDecimalValue decimal=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRY  
DECLARE @PrintOutput varchar(150)
SET @PrintOutput = '@StaffID = ' + CASE WHEN @StaffID = NULL THEN 'Null' ELSE CONVERT(varchar(20), @StaffID) END
RAISERROR (@PrintOutput, 10, 1) WITH NOWAIT
IF (@StaffID = NULL)    -- If the staffid of the current user was not supplied, find it in the Staff table
BEGIN
DECLARE @CurrentUser nvarchar(255) = SUSER_SNAME();
SELECT @StaffID = [StaffID] FROM [dbo].[Staff] WHERE [UserName] = @CurrentUser;
SET @PrintOutput = '@StaffID = ' + CASE WHEN @StaffID = NULL THEN 'Null' ELSE CONVERT(varchar(20), @StaffID) END
RAISERROR (@PrintOutput, 10, 1) WITH NOWAIT
IF @StaffID = NULL -- raise error if staffid wasn't found
BEGIN
RAISERROR (50001 --error number
, 16 -- severity
, 1 --state
, @CurrentUser -- parameter
)
END
END
-- Get the variable data type (used to determine where the variable is stored)
DECLARE @VarDataTypeDesc varchar(20)
DECLARE @StaffVarID int
SELECT @VarDataTypeDesc = dt.[StaffVariableDataType] 
FROM [list].[DataTypes] dt INNER JOIN [list].[StaffVariableTypes] svt ON dt.DataTypeID = svt.DataTypeID 
WHERE svt.VariableTypeID = @VariableTypeID
-- update or add the staff variable
IF EXISTS (SELECT 1 FROM [dbo].[StaffVariables] WHERE StaffID = @StaffID AND [VariableTypeID] = @VariableTypeID) -- update
BEGIN
IF @VarDataTypeDesc = 'int'
BEGIN -- only update here - other data types are updated further down
UPDATE [dbo].[StaffVariables] SET VariableIntValue = @VariableIntValue WHERE StaffID = @StaffID AND VariableTypeID = @VariableTypeID
END
ELSE -- StaffVariableID is only needed if the variable type is not int
BEGIN
SELECT @StaffVarID = StaffVariableID FROM [dbo].[StaffVariables] WHERE StaffID = @StaffID AND [VariableTypeID] = @VariableTypeID
END
END
ELSE -- insert
BEGIN
IF @VarDataTypeDesc = 'int'
BEGIN
INSERT INTO [dbo].[StaffVariables] (StaffID, VariableTypeID, VariableIntValue) 
VALUES (@StaffID, @VariableTypeID, @VariableIntValue)
END
ELSE -- StaffVariableID is only needed if the variable type is not int
BEGIN
DECLARE @StaffVarIDTbl table(ID int)
INSERT INTO [dbo].[StaffVariables] (StaffID, VariableTypeID, VariableIntValue) 
OUTPUT INSERTED.[StaffVariableID] INTO @StaffVarIDTbl 
VALUES (@StaffID, @VariableTypeID, @VariableIntValue)
SELECT @StaffVarID = ID FROM @StaffVarIDTbl
END
END
-- Cutting out the section where I deal with other variable types besides int here - not relevant to this problem
END TRY  
BEGIN CATCH  
DECLARE @ErrorMessage NVARCHAR(4000);  
DECLARE @ErrorSeverity INT;  
DECLARE @ErrorState INT;  
SELECT   
@ErrorMessage = ERROR_MESSAGE(),  
@ErrorSeverity = ERROR_SEVERITY(),  
@ErrorState = ERROR_STATE();  
-- Use RAISERROR inside the CATCH block to return error  
-- information about the original error that caused  
-- execution to jump to the CATCH block.  
RAISERROR (@ErrorMessage, -- Message text.  
@ErrorSeverity, -- Severity.  
@ErrorState -- State.  
);  
END CATCH;  
END

下面是测试过程运行代码：

DECLARE @return_value int
EXEC    @return_value = [dbo].[udp_AddUpdateStaffVariable]
@VariableTypeID = 1,
@VariableIntValue = 10
SELECT  'Return Value' = @return_value
GO

。以下是回应：

Msg 50000, Level 16, State 2, Procedure dbo.udp_AddUpdateStaffVariable, Line 130 [Batch Start Line 2]
Cannot insert the value NULL into column 'StaffID', table 'SnippingDbName.dbo.StaffVariables'; column does not allow nulls. INSERT fails.
(1 row affected)
Completion time: 2020-06-01T21:17:08.2049072-05:00

所以......这里有一个问题。该错误似乎表明它从未运行过整个代码if @StaffID = NULL部分代码，或者它确实运行了代码，并且没有找到StaffID并设置@StaffID变量。但如果是这样的话，为什么我看不到我之前RAISERROR陈述的结果呢？

我最初尝试了 PRINT，并在PRINT不起作用时切换到RAISERROR。

SQL Server 2017 开发人员版，SSMS 15.0.18183.0

Answer 1

这是一个语法错误，评论这个问题的人发现了。IF (@StaffID = NULL)应该是，IF (@StaffID IS NULL)在过程中的所有位置修复它解决了问题，并更改了我的测试人员记录，使用户名不匹配SUSER_SNAME()导致预期的错误。