Sqlserver表值类型和表变量

表类型可以用在存储过程中，用于批量增加

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,

??? [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

declare? @mtTable? table(

??? id int,

??? [name]varchar(10)

)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!