文章来源:IT工程信息网 http://www.systhinker.com/?action-viewnews-itemid-17503
最近装了VS2010尝鲜。在Win7下速度明显快了很多。顺便装了Microsoft SQL Server 2008 R2,在看SQL 2008对T-SQL语法的增强的时候注意到了一个表变量作为参数传递的新语法觉得很不错。
首先建立一张测试表:
CREATE
TABLE
[
dbo
]
.
[
us
]
(
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ name ] [ varchar ] ( 50 ) NULL ,
[ pass ] [ varchar ] ( 50 ) NULL ,
CONSTRAINT [ PK_us ] PRIMARY KEY CLUSTERED
(
[ id ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ name ] [ varchar ] ( 50 ) NULL ,
[ pass ] [ varchar ] ( 50 ) NULL ,
CONSTRAINT [ PK_us ] PRIMARY KEY CLUSTERED
(
[ id ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
定义一个自定义表类型:
CREATE
TYPE
[
dbo
]
.
[
UsTable
]
AS
TABLE
(
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ NAME ] [ varchar ] ( 50 ) NULL
)
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ NAME ] [ varchar ] ( 50 ) NULL
)
然后我们写一个存储过程来传递这个类型,这里我们模拟用户批量添加默认密码为123
测试一下:
CREATE
PROC
[
dbo
]
.
[
addus
]
@tab [ UsTable ] READONLY
AS
BEGIN
INSERT INTO us(name,pass) SELECT name, ' 123 ' FROM @tab
END
@tab [ UsTable ] READONLY
AS
BEGIN
INSERT INTO us(name,pass) SELECT name, ' 123 ' FROM @tab
END
–
-
定义一个自定义表类型的变量
DECLARE @us UsTable
-- 插入模拟数据
INSERT INTO @us (name) VALUES ( ' dc ' )
INSERT INTO @us (name) VALUES ( ' ada ' )
INSERT INTO @us (name) VALUES ( ' dacey ' )
-- 执行存储过程
EXEC dbo.addus @tab = @us
DECLARE @us UsTable
-- 插入模拟数据
INSERT INTO @us (name) VALUES ( ' dc ' )
INSERT INTO @us (name) VALUES ( ' ada ' )
INSERT INTO @us (name) VALUES ( ' dacey ' )
-- 执行存储过程
EXEC dbo.addus @tab = @us
查询us表看下是否都插入进去了:
1 dc 123
2 ada 123
3 dacey 123