表类型可以用在存储过程中,用于批量增加
[sql]
use Customer
GO
if exists(select 1 from systypes where name='Type_TB_Biz_Account')
drop typeType_TB_Biz_Account
GO
----------------------------------------------------------
-- TypeName: Type_TB_Biz_Account
----------------------------------------------------------
CREATE TYPE Type_TB_Biz_Account AS TABLE
(
Account int,
Balance decimal(19,2),
Credit decimal(19,2),
[Readonly] int
);
GO
if exists(select 1 from sysobjects where name='SP_Biz_Account_BatchUpdateBalance')
drop proc SP_Biz_Account_BatchUpdateBalance
GO
----------------------------------------------------------
-- ProcedureName: 批量更新账户余额、信用额
----------------------------------------------------------
create proc SP_Biz_Account_BatchUpdateBalance
@TBAccount Type_TB_Biz_Account readonly
as
update TB_Biz_Account
set Balance=New.Balance,Credit=New.Credit
FROM dbo.TB_Biz_Account as Cur
INNER JOIN @TBAccount AS New ON Cur.Account = New.Account;
return @@rowcount
GO
sqlserver2005好像不支持表类型,不过支持表变量
表变量定义:
[sql]
declare @mtTable table(
id int,
[name]varchar(10)
)