What is the syntax meaning of RAISERROR

Question

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  gt  1000          begin         RAISERROR  Cannot Insert where salary  gt  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  gt  1000  16 1   But when I write RAISERROR  Cannot Insert where salary  gt  1000   it gives the error  Incorrect syntax near      on the same line   Can anyone please explain the use of  16 1  here

User · Answer

according to MSDN RAISERROR     msg id   msg str    local variable          severity  state          argument       n             WITH option       n      16 would be the severity  1 would be the state  The error you get is because you have not properly supplied the required parameters for the RAISEERROR function

User · Answer

The answer posted to this question as an example taken from Microsoft s MSDN is nice however it doesn t directly demonstrate where the error comes from if it doesn t come from the TRY Block   I prefer this example with a very minor update to the RAISERROR Message within the CATCH Block stating that the error is from the CATCH Block   I demonstrate this in the gif as well   BEGIN TRY        RAISERROR with severity 11-19 will cause execution         to jump to the CATCH block             RAISERROR   Error raised in TRY block    -- Message text                 5  -- Severity     Severity Levels Less Than 11 do not jump to the CATCH block                   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   Caught Error in Catch   -- ErrorMessage     Message text                    ErrorSeverity                               Severity                        ErrorState                                  State                             END CATCH

User · Answer

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    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

User · Answer

16 is severity and 1 is state  more specifically following example might give you more detail on syntax and usage   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

User · Answer

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 transaction should be aborted  You will get the syntax error when you do  RAISERROR  Cannot Insert where salary  gt  1000     Because you have not specified the correct parameters  severity level or state   If you wish to issue a warning and not an exception  use levels 0 - 10  From 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

[sql] What is the syntax meaning of RAISERROR()

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to sql-server-2008

Examples related to sql-server-2008-r2