SQL110 Transact SQL Essentials Scripts Doug Shook Scripts - - PowerPoint PPT Presentation

SQL110 – Transact SQL Essentials

 Scripts

Doug Shook

2

Scripts

 Series of SQL Statements

– Grouped into batches

 Batches are executed when a GO statement is hit

– Why are these batches necessary?

 Certain commands cannot be combined with other

statements in a batch – Views, triggers, procedures, schemas, functions

 Comments – use them!!!

3

Script Processing

Transact-SQL statements for controlling the flow of execution

 IF...ELSE  BEGIN...END  WHILE  BREAK  CONTINUE  TRY...CATCH  GOTO  RETURN

Other Transact-SQL statements for script processing

 USE  PRINT  DECLARE  SET  EXEC

4

Variables

 Use DECLARE to create variables

– Names always start with @ – Include the type – Initial value?

 Variables can only be used in the batch where they

are defined

 SET can be used to assign value

– Or SELECT

5

Variables

A SQL script that uses variables

USE AP; DECLARE @MaxInvoice money, @MinInvoice money; DECLARE @PercentDifference decimal(8,2); DECLARE @InvoiceCount int, @VendorIDVar int; SET @VendorIDVar = 95; SET @MaxInvoice = (SELECT MAX(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorIDVar); SELECT @MinInvoice = MIN(InvoiceTotal), @InvoiceCount = COUNT(*) FROM Invoices WHERE VendorID = @VendorIDVar; SET @PercentDifference = (@MaxInvoice - @MinInvoice) / @MinInvoice * 100;

6

Variables

A SQL script that uses variables (continued)

PRINT 'Maximum invoice is $' + CONVERT(varchar,@MaxInvoice,1) + '.'; PRINT 'Minimum invoice is $' + CONVERT(varchar,@MinInvoice,1) + '.'; PRINT 'Maximum is ' + CONVERT(varchar,@PercentDifference) + '% more than minimum.'; PRINT 'Number of invoices: ' + CONVERT(varchar,@InvoiceCount) + '.';

The response from the system

Maximum invoice is $46.21. Minimum invoice is $16.33. Maximum is 182.97% more than minimum. Number of invoices: 6.

7

Table Variables

 Can store an entire result set  Defining columns is the same as if you were using

CREATE TABLE

 Still only has batch scope  Can be used with SELECT, INSERT, UPDATE, DELETE

8

Table Variables

A SQL script that uses a table variable

USE AP; DECLARE @BigVendors table (VendorID int, VendorName varchar(50)); INSERT @BigVendors SELECT VendorID, VendorName FROM Vendors WHERE VendorID IN (SELECT VendorID FROM Invoices WHERE InvoiceTotal > 5000); SELECT * FROM @BigVendors;

The result set

9

Temporary Tables

 Exist for the duration of the database session

– I.e. As long as you have the query window open

 Two types

– Local (#) – Global (##)

 Derived tables are still more efficient

– Temp tables are useful in complex scripts

10

Temporary Tables

A script that uses a local temporary table

SELECT TOP 1 VendorID, AVG(InvoiceTotal) AS AvgInvoice INTO #TopVendors FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC; SELECT Invoices.VendorID, MAX(InvoiceDate) AS LatestInv FROM Invoices JOIN #TopVendors ON Invoices.VendorID = #TopVendors.VendorID GROUP BY Invoices.VendorID;

The result set

11

Temporary Tables

Create a global temporary table

• f random numbers

CREATE TABLE ##RandomSSNs (SSN_ID int IDENTITY, SSN char(9) DEFAULT LEFT(CAST(CAST(CEILING(RAND()*10000000000) AS bigint) AS varchar),9)); INSERT ##RandomSSNs VALUES (DEFAULT); INSERT ##RandomSSNs VALUES (DEFAULT); SELECT * FROM ##RandomSSNs;

The result set

12

Table Comparison

What about Common Table Expressions?

The five types of Transact-SQL table objects

Type Scope Standard table Available within the system until explicitly deleted. Temporary table Available within the system while the current database session is open. Table variable Available within a script while the current batch is executing. Derived table Available within a statement while the current statement is executing. View Available within the system until explicitly deleted.

13

IF...ELSE

 Guidelines

– Use BEGIN and END to enclose conditionally executed statements (always!) – Be careful when nesting

• 2 layers deep, maximum

 Note that there is not ELSEIF construct

14

IF...ELSE

A script that uses an IF...ELSE statement

USE AP; DECLARE @MinInvoiceDue money, @MaxInvoiceDue money; DECLARE @EarliestInvoiceDue smalldatetime, @LatestInvoiceDue smalldatetime; SELECT @MinInvoiceDue = MIN(InvoiceTotal - PaymentTotal - CreditTotal), @MaxInvoiceDue = MAX(InvoiceTotal - PaymentTotal - CreditTotal), @EarliestInvoiceDue = MIN(InvoiceDueDate), @LatestInvoiceDue = MAX(InvoiceDueDate) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;

15

IF...ELSE

A script that uses an IF...ELSE statement (cont.)

IF @EarliestInvoiceDue < GETDATE() BEGIN PRINT 'Outstanding invoices overdue!'; PRINT 'Dated ' + CONVERT(varchar,@EarliestInvoiceDue,1) + ' through ' + CONVERT(varchar,@LatestInvoiceDue,1) + '.'; PRINT 'Amounting from $' + CONVERT(varchar,@MinInvoiceDue,1) + ' to $' + CONVERT(varchar,@MaxInvoiceDue,1) + '.'; END; ELSE --@EarliestInvoiceDue >= GETDATE() PRINT 'No overdue invoices.';

The response from the system

Outstanding invoices overdue! Dated 04/09/12 through 04/30/12. Amounting from $30.75 to $19,351.18.

16

Testing for Existence

 Before you work with a DB object, you should make

sure it exists – Also need to check before object creation

 OBJECT_ID

– Used to check for tables, views, procedures, functions, triggers

 DB_ID

– Used to check for a database

 Commonly used with IF...ELSE

17

DB_ID

The syntax of the DB_ID function

DB_ID('database')

Test if a database exists before deleting it

USE master; IF DB_ID('TestDB') IS NOT NULL DROP DATABASE TestDB; CREATE DATABASE TestDB;

18

OBJECT_ID

The syntax of the OBJECT_ID function

OBJECT_ID('object')

Test for the existence of a table

IF OBJECT_ID('InvoiceCopy') IS NOT NULL DROP TABLE InvoiceCopy;

Another way to test for the existence of a table

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'InvoiceCopy') DROP TABLE InvoiceCopy;

Test for the existence of a temporary table

IF OBJECT_ID('tempdb..#AllUserTables') IS NOT NULL DROP TABLE #AllUserTables;

19

Repetition

 WHILE can be used to repeat execution of

statements – Notice that there is no FOR construct...

 Like IF...ELSE, should always use BEGIN...END with

a WHILE

 BREAK can be used to exit the loop immediately  CONTINUE can be used to skip back to the top of the

loop

20

Repetition

A script that uses a WHILE loop

USE AP; IF OBJECT_ID('tempdb..#InvoiceCopy') IS NOT NULL DROP TABLE #InvoiceCopy; SELECT * INTO #InvoiceCopy FROM Invoices WHERE InvoiceTotal – CreditTotal - PaymentTotal > 0; WHILE (SELECT SUM(InvoiceTotal - CreditTotal – PaymentTotal) FROM #InvoiceCopy) >= 20000 BEGIN UPDATE #InvoiceCopy SET CreditTotal = CreditTotal + .05 WHERE InvoiceTotal – CreditTotal – PaymentTotal > 0;

21

Repetition

A script that uses a WHILE loop (continued)

IF (SELECT MAX(CreditTotal) FROM #InvoiceCopy) > 3000 BREAK; ELSE --(SELECT MAX(CreditTotal) FROM #InvoiceCopy)

• - <= 3000

CONTINUE; END; SELECT InvoiceDate, InvoiceTotal, CreditTotal FROM #InvoiceCopy;

The result set