SQL RAISERROR() 的语法含义是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16170073/
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
What is the syntax meaning of RAISERROR()
提问by user2289490
I just created a Instead After Trigger whose syntax is given below:
我刚刚创建了一个替代触发器，其语法如下：
Create trigger tgrInsteadTrigger on copytableto
Instead of Insert as
Declare @store_name varchar(30);
declare @sales int;
declare @date datetime;
select @store_name = i.store_name from inserted i
select @sales = i.sales from inserted i
select @date = i.Date from inserted i
begin
if (@sales > 1000)
begin
RAISERROR('Cannot Insert where salary > 1000',16,1); ROLLBACK;
end
else
begin
insert into copytablefrom(store_name, sales, date) values (@store_name, @sales, @date);
Print 'Instead After Trigger Executed';
end
End
In the above syntax I have used RAISERROR('Cannot Insert where salary > 1000',16,1)
在我使用的上述语法中 RAISERROR('Cannot Insert where salary > 1000',16,1)
But when I write RAISERROR('Cannot Insert where salary > 1000')it gives the error "Incorrect syntax near ')'" on the same line.
但是当我写RAISERROR('Cannot Insert where salary > 1000')它时，它会在同一行上给出错误“')'附近的语法不正确”。
Can anyone please explain the use of (16,1) here.
任何人都可以在这里解释 (16,1) 的使用。
回答by Darren
It is the severity level of the error. The levels are from 11 - 20 which throw an error in SQL. The higher the level, the more severe the level and the transactionshould be aborted.
它是error. 级别为 11 - 20，在SQL. 级别越高，级别越重，transaction应该中止。
You will get the syntax error when you do:
执行以下操作时会出现语法错误：
RAISERROR('Cannot Insert where salary > 1000').
Because you have not specified the correct parameters(severity levelor state).
因为您没有指定正确的parameters(severity level或state)。
If you wish to issue a warning and not an exception, use levels 0 - 10.
如果您希望发出警告而不是exception，请使用级别 0 - 10。
From MSDN:
来自 MSDN：
severity
Is the user-defined severity level associated with this message. When using msg_id to raise a user-defined message created using sp_addmessage, the severity specified on RAISERROR overrides the severity specified in sp_addmessage. Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.
state
Is an integer from 0 through 255. Negative values or values larger than 255 generate an error. If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors. For detailed description here
严重性
是与此消息关联的用户定义的严重性级别。使用 msg_id 引发使用 sp_addmessage 创建的用户定义消息时，RAISERROR 上指定的严重性将覆盖 sp_addmessage 中指定的严重性。任何用户都可以指定从 0 到 18 的严重级别。19 到 25 的严重级别只能由 sysadmin 固定服务器角色的成员或具有 ALTER TRACE 权限的用户指定。对于 19 到 25 的严重性级别，需要 WITH LOG 选项。
状态
是 0 到 255 之间的整数。负值或大于 255 的值会产生错误。如果在多个位置引发了相同的用户定义错误，则对每个位置使用唯一的状态编号可以帮助找出引发错误的代码部分。详细说明在这里
回答by harsh
16 is severity and 1 is state, more specifically following example might give you more detail on syntax and usage:
16 是严重性，1 是状态，更具体地说，以下示例可能会为您提供有关语法和用法的更多详细信息：
BEGIN TRY
-- RAISERROR with severity 11-19 will cause execution to
-- jump to the CATCH block.
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
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;
You can follow and try out more examples from http://msdn.microsoft.com/en-us/library/ms178592.aspx
您可以从http://msdn.microsoft.com/en-us/library/ms178592.aspx遵循并尝试更多示例
回答by Woot4Moo
according to MSDN
根据MSDN
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
16would be the severity.1would be the state.
16将是严重性。1将是国家。
The error you get is because you have not properly supplied the required parameters for the RAISEERRORfunction.
您得到的错误是因为您没有正确提供RAISEERROR函数所需的参数。
回答by hardmath
The severity level 16 in your example code is typically used for user-defined (user-detected) errors. The SQL Server DBMS itself emits severity levels(and error messages) for problems it detects, both more severe (higher numbers) and less so (lower numbers).
示例代码中的严重性级别 16 通常用于用户定义的（用户检测到的）错误。SQL Server DBMS 本身会针对它检测到的问题发出严重性级别（和错误消息），包括更严重（更高的数字）和更轻（更低的数字）。
The state should be an integer between 0 and 255 (negative values will give an error), but the choice is basically the programmer's. It is useful to put different state values if the same error message for user-defined error will be raised in different locations, e.g. if the debugging/troubleshooting of problems will be assisted by having an extra indication of where the error occurred.
状态应该是 0 到 255 之间的整数（负值会报错），但选择基本上是程序员的。如果用户定义错误的相同错误消息将在不同位置出现，则放置不同的状态值很有用，例如，如果问题的调试/故障排除将通过额外指示错误发生的位置来帮助。
