SQL Server 笔记：T-SQL programming basic

Flow control :=============
DECLARE @increase money
DECLARE @salary money

SET @increase = $1000
SELECT @salary = salary FROM salaries WHERE ssn=’123456789’
IF @salary < $100000
BEGIN
SET @salary = @salary + @increase
UPDATE salaries SET salary = @salary WHERE ssn =
‘123456789’
END
ELSE
PRINT ‘HAVE A NICE DAY’

while==============

WHILE (SELECT SUM(bonus) FROM salaries) <= $1000000
BEGIN
UPDATE salaries SET bonus = salary * 0.02
END


Other keys:=============
continue, break, return

BREAK triggers an immediate exit from the innermost WHILE loop and jumps to
the line after END statement. If you are using nested loops you will need to use
the BREAK statement for every loop in order to get out completely.
CONTINUE immediately returns you to the WHILE condition; not a single statement
following CONTINUE will be executed.
RETURN is used in stored procedures (covered in the next session). It causes
execution to stop immediately and returns results to the calling procedure or the
client.

==============================
WAITFOR introduces a delay in the execution. The following statement will suspend
execution until 6:15 p.m.; a statement on the next line will be executed
after this time.

WAITFOR TIME ‘18:15’

You can also suspend the process for a certain amount of time, in the following
example, it’s five seconds:
WAITFOR DELAY ‘000:00:05’
One of the most maligned flow-control statements is the infamous GOTO:
IF @salary < 10000
GOTO ask_for_raise
many more statements here
ask_for_raise:
UPDATE salaries SET salary = @salary * 0.1

=================================
Logical operators
Logical operators evaluate to true or false following the rules of Boolean algebra—
they are, in fact, Boolean operators. The full list of the logical operators is given in

Logical Operators ：Operator Description
ALL True if all of a set of compared values evaluates to true
AND True if both expressions evaluate to true
ANY True if any one of a set of compared values evaluates to true
BETWEEN True if the value is within a specified range
EXISTS True if a subquery (introduced later in this session) returns any
records
IN True if the result is equal to one in a list
LIKE True if the result matches a pattern
NOT Reverses the value of any other logical operator (such as NOT IN)
OR True if either logical expression evaluates to true
SOME True if some of a set of compared values evaluates to true

================================
The string concatenation operator
String concatenation is an operation you’ll find yourself performing over and over
again. Luckily, it is very intuitive—T-SQL uses the plus sign (+) to concatenate
strings. You can use it in SELECT statements like the following:

SELECT au_fname + ‘,’ + au_lname FROM authors
You can also use it to produce console output:
DECLARE @MyString VARCHAR(40)
SET @MyString = ‘concatenating’ + ‘ ‘ + ‘strings’ + ‘ is ‘ +
‘easy’
PRINT @MyString

=====================================

Unary operators


Unary Operators
Operator Description
+ The number is positive
- The number is negative

The unary operators listed in Table 10-4 work only on a numeric type of the variable.
They enable you to use negative and positive numbers. The default for a
number is positive—a number without a negative sign is considered positive.

Table 10-4The following sample creates two variables, assigns an integer value to one of
them, and assigns the negative value of the first variable to the second.
DECLARE @Num1 int
DECLARE @Num2 int
SET @Num1 = 5
SET @Num2 = -@Num2
PRINT CAST(@Num2 AS VARCHAR(2))
In Books Online you also will find the bitwise unary operator (~), which performs
the logical NOT operation.

==============================

Using the CASE Function
In T-SQL the CASE function compares two or more values and returns some predefined
result. Consider the following sample in which your boss wants a suggestion
based on overall employee performance.
SELECT Emp_FirstName + ‘,’ + Emp_LastName, suggestions =
CASE rating
WHEN ‘excellent’ THEN ‘deserves a bonus’
WHEN ‘good’ THEN ‘needs to improve’
WHEN ‘poor’ THEN ‘ready to be fired’
ELSE ‘no suggestions’
END
FROM employees
To those who program in any other language, the CASE statement
of T-SQL can be somewhat confusing. It is not equivalent to the
CASE you might know from C or Visual Basic, but it is similar to
the IIF function. For example, consider the following: variable =
IIF( expression, true part, false part). In plain English, this
means that if the expression yields true then the true part will
be returned; otherwise, false part is assigned to the variable.
Note

============================