25

SQL Server 2008 R2 Management Studio does not recognized my throw in the below example, it says

incorrect syntax near Throw

I am trying to throw an error here, so I can handled it in my website when someone insert the same value twice.

Begin Try
 insert into BusinessID (BusinessID) values (@ID)
 insert into BusinessID (BusinessID) values (@ID)

End Try

Begin Catch

Print 'PK already exist'
THROW
End Catch
Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
user2405574
  • 253
  • 1
  • 3
  • 4
  • 1
    `THROW` is a **new feature** in SQL Server **2012** and thus it's not (yet) available in 2008 R2 ..... – marc_s May 21 '13 at 13:35
  • Furthemore, it appears you need to terminate the statement preceeding THROW with a semicolon; else you get a syntax error ! – joedotnot Mar 11 '16 at 06:19

2 Answers2

47

THROW Statement is introduced in SQL Server 2012

http://msdn.microsoft.com/en-us/library/ee677615.aspx

You can use RAISERROR instead.

http://msdn.microsoft.com/en-us/library/483588bd-021b-4eae-b4ee-216268003e79(v=sql.105)

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;
Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
  • Thank you very much, all what i need is this little info that Throw is introduced in SQL server 2012, and thank you for clarifying RAISERROR statement – user2405574 May 21 '13 at 14:36
  • A slight difference. RAISERROR does not stop execution, whereas THROW does. – Mehmet AVŞAR Apr 16 '16 at 13:50
  • 1
    @MehmetAVŞAR That's not exactly correct. Execution is not stopped based on whatever it's RAISERROR or THROW, it stopped based on severity level. With RAISERROR you can set the severity yourself and therefor choose whatever the execution will be stopped. With THROW, you can't and default is 16 which does stop the execution, however when used from the CATCH block, it just forwards the last known exception, which might or might not be 16. – Nenad Zivkovic Apr 19 '16 at 06:49
  • @NenadZivkovic I've just tried again and error level 17 does not stop execution. It just requires the user to be a member of sysadmin to raise errors with level higher than 18. `CREATE PROCEDURE RaiseMeAnError AS PRINT 'Step 1' RAISERROR('Here is a low level error', 1, 1) PRINT 'Step 2' RAISERROR('Here is a low level error', 17, 1) PRINT 'Step 3'` – Mehmet AVŞAR Apr 19 '16 at 14:22
  • 1
    @MehmetAVŞAR `PRINT` is somewhat specific command when it comes to execution plan and SQL has run it before it got to raising error, try replacing it with `SELECT` in your demo code, and you will see that the third select will not happen. – Nenad Zivkovic Apr 20 '16 at 14:42
  • Got it @NenadZivkovic, but still confused. [Here](https://connect.microsoft.com/SQLServer/feedback/details/275308/have-raiserror-work-with-xact-abort) a member of SQL Server engine team says raiserror's this behaviour is by design. – Mehmet AVŞAR Apr 21 '16 at 06:44
  • What if `ERROR_MESSAGE() LIKE '%[%]s%'` or something? – binki Nov 23 '16 at 04:24
4

Use RAISERROR instead of Throw in your sql block.

Begin Try
 insert into BusinessID (BusinessID) values (@ID)
 insert into BusinessID (BusinessID) values (@ID)

End Try

Begin Catch

Print 'PK already exist'
  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
Pawan
  • 1,065
  • 5
  • 10