There might be cases when you might want to create temporary tables; they are especially useful when using cursors.

The command to create a table is as follows:

CREATE TABLE tablename (
Fieldname type,
Fieldname type)

For example, the following code will create a customer's table with two fields:

CREATE TABLE customers(
  name varchar(100),
  age int
)

To create temporary tables, we usually add a # character to the name of the table; for example:

CREATE TABLE #customers(
  name varchar(100),
  age int
)

After you are done with the temporary table you created, it is a good practice to remove the table from the system. The command to delete a table is the DROP TABLE command; the following is an example:

DROP TABLE #temptable

A stored procedure is a very good way to store our queries in a way that we can use them more than once; SQL Server will optimize the execution by precompiling the query, so the next time we call the stored procedure, it will run faster.

To create a stored procedure you use the CREATE command as follows:

CREATE PROCEDURE mySP
AS
BEGIN
  SET NOCOUNT ON;
    -- Insert statements for procedure here
  SELECT * from FilteredAccount
END

To modify a stored procedure we use the ALTER command as follows:

ALTER PROCEDURE mySP
AS
BEGIN
  SET NOCOUNT ON;
    -- Insert statements for procedure here
  SELECT name from FilteredAccount
END

To delete a stored procedure we use the DROP command as follows:

DROP PROCEDURE mySP

To execute a stored procedure we use the EXEC command as follows:

EXEC mySP

Cursors are used when you need to read row by row from a query that generates more than one row in its result. If you need to make a calculation, for example, to show subtotals then you might need to use cursors.

To use a cursor you will first need to declare it as follows:

DECLARE account_cursor CURSOR FOR Select name, revenue from FilteredAccount

After you declare the cursor you will need to open it to start the calculation:

OPEN account_cursor

We will need to declare one variable per field. We will retrieve the value of the fields from the query we used in the cursor. In our example, we will need a variable to store the name and another to store the revenue, so we declare the variables as follows:

declare @name as varchar(160)
declare @revenue as money

We are now ready to fetch the rows one-by-one:

FETCH NEXT FROM account_cursor INTO @name, @revenue
WHILE @@FETCH_STATUS = 0
BEGIN
  -- do something here
  print @name  + ' ' + CAST( isnull(@revenue, '') as varchar(40))
  FETCH NEXT FROM account_cursor INTO @name, @revenue
END

Notice that in the code we will first use the FECTH NEXT method that will retrieve the first row and assign the values into the @name and @revenue variables; we will then iterate on the rest of the records by looking at the @@FETCH_STATUS flag. It will be equal to zero if there are more records available to read; otherwise it will be distinct to zero and our loop will be completed.

Between the BEGIN and END lines and before the last FECTH flag is where we put our logic, as shown in the following screenshot. In our example we are just printing the values.

When you are done with the cursor, you will need to close it and free the memory resources as follows:

CLOSE account_cursor
DEALLOCATE account_cursor

The complete cursor code will look as follows:

DECLARE account_cursor CURSOR FOR Select name, revenue from FilteredAccount
OPEN account_cursor
declare @name as varchar(160)
declare @revenue as money
FETCH NEXT FROM account_cursor INTO @name, @revenue
WHILE @@FETCH_STATUS = 0
BEGIN   
  -- do something here
  print @name  + ' ' + CAST( isnull(@revenue, '') as varchar(40))
  FETCH NEXT FROM account_cursor INTO @name, @revenue
END
CLOSE account_cursor
DEALLOCATE account_cursor

Transactions are used when you need to perform more than one operation such as Insert, Update, or Delete or when you want to validate that the result is what it is expected to be. If everything is good, you commit the transaction; if something goes wrong, you can roll back the transaction and everything goes back to what it was before.

To start a transaction, use the following command:

Begin tran T1

Here, T1 is the name of the transaction and can be any name you want. If everything is good with the operations and you want to commit the transaction, you use the following command:

Commit tran T1

If you want to cancel the transaction and go back to what it was before, you use the following command:

Rollback tran T1