Stored procedure (SQL server) Notes

Stored procedure is a sql code or set of sql, which is saved in server side, and can be executed  as many times. We can pass run time parameters to stored procedure. Below are the some of the basic things to write a stored procedure:

• Create stored procedure
• Logging
• Variables
• Iteration
• Transaction/Exception Handling

Create Stored Procedure:

CREATE PROCEDURE updateLocality @State nvarchar(50), @City nvarchar(30), @PostalCode  nvarchar(10), @Locality nvarchar(50)
AS
update Employee
set locality=@Locality
where state = @State and city=@City and postalCode=@PostalCode;
GO;

Execute Procedure:

EXEC updateLocality @State='TN' @City = "Chennai", @PostalCode = "600053" @Locality = 'Ambattur'; 

The procedure takes four input parameters (State, City, PostalCode, Locality) and update the locality for those matching state, city and postalcode.

Logging:

1.  First, to log the db response for the sql execution:

SET NOCOUNT OFF;

This logs the db response, for ex: no. of rows updated. This is switched off for debugging/demo purpose but it is always a best practice to switch on to avoid unnecessary log


2. Print execution log, something developer log as info
This can be done using PRINT or RAISE ERROR. The drawback with print seem it will be printed only after the script is executed whereas RAISE ERROR can log it during execution itself

Print 'Execution started'; -- self explanatory

RAISERROR('Execution started',0,1) WITH NOWAIT; 

• The first parameter is the message to display, 

• second is the error level, it treats anything with an error level from 0-10 as a message and does not affect code execution

• third is the system state. This ranges from -1 to 255 and anything outside of that results in an exception 

• WITH NOWAIT allows the immediate message output describing how the script is executing as it happens

Variables:

DECLARE @Message VARCHAR(100) = 'Welcome to Stored Procedure';
--initialize a local variable Message
--VARCHAR is the datatype/string for message. It can be of any supported datatype

Sqlserver does not support array of values, but we can do is

declare @listOfIDs table (id int);

insert @listOfIDs(id) select ID from Employee where state = @State and city=@City and postalCode=@PostalCode;

--This stores all matching employee id into listOfIDs

Iteration: 

Cursor: Helps us iterate over result set

DECLARE @CustomerId INT, @FirstName VARCHAR(30), @LastName VARCHAR(50)
DECLARE @MessageOutput VARCHAR(100)
DECLARE Customer_Cursor CURSOR FOR
    SELECT CustomerId, FirstName, LastName FROM Customers

OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor INTO
    @CustomerId, @FirstName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @MessageOutput = @FirstName + ' ' + @LastName
    RAISERROR(@MessageOutput,0,1) WITH NOWAIT
    FETCH NEXT FROM Customer_Cursor INTO
    @CustomerId, @FirstName, @LastName
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor

Transaction/Exception Handling: 

 By default, all sqls with in the procedure is executed independently, incase of any failure in any sql will not rollback the data. Transaction helps us to execute multiple sqls in a single transaction

CREATE PROCEDURE updateLocality @State nvarchar(50), @City nvarchar(30), @PostalCode  nvarchar(10), @Locality nvarchar(50)
AS
SET NOCOUNT OFF;
BEGIN
    BEGIN TRANSACTION;
    SAVE TRANSACTION OldStateSavePoint;
    BEGIN TRY
    RAISERROR('Execution started state: %s, city: %s, postalcode: %s, locality:%s',0,1, @State, @City, @PostalCode, @Locality) WITH NOWAIT;
    -- update employees
    update Employee
set locality=@Locality
where state = @State and city=@City and postalCode=@PostalCode;
RAISERROR('Employee table updated',0,1) WITH NOWAIT;
--some other sqls
RAISERROR('Table2 updated',0,1) WITH NOWAIT;
-- more
    END TRY
    BEGIN CATCH
    RAISERROR('Rollback - state: %s, city: %s, postalcode: %s, locality:%s',0,1, @State, @City, @PostalCode, @Locality) WITH NOWAIT;
    ROLLBACK TRANSACTION OldStateSavePoint;
    END CATCH
   
update Employee
set locality=@Locality
where state = @State and city=@City and postalCode=@PostalCode;
END;
GO

This ensures the transaction is rolledback incase of any failures

References:

https://www.w3schools.com/sql/sql_stored_procedures.asp

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15

http://structuredsight.com/2014/11/24/wait-wait-dont-tell-me-on-second-thought/

https://stackoverflow.com/questions/2622230/how-can-i-iterate-over-a-recordset-within-a-stored-procedure

https://dba.stackexchange.com/questions/134129/transaction-in-a-stored-procedure