0TXfeY1G

1use tempdb;
2go
3
4/* Create test table. */
5drop table if exists dbo.tvp_test_table;
6go
7create table dbo.tvp_test_table(
8	Id int not null identity(1,1),
9	val1 int not null,
10	val2 nvarchar(255) not null,
11	dt datetime2(0) not null,
12	constraint PK_tvp_test_table primary key clustered (Id)
13);
14go
15
16/* Create a table type. */  
17drop type if exists tvp_test_type;
18go
19create type tvp_test_type as table(
20	val1 int,
21	val2 nvarchar(255)
22);
23go  
24
25/* Create a procedure to receive data for the table-valued parameter. */  
26drop procedure if exists dbo.usp_insert_tvp_test_table;
27go
28create procedure dbo.usp_insert_tvp_test_table
29	@tvp_test_type_param tvp_test_type readonly
30as
31	set nocount on;
32	declare @dt datetime2(0) = cast(getdate() as datetime2(0));
33	insert into dbo.tvp_test_table(val1, val2, dt)
34	select val1, val2, @dt
35	from @tvp_test_type_param;
36go
37
38drop procedure if exists dbo.usp_select_from_test_table;
39go
40create procedure dbo.usp_select_from_test_table (@limit int = 10)
41as
42    set nocount on;
43    select top ( @limit ) Id, val1, val2
44    from dbo.tvp_test_table;
45    return 99;
46go
47
48/* Declare a variable that references the type. */  
49declare @tvp_test_type AS tvp_test_type;
50
51/* Add data to the table variable. */
52;with
53t1(n) as (select 0 union all select 0), -- 2
54t2(n) as (select t1.n from t1 cross join t1 as t11), -- 4
55t3(n) as (select t2.n from t2 cross join t2 as t22), -- 16
56t4(n) as (select t3.n from t3 cross join t3 as t33) -- 256
57insert into @tvp_test_type(val1, val2)
58select abs(checksum(newid())) % 1000, cast(newid() as nvarchar(255))
59from t4;
60
61/* Pass the table variable data to a stored procedure. */  
62exec dbo.usp_insert_tvp_test_table @tvp_test_type;  
63go
64
65exec dbo.usp_select_from_test_table;
66go
67
68drop procedure if exists dbo.usp_insert_tvp_test_table;
69go
70drop procedure if exists dbo.usp_select_from_test_table;
71go
72drop type if exists tvp_test_type;
73go
74drop table if exists dbo.tvp_test_table;
75go