SQLQueryErrors.sql

-- Error Handeling

--An error indicates a problem or notable issue that arises during a database operation. 
--Errors can be generated by the SQL Server Database Engine in response to an event or failure at the system 
--level; or you can generate application errors in your Transact-SQL code.

use AdventureWorksDW2020;

-- System errors are predefined, and you can view them in the sys.messages system view. 
-- You can generate errors in Transact-SQL code to respond to application-specific conditions or to 
-- customize information sent to client applications in response to system errors. 

-- In addition, you can define custom error messages, members of the sysadmin server role can also use an
-- additional parameter, @with_log. When set to TRUE, the error will also be recorded in the Windows 
-- Application log.
-- Messages can be replaced without deleting them first by using the @replace = ‘replace’ option.

RAISERROR (N'%s %d', -- Message text,
    10, -- Severity,
    1, -- State,
    N'Custom error message number',
    2)

--Errors raised by THROW are always severity 16.

THROW 50001, 'An Error Occured',0

-- @@ERROR is a system variable that holds the error number of the last error that has occurred. 
RAISERROR(N'Message', 16, 1);
IF @@ERROR <> 0
PRINT 'Error=' + CAST(@@ERROR AS VARCHAR(8));
GO

-- Alerts can be created for specific error messages. The alerting service works by registering itself
-- as a callback service with the event logging service.

-- Try Catch Blocks

-- If the THROW statement is used in a CATCH block without any parameters, it will rethrow the error that 
-- caused the code to enter the CATCH block. 

BEGIN TRY
    -- code to be executed
END TRY

BEGIN CATCH
    PRINT ERROR_MESSAGE0.
    THROW
END CATCH

-- exercises:

SELECT CAST(N'Some text' AS int);
BEGIN TRY    
SELECT CAST(N'Some text' AS int);END TRYBEGIN CATCH    PRINT 'Error';
END CATCH;DECLARE @num varchar(20) = '0';
BEGIN TRY    
PRINT 5. / CAST(@num AS numeric(10,4));
END TRY 
BEGIN CATCH
END CATCH;

DECLARE @num1 varchar(20) = '0';
BEGIN TRY    
PRINT 5. / CAST(@num1 AS numeric(10,4));
END TRY

BEGIN CATCH    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));    
PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH;
DECLARE @num2 varchar(20) = 'A';
DECLARE @num3 varchar(20) = ' 1000000000';
DECLARE @num4 varchar(20) = 'A';BEGIN TRY    
PRINT 5. / CAST(@num4 AS numeric(10,4));
END TRY
BEGIN CATCH    
IF ERROR_NUMBER() IN (245, 8114)    
BEGIN  
PRINT 'Handling conversion error...'    
END    
ELSE    
BEGIN         
PRINT 'Handling non-conversion error...';   
END;    
PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));    
PRINT 'Error Message: ' + ERROR_MESSAGE();END CATCH;

go 


create proceduresCREATE PROCEDURE dbo.GetErrorInfo AS
PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));
PRINT 'Error Message: ' + ERROR_MESSAGE();PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(10));
PRINT 'Error State: ' + CAST(ERROR_STATE() AS varchar(10));
PRINT 'Error Line: ' + CAST(ERROR_LINE() AS varchar(10));
PRINT 'Error Proc: ' + COALESCE(ERROR_PROCEDURE(), 'Not within procedure');
GOexec dbo.GetErrorInfogoDECLARE @num varchar(20) = '0';
BEGIN TRY    
PRINT 5. / CAST(@num AS numeric(10,4));
END TRY
BEGIN 
CATCH    
EXECUTE dbo.GetErrorInfo;END CATCH;
go
--Rethrow the Existing Error Back to a Client
DECLARE @num varchar(20) = '0';
BEGIN TRY    
PRINT 5. / CAST(@num AS numeric(10,4));
END TRYBEGIN 
CATCH    
EXECUTE dbo.GetErrorInfo;     THROW;
END CATCH;
GODECLARE @num varchar(20) = 'A';

BEGIN TRY    
PRINT 5. / CAST(@num AS numeric(10,4));
END TRYBEGIN 
CATCH    

EXECUTE dbo.GetErrorInfo;    

IF ERROR_NUMBER() = 8134    BEGIN        
PRINT 'Handling devision by zero...';    
END    
ELSE     
BEGIN        
PRINT 'Throwing original error';        
THROW;    
END;
END CATCH;
goDECLARE @customerID AS INT = 30110;

DECLARE @fname AS NVARCHAR(20);
DECLARE @lname AS NVARCHAR(30);
DECLARE @maxReturns AS INT = 1; 
WHILE @maxReturns <= 10
BEGIN    
try                
SELECT @fname = FirstName, @lname = LastName FROM SalesLT.Customer        WHERE CustomerID = @CustomerID;
if @@ROWCOUNT > 0        
BEGIN        
PRINT CAST(@customerID as NVARCHAR(20)) + N' ' + @fname + N' ' + @lname;        
   
end try    
BEGIN CATCH    
PRINT 'Unable to run query';    
end CATCH    
SET @maxReturns += 1;    SET @CustomerID += 1;    END;GODECLARE @num10 varchar(20) = 'Challenge 2';
BEGIN TRY    PRINT 'Casting: ' + CAST(@num10 AS numeric(10,4));END TRYBEGIN CATCH    
PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH;

go

CREATE PROCEDURE dbo.DisplayErrorDetails AS
PRINT 'ERROR INFORMATION';PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS varchar(10));

PRINT 'Error Message: ' + ERROR_MESSAGE();PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(10));GO

DECLARE @num11 varchar(20) = 'Challenge 2';BEGIN TRY    PRINT 'Casting: ' + CAST(@num11 AS numeric(10,4));

END TRY BEGIN CATCH    EXECUTE dbo.DisplayErrorDetails;
END CATCH;