Day 1 of Documenting My Data Engineering Journey Today, I started learning about stored procedures in SQL. A stored procedure is like a saved set of SQL instructions inside a database. Instead of writing the same SQL code again and again, I can save it once and run it whenever I need it. Today, I learned three important concepts: 1. Control / `IF ELSE 2. Error Handling 3. Styling in Stored Procedures 1. Control / IF ELSE in Stored Procedures I learned that control statements help stored procedures make decisions. It works like normal decision-making in real life: sql IF something is true Do this ELSE Do something else For example, if a student scores 50 or above, they pass. Else, they fail. sql CREATE PROCEDURE CheckStudentResult @Score INT AS BEGIN IF @Score >= 50 BEGIN PRINT 'You passed!'; END ELSE BEGIN PRINT 'You failed. Try again.'; END END; I also learned that `IF ELSE` can be used to check things like: - Whether a student passed or failed - Whether someone is old enough to vote - Whether a product is in stock - Whether a customer should get a discount - Whether a user is an admin or normal user Example: sql CREATE PROCEDURE CheckVotingAge @Age INT AS BEGIN IF @Age >= 18 BEGIN PRINT 'You can vote.'; END ELSE BEGIN PRINT 'You are too young to vote.'; END END; So, I now understand that `IF ELSE` helps stored procedures choose what action to take. 2. Error Handling in Stored Procedures I learned that error handling means preparing for mistakes in SQL code. Sometimes something can go wrong, like: - Dividing by zero - Inserting duplicate data - Trying to update data that does not exist - A money transfer failing halfway Instead of letting the whole procedure crash badly, we can use: ```sql BEGIN TRY -- Code that might cause an error END TRY BEGIN CATCH -- Code that runs if an error happens END CATCH ``` Example: ```sql CREATE PROCEDURE DivideNumbers @Number1 INT, @Number2 INT AS BEGIN BEGIN TRY SELECT @Number1 / @Number2 AS Result; END TRY BEGIN CATCH PRINT 'Error: You cannot divide by zero.'; END CATCH END; ``` I also learned that SQL Server has useful error functions like: ```sql ERROR_MESSAGE() ``` This shows the actual error message. Example: ```sql CREATE PROCEDURE ShowErrorMessage AS BEGIN BEGIN TRY SELECT 10 / 0; END TRY BEGIN CATCH PRINT ERROR_MESSAGE(); END CATCH END; ``` I also learned about using transactions with error handling. A transaction means: Do everything successfully, or undo everything. Example: ```sql CREATE PROCEDURE TransferMoney @FromAccount INT, @ToAccount INT, @Amount DECIMAL(10,2) AS BEGIN BEGIN TRY BEGIN TRANSACTION; UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountId = @FromAccount; UPDATE Accounts SET Balance = Balance @Amount WHERE AccountId = @ToAccount; COMMIT TRANSACTION; PRINT 'Transfer successful.'; END TRY BEGIN CATCH ROLLBACK TRANSACTION; PRINT 'Transfer failed. Money returned.'; PRINT ERROR_MESSAGE(); END CATCH END; ``` This taught me that error handling is very important because it protects the database from bad or incomplete changes. 3. Styling in Stored Procedures I learned that styling means writing stored procedures in a clean and readable way. Good styling makes SQL easier to understand, especially when the code becomes long. Bad style: ```sql create procedure getstudents as begin select * from students end ``` Good style: ```sql CREATE PROCEDURE GetStudents AS BEGIN SELECT * FROM Students; END; ``` I learned some good styling rules: - Use clear procedure names - Use clear parameter names - Write SQL keywords in uppercase - Use indentation - Add comments only when needed - Keep the code organized Example of a clear procedure name: ```sql CREATE PROCEDURE GetAllCustomers AS BEGIN SELECT * FROM Customers; END; ``` Example of clear parameter names: ```sql CREATE PROCEDURE GetStudentById @StudentId INT AS BEGIN SELECT StudentId, Name, Age FROM Students WHERE StudentId = @StudentId; END; ``` Example with comments: ```sql CREATE PROCEDURE GetPassedStudents AS BEGIN -- Get students who scored 50 or above SELECT Name, Score FROM Students WHERE Score >= 50; END; ``` I also learned about using `SET NOCOUNT ON;`, which helps stop unnecessary messages from being returned. ```sql CREATE PROCEDURE RegisterStudent @Name VARCHAR(50), @Age INT, @ClassName VARCHAR(20) AS BEGIN SET NOCOUNT ON; IF @Age < 3 BEGIN PRINT 'Student is too young.'; RETURN; END; INSERT INTO Students(Name, Age, ClassName) VALUES (@Name, @Age, @ClassName); PRINT 'Student registered successfully.'; END; ``` My Summary for Today Today, I learned that stored procedures are saved SQL instructions that help make database work easier and reusable. I learned that: -Control / IF ELSE* helps stored procedures make decisions. -Error handling helps stored procedures handle problems without crashing badly. -Styling helps make stored procedures clean, readable, and professional. This is an important step in my data engineering journey because data engineers work with databases a lot. Understanding stored procedures will help me write better SQL, manage data safely, and build stronger database workflows. Day 1 completed. #Datafam