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

SQL110 – Transact SQL Essentials  Scripts Doug Shook

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!!! 2

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 3

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 4

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; 5

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. 6

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 7

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 8

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 9

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 10

Temporary Tables Create a global temporary table of 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 11

Table Comparison 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. What about Common Table Expressions? 12

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 13

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; 14

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. 15

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 16

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; 17

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; 18

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 19

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; 20

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 21