Programming with Transact-SQL 2 day training course from £590.00 plus VAT
Classroom - per delegate | £695.00 plus VAT £834.00 inc VAT |
In person at one of our UK locations, max 10 delegates |
In Person one on one course | £1,190.00 plus VAT £1,428.00 inc VAT |
In person at one of our UK locations, no other delegates - click here for details |
Closed Courses | £1,900.00 plus VAT £2,280.00 inc VAT |
In person at a location of your choosing, max 10 delegates (contact us for prices over 10 delegates) - click here for details |
Remote live public course | £590.00 plus VAT £708.00 inc VAT |
Remotely accessed over the internet, max 6 delegates - click here for details |
Remote live one on one course | £790.00 plus VAT £948.00 inc VAT |
Remotely accessed over the internet, no other delegates - click here for details |
Synopsis
Transact-SQL (or T-SQL) is Microsoft’s proprietary extension to SQL. This Transact-SQL programming training course will introduce you to the programming features of the language, using Microsoft SQL Server. With prior knowledge of SQL assumed, this course looks at the procedural programming language capabilities of T-SQL, such as local variables, flow control constructs, error handling and built-in functions. You will learn how to write your own functions to be used in SQL Server just in the way you use built in functions such as UPPER and GETDATE. You will also learn to write procedures, allowing you to execute any number of commands in a single database call. You'll write triggers, which automatically fire in response to a predefined database event - for example allowing you to automatically archive changed or deleted records. You'll find out how to get information from the database, such as whether there is an existing open transaction. All in all your capacity to work with SQL Server will massively increase over just using plain old SQL
Duration
2 daysCost
See options at top of the page
Prerequisites
A working level of basic SQL (SELECT, INSERT, UPDATE and DELETE statements) is assumed. Delegates without this experience should consider the Introduction to SQL course.
Detailed course contents
Introducing Transact-SQL
- What is Transact-SQL?
- Procedural Programming Language
- Programme structures
- What does T-SQL look like?
- Statement blocks
- Comments
- Adding comments
Variables
- What are variables?
- Declaring a variable
- Variable names
- Available data types
- The scope of a variable
- Setting a variable with SET
- Initialising a variable
- New operators in SQL Server 2008
- Displaying the values of variables
- Setting variables from queries
- Assigning values in the UPDATE Statement
Table variables
- Table variables
- Table variable rules
Conditional logic
- Conditions: IF
- Conditions: ELSE
- Conditions: Nested IF
- Conditions
- Note on declaring variables
Loops and other control-of-flow mechanisms
- Loops
- The WHILE statement
- BREAK
- CONTINUE
- GOTO
- WAITFOR
Processing records with cursors
- Cursors
- Using a cursor
- Declaring a cursor
- Local and global cursors
- Opening a cursor and fetching a record
- Check if a row was returned
- Using @@FETCH_STATUS
- Closing and deallocating a cursor
- @@CURSOR_ROWS
- Cursor variables
- FOR UPDATE / WHERE CURRENT OF
More cursor options
- More on the FETCH statement
- DECLARE CURSOR options
- Restrictions on cursor options
Handling errors
- Error handling in SQL Server
- The @@ERROR global function
- Working with @@ERROR
- TRY..CATCH
- The CATCH block
- More error functions
- Nested TRY..CATCH blocks
- RAISERROR
Stored procedures
- What are stored procedures?
- Advantages of stored procedures
- What is/is not allowed?
- Creating stored procedures
- Stored procedure names
- Executing stored procedures
- SET NOCOUNT ON
- Using a result set in an INSERT statement
- Parameters
- Executing a procedure with parameters
- Procedures return a value
- Setting the return value of a procedure
- Output parameters
- Viewing the source code of a stored procedure
- More procedure DDL
User Defined Functions
- What are UDFs?
- Creating UDFs
- Returning a value from a function
- Calling a scalar function
- Table-Valued Functions
- Calling table valued functions
- Inline table valued functions
- Multistatement table valued functions
- More UDF DDL
Schemas
- SQL Server schemas
- Creating a schema
- Referencing a schema’s objects
Triggers
- What are triggers?
- Uses of Triggers
- Types of trigger
- What can you do in trigger code?
- DML triggers
- DML trigger syntax
- The inserted and deleted tables
- The UPDATE()function
- COLUMNS_UPDATED
- INSTEAD OF triggers
- INSTEAD OF trigger rules
- View Triggers
- Notes on DML triggers
- DDL triggers
- Creating DDL triggers
- Notes on DDL triggers
- The EVENTDATA() Function
- XML returned by EVENTDATA()
- EVENTDATA() schema definition
- Logon triggers
- Finding information on triggers
- Changing a trigger definition
- Disabling, enabling and dropping triggers
- More trigger DDL
Global functions
- Global Functions
- @@TRANCOUNT
- @@IDENTITY
- @@VERSION
- SERVERPROPERTY()
Dynamic SQL
- What is dynamic SQL?
- Dynamic SQL example
- Guard against SQL injection
Availability
Click on a date to book it
Can't see a suitable date?
Click here to register your interest in this course and we will keep you informed when any new dates are added.
FAQs
-
What are the course times?
-
Courses start at 9:30am start on the first day of your course. Subsequent days will usually start 30 minutes earlier but check with your instructor. The course will end by 4:30am each day.
-
-
Do I need to bring anything?
-
No, everything is provided, you only have to bring yourself.
-
-
How can I pay?
-
Once you have booked you will receive a confirmation email, and shortly after that, an invoice. For in-person public and private courses the invoice is payable within 30 days, for online courses payment is required 7 days before the start of the course. Payment can be made by bank transfer (BACS etc), cheque or card.
-
-
Is there a dress code?
-
No, there is no dress code for any of our courses.
-
-
Are refreshments provided?
-
At our in-person courses, mid-morning and mid-afternoon refreshments will be provided. Lunch is not provided but all our venues are in central locations.
-
-
What is the cancellation policy?
-
Cancellations received within 7 days of making your booking or more than 21 days before the scheduled start date will not incur any charge. For cancellations received between 7 and 21 days before the scheduled start date a 50 percent charge will apply. For cancellations received less than 7 days before the scheduled start date a 100 percent charge will apply. For online courses where you have already been sent course materials, any refund is conditional on the return of the materials at your own expense.
-