Transact-SQL Introduction

1. Transact-SQL Introduction ISYS 464

2. T-SQL • Transact-SQL is Microsoft's and Sybase's proprietary extension to the SQL language. Microsoft's implementation ships in the Microsoft SQL Server product. Sybase uses the language in its Adaptive Server Enterprise, the successor to Sybase SQL Server. SQL has been enhanced with additional features such as: • Control-of-flow language • Local variables • Various support functions for string processing, date processing, mathematics, etc. • T-SQL on-line reference: • http://msdn2.microsoft.com/en-us/library/ms189826.aspx

3. Stored Procedure • System procedures are batch files that are stored in the database and then executes by name. They provide secure interface to the data. • System procedures • Sp_help • Sp_databases • Sp-tables • Sp-columns tableName • Sp_stored_procedures • User-defined procedures • To run a procedure: • Exec procedureName

4. Listing Tables and Columns • Use system procedure sp_help • Exec sp_help • Select tables from the SysObjects Table: • Select * from sysobjects where type=‘U’; • Type • U – user tables • V – views • S – system tables • TR – triggers • P – stored procedure • K – primary key • F – foreign key • To show the names of columns: • Select * from customer where 1=2;

5. SELECT Dialect • Select 1+1; • Select 'my name is',cname from customer; • select getdate(); • select datepart(yy, getdate()),datepart(mm,getdate()),datepart(dd,getdate()) • select convert(varchar(40),getdate(),9);

6. SELECT with CASE select cid,cname,(case when rating='A' then 'Excellent' when rating='B' then 'Good' else 'Bad' end) As Greeting from customer;

7. Case with Update Statement Update Customer Set Credit = Case When Credit < 500 then Credit*1.15 Else Credit * 1.10 End

8. Top N Analysis SELECT Top 3 Student.SID, Student.SName, Student.Major, Student.GPA, Student.SEX FROM Student Order By GPA Desc;

9. Batch Programming • Declare local variables (last only in the batch it is declared): • Declare @counter int • Assigning value to a variable: • Select @counter = 1 • Assigning variable a value from a table: • Declare @custrating char(1) • Select @custrating = rating from customer where cid=‘C1’;

10. Global Variables • @@error: If not zero, an error occurred • @@rowcount; the number of records affected by the last statement

11. If Statement • If : • If @custrating = ‘A’ • Print ‘Excellent’ • Else • Print ‘Not good’ • Use Begin End to group statements after an IF or WHILE: • If @custrating = ‘A’ Begin • Print ‘Excellent’ • Select @counter = @counter + 1 • End

12. WHILE Statement • Declare @counter int • select @counter = 1 • while @counter < 5 begin • print @counter • select @counter = @counter + 1 • end

13. Cursor • A cursor is a pointer to a set of records returned by a SQL statement. It enables you to take a set of records and deal with it on a row-by-row basis.

14. Defining and Using Cursors Step 1: Declare cursor: • Declare cursorname Cursor • Read_Only • For SQL statement • Ex: DeclareACustomerCursor Read_Only For select * from customer where rating=‘a’ Step 2: Open cursor: OPEN cursorname Example: Open Acustomer Step 3: Fetch data into variables: FETCH Next From cursorname into list of local variables Example: Fetch Next ACustomer into @CustID, @CustName, @CustCity, @CustRating Use @@Fetch_Status to test if record exist. If @@Fetch_Status = 0, fetching is successful Step 4: CLOSE cursorname Step 5: Deallocate cursor deallocate Acustomer

15. Cursor Example Use MySQLDB Declare ACustomer Cursor Read_Only For Select * from customer where rating='A' /* SQL 92: For Select * from customer where rating='A' */ /*for read only */ Declare @CustID char(3), @CustName char(20), @CustCity char(20) Declare @CustRating char(1),@CustCredit decimal(7,2) Open ACustomer Fetch Next From ACustomer Into @CustID, @CustName,@CustCity,@CustRating,@CustCredit While @@Fetch_Status=0 Begin If @CustCredit > 1000 Select @CustName, 'Excellent customer' else select @CustName, 'Regular customer' Fetch Next From ACustomer Into @CustID, @CustName,@CustCity,@Custrating,@CustCredit end Close Acustomer deallocate Acustomer

