Course description
The Programming Microsoft SQL Server with Transact-SQL training course introduces the delegate to the programming features of the Transact-SQL programming language, using Microsoft SQL Server. This course looks at the procedural programming language capabilities of T-SQL. The delegate will learn how to use local variables, write program flow control constructs, create stored procedures and triggers, handle errors and use built-in functions.
Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.
Who will the Course Benefit?Software development personnel and database support staff who need to write Transact-SQL scripts to enhance their productivity in manipulating data.
Course ObjectivesTo provide the skills needed to write programs using the main features of the Microsoft Transact-SQL Programming Language.
Upcoming start dates
Suitability - Who should attend?
A working knowledge of SQL is required along with practical experience in writing SQL statements. This can be obtained by attendance on the pre-requisite Querying Microsoft SQL Server with Transact-SQL course. Some previous programming experience will also prove advantageous.
Experience to the level as demonstrated in this course is recommended:
- Querying Microsoft SQL Server with Transact-SQL
Training Course Content
Programming Microsoft SQL Server with Transact-SQL Training Course
Course Contents - DAY 1
Course Introduction- Administration and Course Materials
- Course Structure and Agenda
- Delegate and Trainer Introductions
- Procedural Programming Language
- Program structures
- Statement blocks
- Comments
- What is a variable
- Declaring a variable
- Variable names
- Available data types
- Variable scope
- Using the SET command
- Initialise a variable
- Display the values of variables
- Set variables in a query
- Assign values to variables in the UPDATE Statement
- IF THEN ELSE statements
- Nested IF statements
- Specifying Conditions
- The basic Loop construct
- The WHILE loop
- The BREAK statement
- The CONTINUE statement
- The GOTO statement
- The WAITFOR statement
- What is a cursor?
- Cursor operations
- Declare a cursor
- Local and global cursors
- Open a cursor and fetch a row
- Cursor status checking with @@FETCH_STATUS
- Close and deallocate a cursor
- @@CURSOR_ROWS
- Use Cursor variables
- The FOR UPDATE and WHERE CURRENT OF statements
- Declare cursor options
- Cursor operation restrictions
Course Contents - DAY 2
Session 6: STORED PROCEDURES- What is a stored procedure?
- The advantages of using stored procedures
- What is allowed and disallowed in a stored procedure
- Create a stored procedure
- Execute a stored procedure
- Use parameters
- Execute a procedure with parameters
- Use SET NOCOUNT ON
- Use a result set in an INSERT statement
- Set the return value of a procedure
- Output parameters
- View the source code of a stored procedure
- Handling errors
- The @@ERROR global function
- Using @@ERROR
- The TRY..CATCH statement
- The CATCH block
- Other error functions
- The RAISERROR statement
- What is a user defined function?
- Creating user defined function
- Return a value from a user defined function
- Call a scalar function
- Table-Valued Functions
- Call table valued functions
- Inline table valued functions
- Multi-statement table valued functions
- What is a trigger?
- How a trigger can be used
- Trigger types
- What can be done in trigger code?
- DML triggers and syntax
- The inserted and deleted tables
- The UPDATE()function
- COLUMNS_UPDATED
- Use INSTEAD OF triggers
- The INSTEAD OF trigger rules
- View Triggers
- DDL triggers
- Create a DDL triggers
- Notes on DDL trigger
- Logon triggers
- View trigger information
- Alter a trigger definition
- Disable, enable and drop triggers
- What is dynamic SQL?
- Create a dynamic SQL statement
- Prevent SQL injection
Customer Outreach Award
We believe that it should be easy for you to find and compare training courses.
Our Customer Outreach Award is presented to trusted providers who are excellent at responding to enquiries, making your search quicker, more efficient and easier, too.
Reviews
Average rating 5
Continuing Studies
Further Learning
- Advanced Querying Microsoft SQL Server with Transact-SQL
Request info
Why StayAhead. From a single person on a scheduled course to large scale training projects StayAhead Training have the expertise and experience to help. Established since 1992, StayAhead Training is recognised as one of the leading independent IT Training specialists...
excellent trainer, couldn't ask for better :)