new courses
for 2010

check out our new linux, c#, ruby on rails and asp.net 3.5 courses, with quality material and leading instructors. stay up to date with the very latest in web development technology.

Programming with Transact-SQL - 2 day course

24-25 November 2010LondonClick here to book

Can't see a suitable date or location?
Click here to register your interest in this course and we will
keep you informed when any new dates are added.

Synopsis

Transact-SQL (or T-SQL) is Microsoft’s and Sybase’s proprietary extension to SQL. This course will introduce you to the programming features of the language, using Microsoft’s SQL Server 2008. 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.

Duration

2 days

Cost

£595

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()

 

24-25 November 2010LondonClick here to book

Can't see a suitable date or location?
Click here to register your interest in this course and we will
keep you informed when any new dates are added.

our courses
up to date, relevant, thorough


more about our courses

our services
leading professionals, teaching professionals


more about our courses

Share

 

©impartica IT training. All right reserved.