在SQL中,可以使用表值参数作为存储过程的输入参数,这样不用重复定义临时表或者表变量。
表值参数需要采用用户自定义的表类型来声明,所以在使用表值参数之前一定要先定义表类型
下面是敌营表类型的例子:
use Test
go
---------- create table type ----------
create type LocationTableType as table (
TrustId int
, TrustCode nvarchar(255)
)
go
定义好表类型LocationTableType之后,就可以声明表值变量了。如下:
declare @TrustInfo LocationTableType
insert into @TrustInfo
values ( 1 , 'AUTO' )
, ( 2 , 'UNK' )
, ( 3 , 'RMBS')
select * from @TrustInfo
下面创建一个存储过程,用表值变量传值
create procedure dbo.usp_GetTrustInfo
@TrustInfo LocationTableType READONLY
as
begin
set nocount on
select * from @TrustInfo
end
完整代码如下
use Test
go
---------- create table type ----------
create type LocationTableType as table (
TrustId int
, TrustCode nvarchar(255)
)
go
---------- example ----------
declare @TrustInfo LocationTableType
insert into @TrustInfo
values ( 1 , 'AUTO' )
, ( 2 , 'UNK' )
, ( 3 , 'RMBS')
select * from @TrustInfo
---------- example 1 ----------
if exists ( select 1
from sysobjects
where id = object_id(N'[dbo].[usp_GetTrustInfo]')
and objectproperty(id, N'IsProcedure') = 1 )
drop procedure dbo.usp_GetTrustInfo
go
create procedure dbo.usp_GetTrustInfo
@TrustInfo LocationTableType READONLY
as
begin
set nocount on
select * from @TrustInfo
end
exec dbo.usp_GetTrustInfo @TrustInfo