存储过程 表变量

SQL code
--建立一个表值参数类型
CREATE TYPE LocationTableType AS TABLE
    ( LocationName VARCHAR(50)
    , CostRate INT )
GO

--使用表值参数的存储过程
create proc pr_test(@p LocationTableType readonly)
as
begin
    declare @name varchar(50)
    declare @Rate int
    select @name=ISNULL(@Name+',','')+LocationName,
        @Rate=isnull(@Rate,0)+CostRate
    from @p
    select @name as name ,@rate as rate
end

GO

--调用
declare @t LocationTableType
insert @t select 'a',1 union all select 'b',2 union all select 'c',3
exec pr_test @t

GO
--结果
name                                                      rate
-------------------------------------------------- -----------
a,b,c                                                        6

(1 行受影响)

--使用表值参数的标量函数
create function fn_test(@p LocationTableType readonly)
returns
VARCHAR(50)
as
begin
    declare @name varchar(50)
    select @name=ISNULL(@Name+',','')+LocationName
    from @p
    return @Name
end

GO

--调用
declare @t LocationTableType
insert @t select 'a',1 union all select 'b',2 union all select 'c',3
select dbo.fn_test1(@t)

GO
--结果
--------------------------------------------------
a,b,c

(1 行受影响)

--使用表值参数的表值函数
create function fn_test1(@p LocationTableType readonly)
returns
@r table (name varchar(50),Rate int)
as
begin
    declare @name varchar(50)
    declare @Rate int
    select @name=ISNULL(@Name+',','')+LocationName,
        @Rate=isnull(@Rate,0)+CostRate
    from @p
    insert @r values(@name,@rate)
    return
end

GO

create function fn_test2(@p LocationTableType readonly)
returns
table
as
    return select * from @p

GO