-- Note! This script shold be run from SQLCMD, because it uses syntax that only
-- works in SQLCMD.
-- Most errors occurs in Northwind, but we start some in tempb.
USE tempdb
go
; THROW 87000, 'This is a demo error', 12
go
-- This is a very simple SP that raises a user-defined error.
IF object_id('demo_error_sp') IS NULL
   EXEC('CREATE PROCEDURE demo_error_sp AS PRINT 12')
go
ALTER PROCEDURE demo_error_sp AS
    THROW 88000, 'This is another demo error', 98
go
EXEC demo_error_sp
go
EXEC sp_help nosuchtable    -- This an error from a system procedure
go

-- Let's move over to Northwind for more errors.
USE Northwind
go
-- There is nothing wrong with this procedure.
IF object_id('temptable_sp') IS NULL
   EXEC('CREATE PROCEDURE temptable_sp AS PRINT 12')
go
ALTER PROCEDURE temptable_sp @OrderDate date AS
   CREATE TABLE #orders (OrderID    int            NOT NULL PRIMARY KEY,
                         CustomerID nchar(5)       NOT NULL,
                         CompanyName nvarchar(40)  NOT NULL,
                         City        nvarchar(15)  NOT NULL,
                         Country     nvarchar(15)  NOT NULL,
                         Amount      decimal(10,2) NOT NULL)

   INSERT #orders (OrderID, CustomerID, CompanyName, City, Country, Amount)
      SELECT O.OrderID, C.CustomerID, C.CompanyName, C.City, C.Country, OD.Amount
      FROM   Orders O
      JOIN   Customers C ON O.CustomerID = C.CustomerID
      JOIN   (SELECT OrderID, SUM(UnitPrice * Quantity * (1 - Discount)) AS Amount
              FROM   [Order Details]
              GROUP  BY OrderID) AS OD ON O.OrderID = OD.OrderID
      WHERE  OrderDate = @OrderDate

   SELECT OrderID, CustomerID, CompanyName, City, Country, Amount
   FROM   #orders
   ORDER  BY CustomerID, OrderID
go
-- But one of the calls below fails because of the illegal date.
go
EXEC temptable_sp '19970418'
EXEC temptable_sp '19970229'
go

-- This SP produces a foreign-key error. However, first attempt has compilation error.
-- Run both as EXEC and as RPC.
IF object_id('FK_error_sp') IS NULL
   EXEC('CREATE PROCEDURE FK_error_sp AS PRINT 12')
go
ALTER PROCEDURE FK_error_sp @OrderID    int,
                            @CustomerID nchar(5) AS
   UPDATE Orders
   SET    CustomerID = @CustomerID
   WHERE  Order = @OrderID
go
ALTER PROCEDURE FK_error_sp @OrderID    int,
                            @CustomerID nchar(5) AS
   UPDATE Orders
   SET    CustomerID = @CustomerID
   WHERE  OrderID = @OrderID
go
EXEC FK_error_sp 11000, 'GAMMA'
go
{call FK_error_sp (10555, 'DELTA')}
go

-- This procdure catches an error and rethrows it with
-- THROW. That only results in a single event.
IF object_id('Rethrow_sp') IS NULL
   EXEC('CREATE PROCEDURE Rethrow_sp AS PRINT 12')
go
ALTER PROCEDURE Rethrow_sp @ProductID int AS
BEGIN TRY
   DELETE Products WHERE ProductID = @ProductID
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
END CATCH
go
EXEC Rethrow_sp 76
go

-- This is a 
EXEC missing_sp 'In vein'
go

-- This is a customer procedure for re-raising errors.
IF object_id('error_handler_sp') IS NULL
   EXEC('CREATE PROCEDURE error_handler_sp AS PRINT 12')
go
ALTER PROCEDURE error_handler_sp AS
SET XACT_ABORT, NOCOUNT ON

   DECLARE @errno    int            = error_number(),
           @severity int            = error_severity(),
           @state    int            = error_state(),
           @proc     sysname        = error_procedure(),
           @lineno   int            = error_line(),
           @errmsg   nvarchar(2048) = error_message()

   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') +
                       ', Line ' + ltrim(str(@lineno)) +
                       '. Errno ' + ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)
go

-- And here is a procedure that re-raises an error with help
-- of error_handler_sp. This results in two events in the 
-- X-event session.
IF object_id('Reraise_sp') IS NULL
   EXEC('CREATE PROCEDURE Reraise_sp AS PRINT 12')
go
ALTER PROCEDURE Reraise_sp @ProductID   int,
                           @ProductName nvarchar(30) AS
BEGIN TRY
   UPDATE Products
   SET    ProductName = @ProductName
   WHERE  ProductID = @ProductID
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
END CATCH
go
EXEC Reraise_sp 14, NULL
go

-- This SP catches an error, but do not re-raise it.
-- The error is tracked by the X-event session.
IF object_id('Ignore_sp') IS NULL
   EXEC('CREATE PROCEDURE Ignore_sp AS PRINT 12')
go
ALTER PROCEDURE Ignore_sp @OrderID   int, 
                          @dateasstr CHAR(8) AS
