Just a little recap Ch. 12 & 13 written review

So beginning with chapter 12;

Lesson 1 was about transaction commands, how SQL handles transactions, how the user is able to see where SQL is in a transaction, and locking for readers and writers.  It also talked about rollbacks, which is essentially not committing a transaction.  Lots of lingo in this chapter, hence the flash cards that I made for myself.

Lesson 2 dealt with errors (raising errors, and throwing errors), one interesting note that I found out was that Microsoft is suggesting to use the THROW statement instead of RAISERROR in new applications, and RAISERROR can't be used in SQL Server's 2014's natively compiled stored procedures.  It also talked about XACT_ABORT (note the XACT part means 'transaction'), as you might imagine this command aborts a transaction.  Lastly, I think of Try/Catch kind of like an IF statement, because if an error occurs during the try part of transaction control is passed to the catch part of the batch.

Lesson 3 Was about Dynamic SQL, and SQL injection.  I had a post on SSC a while back when I first started studying that someone commented that I needed to use dynnamic SQL, and this example from the book is kind of what I was getting at in my post:
USE TSQL2012;
GO
DECLARE @SQLString AS NVARCHAR(4000), @address AS NVARCHAR(60);
SET @SQLString = N'
SELECT custid, companyname, contactname, contacttitle, address
FROM [Sales].[Customers]
WHERE address = @address';
SET @address = N'5678 rue de l''Abbaye';
EXEC sp_executesql
@statement = @SQLString
, @params = N'@address NVARCHAR(60)'
, @address = @address;

In this example, we declare a parameter for sp_executesql command that is actually some SQL code... doing this is the best way to protect against SQL injection.  

Chapter 13

Lesson 1 is all about my fav, stored procedures!  This is simple, if you have a query like:

SELECT name

FROM customers

WHERE firstname = 'Bob';

You can create a procedure out of this, and interchange 'Bob' with a different parameter.  The code would look something like this:

USE TSQL

IF OBJECT_ID('name','P') IS NOT NULL DROP PROC findbyname

CREATE PROC findbyname

   @custname AS VARCHAR(30)

AS

BEGIN

SELECT name

FROM customers

WHERE firstname = @custname

RETURN

END;

--Now you can execute the procedure by using EXECUTE, or EXEC for short:

EXEC findbyname

   @custname = 'Bob' --Or steve, or whatever name you want

This lesson also talks about branching logics for SQL code, these branching logics are statements such as: IF/ELSE, WHILE, WAITFOR, GOTO, RETURN.  When I read through this part of the book I had to remind myself that these branching logics are great, but remember that SQL is not an object oriented language, so when you start running this while loops and what not, it's going to kill your performance.  I use a while loops a bunch for the development work that I do, but that's a totally different animal.  Just a thought on this.  Also remember that procs (stored procedures) can all other procs... again the code can get messy.

Lesson 2 dealt with Triggers.  Triggers are just like procs, but there needs to be something has to happen in order for them to be executed.  SQL supports triggers for two different kinds of events, a data manipulation event (insert, updates, and deletes), and a data definition event (creating objects).

Lesson 3 is about user defined functions.  I found this great page on stack over flow a while back that I bookmarked that talked about the differences between user defined functions, and procs... make sure to review this: difference between udf's and procs

Sources: sqlhints.com, stackoverflow, Querying Microsoft SQL Server 2012; Itzik Ben-Gan, Dejan Sarka, Ron Talmage