Personal Tech Journal

Partitioning is helpful for managing and accessing smaller chunks of data very efficiently and at a faster pace without compromising on data integrity. SQL Server supports Range Partitions. This means that the partitioning is based on data ranges.

To know more about the error information, we can use the below mentioned functions 1. ERROR_NUMBER() returns the number of the error. 2. ERROR_SEVERITY() returns the severity. 3. ERROR_STATE() returns the error state number. 4. ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred. 5. ERROR_LINE() returns the line number inside the routine that caused the error. 6. ERROR_MESSAGE() returns the complete text of the error message. As discussed in my previous post the divide by zero error can be seen using the error information functions. CREATE PROCEDURE GenerateDetailedException AS BEGIN BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH END

Exception Handling is a practice to capture the error efficiently and display the error in a well formatted way. In SQL Server, we use the TRY..CATCH Block for handling exceptions. The syntax is as follows BEGIN TRY       { sql_statement | statement_block } END TRY BEGIN CATCH       [ { sql_statement | statement_block } ] END CATCH [ ; ] The below shows an exception message for divide by zero error CREATE PROCEDURE GenerateException AS BEGIN BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT 'Error Occurred' END CATCH END

@@ERROR returns zero if the last executed statement did not throw an error, else it will return an error number. The below shows a error message with error number when an Divide by Zero is encountered. CREATE PROCEDURE ErrorNumber AS BEGIN DECLARE @ErrorNumber varchar(20) SELECT 1/0 SET @ErrorNumber = @@ERROR PRINT 'Error Generated : ' + @ErrorNumber END

RAISERROR will use the built-in messages in sys.messages view or dynamically generated error message. The Syntax is as follows RAISERROR ( { msg_id | msg_str | @local_variable }     { ,severity ,state }      [ ,argument [ ,...n ] ] )     [ WITH option [ ,...n ] ] Severity 0 – 18 , Can be raised by any user Severity 19 – 25, Can be raised by those who have sysadmin role. State : This is a integer value between 0 to 255 The below shows an error message if the employee id does not exists CREATE PROCEDURE GenerateError(@EmployeeID int) AS BEGIN If exists(SELECT 1 FROM employee WHERE EmployeeID = @EmployeeID) BEGIN SELECT * FROM Employee WHERE EmployeeID = @EmployeeID END ELSE BEGIN RAISERROR ('Employee ID does not exists', 10, 2) END END