Server Interaction  «Prev  Next»

Lesson 1

SQL-Server Interaction

Thus far we have used Transact-SQL to write queries that capture information in your database.
This module covers ways in which you can use Transact-SQL to interact with your server in other ways.
I call it the programming module because it covers ways that you can integrate programming concepts into your Transact-SQL statements to give you greater control over your database.

Learning objectives

After completing this module, you will be able to:
  1. Declare and use variables
  2. Use functions to obtain information and perform SQL Server 2012 operations
  3. Handle errors generated by SQL Server 2012
  4. Program your code so that errors are raised to notify you of events
In the next lesson, declaration and use of variables in your Transact-SQL code will be discussed.

Setting the Value in Your Variables

Well, you now know how to declare variables, but the question that follows is, How do you change their values?
There are three ways to set the value in a variable. You can initialize it in the DECLARE statement, use a SELECT statement, or use a SET statement. Functionally, SET and SELECT work almost the same, except that a SELECT statement can do a couple more things:
  1. SELECT can assign a value from a column in the SELECT statement
  2. SELECT can assign values to many variables in the same statement
So why have two ways of doing this? SELECT predates SET back in SQL Server history, so why go to the trouble to implement SET at all?
Suffice to say that SET is now part of the ANSI/ISO standard, and that is why it has been put in there. However, I cannot find anything wrong with the same functionality in SELECT, even ANSI/ISO seems to think that it is okay.