16. Update Cursor Example Declare UpdateCustomer Cursor For Select * from customer where rating='A' For Update of Credit Declare @CustID char(3), @CustName char(20), @CustCity char(20) Declare @CustRating char(1),@CustCredit numeric(7,2) Open UpdateCustomer Fetch Next From UpdateCustomer Into @CustID, @CustName,@CustCity,@CustRating,@CustCredit While @@Fetch_Status=0 Begin If @CustCredit > 1000 Update Customer Set Credit = Credit * 1.15 Where Current of UpdateCustomer else Update Customer Set Credit = Credit * 1.1 Where Current of UpdateCustomer Fetch Next From UpdateCustomer Into @CustID, @CustName,@CustCity,@Custrating,@CustCredit end Close UpdateCustomer Deallocate UpdateCustomer

17. Stored Procedures • Stored procedures are batches that can be executed by name. • Benefits: • Enhanced control of data • Straightforward access to complex data operations • Improved performance

18. Procedure with Input Parameters CREATE PROCEDURE AddNewCustomer(@cid char(5),@cname char(20),@city char(20),@rating char(1),@credit numeric) AS insert into Customer values (@cid,@cname,@city,@rating,@credit) RETURN Command to call the procedure: AddNewCustomer 'C10','Chen','SF','A',2000 Note: Optional: Execute AddNewCustomer 'C10','Chen','SF','A',2000

19. On-the-Fly Execution of a quoted String Create Procedure ShowTable(@tableName varchar(30)) As exec ('select * from ' + @tableName) Return

20. Procedure with Output Parameters CREATE PROCEDURE showCustomers(@ID char(5), @CustRating char(1) output, @CustCredit numeric output) AS select @custRating=rating,@CustCredit=credit from customer where cid = @ID; RETURN Calling a procedure with output parameters: Declare @rating char(1),@credit numeric(7,2) Exec ShowCustomers 'C1',@rating output ,@credit output select @rating,@credit

21. Triggers • A trigger is a stored procedure associated with an action on a table. • DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. • DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements. • Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established.

22. Special Tables: Deleted, Inserted • SQL Server maintains two temporary tables, Deleted and Inserted, to keep the deleted records and inserted records created by the last command. • A modification is treated as a deletion of the old record, followed by an insertion of the new record. So the old record will be kept in the Deleted and the new record will be kept in the Inserted.

23. Create Trigger Command CREATE TRIGGER trigger_name ON tablename AFTER { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS

24. Example Alter TRIGGER newCustomerTrigger ON Customer AFTER INSERT AS BEGIN Select * from inserted Print 'NewCustomerTrigger was fired' END GO

25. Example Create TRIGGER UpdateTrigger ON Customer AFTER UPDATE AS BEGIN Select 'old record' select * from deleted Select 'New record' select * from inserted if Update(Credit) Begin rollback transaction Select 'old record' select * from deleted select 'New record' select * from inserted END End Note: Update(fieldName) tests if the field is updated

26. Using Inserted and Deleted Tables to Get the Old and New Values CREATE TRIGGER showOldNewValues ON Customer AFTER UPDATE AS BEGIN If Update(Credit) Begin select d.cid,d.cname,d.credit as OldCredit,i.credit as NewCredit from deleted d, inserted i where d.cid=i.cid End END GO

27. Function Example Create FUNCTION CreditEvaluation(@Credit numeric(7,2)) RETURNS char(5) AS BEGIN declare @result char(5) if @Credit > 6000 select @result = 'Good' else select @result='Bad' return(@result) END To use the function: select cid,cname,dbo.CreditEvaluation(credit) from customer