BEGIN TRY
   UPDATE Orders 
   SET   OrderDate = CONVERT(date, @dateasstr)
   WHERE OrderID = @OrderID
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   -- ; THROW  No re-reaise; just ignore error.
END CATCH
go
EXEC Ignore_sp 10800, '20190230'
go

-- This SP raises a user-defined error.
IF object_id('userdefined_sp') IS NULL
   EXEC ('CREATE PROCEDURE userdefined_sp AS PRINT 12')
go
ALTER PROCEDURE userdefined_sp @ProductID int AS
   IF NOT EXISTS (SELECT * FROM Products WHERE ProductID = @ProductID)
   BEGIN
      RAISERROR('No such product ID: %d', 16, 1, @ProductID)
      RETURN
   END
   SELECT COUNT(*) AS OrderCnt
   FROM   [Order Details]
   WHERE  ProductID = @ProductID
go
EXEC userdefined_sp 91
go

-- Here is an example with nested procedures, where the error
-- occurs in the innermost procedure. (And because the middle
-- procedure runs a cursor, the error is raised four times.)
IF object_id('innermost_sp') IS NULL
   EXEC ('CREATE PROCEDURE innermost_sp AS PRINT 12')
IF object_id('middle_sp') IS NULL
   EXEC ('CREATE PROCEDURE middle_sp AS PRINT 12')
IF object_id('outer_sp') IS NULL
   EXEC ('CREATE PROCEDURE outer_sp AS PRINT 12')
go
ALTER PROCEDURE innermost_sp @OrderID int AS
   INSERT #temp(OrderID, ProductID, UnitPrice, Quantity)
      SELECT OrderID, ProductID, UnitPrice, Quantity
      FROM   [Order Details]
      WHERE  OrderID = @OrderID
go
ALTER PROCEDURE middle_sp @CustomerID nchar(5) AS
   CREATE TABLE #temp (OrderID     int       NOT NULL,
                       ProductID   smallint  NOT NULL,
                       UnitPrice   money     NOT NULL,
                       Quantity    smallint  NOT NULL,
                       PRIMARY KEY (OrderID)
   )

   DECLARE @cur CURSOR,
           @OrderID int
   SET @cur = CURSOR STATIC FOR
      SELECT OrderID FROM Orders WHERE CustomerID = @CustomerID

   OPEN @cur

   WHILE 1 = 1
   BEGIN
      FETCH @cur INTO @OrderID
      IF @@fetch_status <> 0
         BREAK

      EXEC innermost_sp @OrderID
   END

   SELECT @CustomerID AS CustomerID, * FROM #temp
go
ALTER PROCEDURE outer_sp @City nvarchar(15) AS

   DECLARE @cur        CURSOR,
           @CustomerID nchar(5)

   SET @cur = CURSOR STATIC FOR
      SELECT CustomerID FROM Customers WHERE City = @City

   OPEN @cur

   WHILE 1 = 1
   BEGIN
      FETCH @cur INTO @CustomerID
      IF @@fetch_status <> 0
         BREAK

      EXEC middle_sp @CustomerID
   END
go
EXEC outer_sp 'Berlin'
go

-- This procedure tries to create dynamic SQL, but there is a syntax error.
IF object_id('dynsqlsyntax_sp') IS NULL
   EXEC ('CREATE PROCEDURE dynsqlsyntax_sp AS PRINT 12')
go
ALTER PROCEDURE dynsqlsyntax_sp @CompanyName nvarchar(40) AS
   DECLARE @sql nvarchar(MAX)
   SELECT @sql = 'SELECT * FROM Customers WHERE CompanyName = ''' + @CompanyName + ''''
   EXEC(@sql)
go
EXEC dynsqlsyntax_sp 'B''s Beverages'
go

-- This procedure also does dynamic SQL, but here it is a run-time error.
IF object_id('dynsqlruntime_sp') IS NULL
   EXEC ('CREATE PROCEDURE dynsqlruntime_sp AS PRINT 12')
go
ALTER PROCEDURE dynsqlruntime_sp @OrderID   int,
                                 @ProductID smallint,
                                 @Quantity  int AS
   DECLARE @sql nvarchar(MAX) = N'
        UPDATE [Order Details]
        SET    Quantity = @Quantity
        WHERE  OrderID = @OrderID
          AND  ProductID = @ProductID'
   DECLARE @params nvarchar(4000) = N'@OrderID int, @ProductID smallint, @Quantity int'

   EXEC sp_executesql @sql, @params, @OrderID, @ProductID, @Quantity
go
EXEC dynsqlruntime_sp 11000, 77, 40000
go

-- This procedure is modified after it been executed with an error. Therefore,
-- the name and the statement is missing in view_exceptions.
IF object_id('modified_sp') IS NULL
   EXEC ('CREATE PROCEDURE modified_sp AS PRINT 12')
go
ALTER PROCEDURE modified_sp @n int AS
   SELECT 1E0 / @n
go
EXEC modified_sp 0
go
ALTER PROCEDURE modified_sp @n int AS
   SELECT 1E0 / nullif(@n, 0)
go
EXEC modified_sp 0
go

-- This batch emulates an application sending a parameterised statement.
{call sp_executesql(N'UPDATE Orders SET ShipVia = @ShipperID 
                      WHERE OrderID = @OrderID', 
                    N'@ShipperID int, @OrderID int', 54, 10248)}

go