T-SQL编程

1.声明一个长度为10个字符的变量strName 并为其赋值为“SmallFish”
Decalare @strName CHAR(10)
Select @strName='SmallFish'
Print @strName
Go
2. 连接字符串举例
Declare @strName CHAR(10)
SET @strName='SmallFish'
Select '我的网名是：'+@strName
3. 根据输入的成绩，为学生的成绩评定等级
Declare @fltScope Float, @strResult CHAR(4)
Select @fltScope =65

SET @strResult=
Case
When @fltScope<70 and @fltScope>=60 Then '及格'
When @fltScope<80 and @fltScope>=70 Then '中等'
When @fltScope<90 and @fltScope>=80 Then '良好'
When @fltScope<100 and @fltScope>=90 Then '优秀'
Else '不及格'
End
Print '学生成绩评定结果：'+@StrResult
Go
4. 使用Goto 语句实现打印字符 123
Declare @X int
Select @X=1
KK:
Print @X
Select @X=@X+1
While @X<=100 goto KK
5.条件选择语句
Declare @intBigger int, @intSmaller int
Select @intBigger=100, @intSmaller=50
if @intBigger<@intSmaller
print '对了！'
Else
print '错了！'

if @intBigger>@intSmaller
print '结果对了！'
else
print '结果错了！'
Go
6. While语句
Declare @intCount int, @intSumValue int
Select @intSumValue=0, @intCount=1
While @intCount<=100
Begin
Select @intSumValue=@intSumValue+@intCount
Select @intCount=@intCount+1
End
Print '1+2+3+.....+100='+ cast(@intSumValue as char(20))
Go
7. Waitfor
Waitfor Delay '00:00:03'
Print '已经过了3秒了！'
go

Waitfor Time '12:48:00'
print '鬼来了！'
8. print 函数
print '当前系统时间为:'+ rtrim(convert(varchar(30),getdate())) +'.'

9. readtext
readtext 命令用于从数据类型为 Text，Ntext，或 image的列中读取数据， Readtext 命令语法：

例：读取pub_info表中pr_info 列的第二个至第26个字符。
use pubs
go
declare @ptrval varbinary(16)
select @ptrval=textptr(pr_info)
from pub_info pr Inner Join publishers p
on pr.pub_id=p.pub_id
and p.pub_name='New Moon Books'
readtext pub_info.pr_info @ptrval 1 25
go

10.select 命令可以给变量赋值
Declare @var1 nvarchar(30)
select @var1='Generic Name'
select @var1 as '公司名称'

11. set 命令可以给局部变量赋值
declare @myvar char(20)
set @myvar ='这是一个测试'
select @myvar
go
12. use pubs
go
declare @state char(2)
set @state='UT'
select rtrim(au_fname)+''+rtrim(au_lname) as Name, state
from authors
where state=@state
go
13.
Declare @intX int, @intY int, @intCount int
set @intCount=1
while @intCount<=5
Begin
print space(5-@intCount)+replicate('*',@intCount*2-1)
select @intCount=@intCount+1
End
go