Microsoft SQL Server - Part 5 : Stored Procedure with Transaction and EXCEPTION Handling

SQL is a standard language for storing, manipulating and retrieving data in databases.
#sql #sqlserver #databasemanagement #microsoft

Our SQL tutorial will teach you how to use SQL in:
MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.

--------------------------------------------------------------------------------------------------------------------------------------------------
A stored procedure is a set of SQL statements that can be executed on the database. It is stored as an object in the database.

A stored procedure allows for code that is run many times to be saved on the database and run at a later time, making it easier for yourself and other developers in the future.

It also allows for extra features that aren’t available in regular SQL language to be used, such as loops, output, and manipulation variables.

--------------------------------------------------------------------------------------------------------------------------------------------------

CREATE [OR ALTER] PROC | PROCEDURE procedure_name [parameter_list]
AS
BEGIN
execution_section
END;
---------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City

EXEC SelectAllCustomers @City = 'London';

TRANSACTION:

A transaction is a single unit of work.
If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database.
If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

------------------------------------------------------------------------------------------------

Examples:

CREATE TABLE dbo.TestRethrow
(ID INT PRIMARY KEY);

------------------------------------------------
ALTER PROCEDURE SP_LearnExceptionHandling
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
INSERT dbo.TestRethrow(ID) VALUES(1);
-- Force error 2627, Violation of PRIMARY KEY constraint to be raised.
INSERT dbo.TestRethrow(ID) VALUES(1);
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
throw ;
END CATCH;
END;
EXEC SP_LearnExceptionHandling

SELECT * FROM dbo.TestRethrow

----------------------------------------------------------------------------------------------------------
ALTER PROCEDURE SP_Throw @ID as INT
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
IF NOT EXISTS (SELECT * FROM [SalesLT].[Customer] WHERE CustomerID = @ID)
THROW 70000,'Customer Doesn''t exits',1;
ELSE
BEGIN
Print 'Customer exists';
END;
END TRY
BEGIN CATCH
Throw;
END CATCH
END;

THROW (Transact-SQL)

Raises an exception and transfers execution to a CATCH block of a TRY...CATCH construct.

THROW [ error_number | @local_variable , message | @local_variable, state | @local_variable ]

Arguments
error_number
Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.

message
Is a string or variable that describes the exception. message is nvarchar(2048).

state
Is a constant or variable between 0 and 255 that indicates the state to associate with the message. state is tinyint.

References: