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;