Transact-SQL

Transact-SQL

Transact-SQL语言是SQL语言的增强版

声明局部变量

DECLARE 
{@变量名称[AS]变量类型}

注释方式

单行注释：–
多行注释：/* … */

流程控制语句

IF…ELSE语句

IF 表达式
  {语句块}
  ELSE
  {语句块}

语句块中包含BEGIN…end语句

CASE语句

CASE 表达式
WHEN...THEN...
[n...]
[else]
end

Select 员工姓名，所任职位，员工职称
Case 所任职位
when ‘经理’ then ‘高级职称’
when ‘主管’ then ‘中级职称’
else ‘其他职称’
end
FROM 员工信息

while语句

while 布尔表达式
{语句块}
[BREAK]
{语句块}
[CONTINUE]
语句块

DECLARE @i int, @num int
Set @i = 1
Set @num = 1
While @i<=10
BEGIN
SET@num=@num*@i
set@i=@i+1
END
PRINT @num

WAITFOR延迟语句

waitfor
{
DELAY time
| TIME time
}

Waitfor delay ’00:00:05’
Exec sp_help

Waitfor time ’21:11:05’
Exac sp_helpdb

GOTO语句

Declare @count int
Set @counter = 1
While @counter <10
BEGIN 
Print @counter 
Set @counter =@counter + 1
If @counter=4 GOTO branch one –jumps to the first branch.
If @counter=5 GOTO branch_two –this will never execute.
End
Branch one:
Print ‘jumping to branch one.’
GOTO Branch_three: --this will prevent branch_two from executing
Branch two:
Print ‘jumping to branch two.’
Branch_three:
Print ‘jumping to branch three.’

**TRY…CATCH错误处理语句

begin try
{语句块}
FND TRY
BEGIN CATCH
{语句块}
end CATCH

Begin try
Declare @num int
Set @num = 1/0
Select @num
End try
Begin catch
Select err_line() as ‘错误行数’

End catch