存储过程表变量

by ·存储过程表变量已关闭评论

2008开始存错过程可以带表参数，不过限制只读。

CREATE TYPE [dbo].[MyTable] AS TABLE
02.(
03.[id] [int],
04.[value] [nvarchar](128)
05.)
06.GO
07. 
08.CREATE PROCEDURE [dbo].[Capitalize]
09.(
10.@myTable [dbo].[MyTable] READONLY
11.)
12.AS
13.BEGIN
14.DECLARE @myOutput [dbo].[MyTable]
15. 
16.INSERT INTO @myOutput([id], [value])
17.SELECT [id], UPPER([value]) FROM @myTable
18. 
19.SELECT [id], [value] FROM @myOutput
20.END
21.GO
22. 
23.DECLARE @myTable [dbo].[MyTable]
24.DECLARE @myOutput [dbo].[MyTable]
25. 
26.INSERT INTO @myTable([id], [value])
27.VALUES
28.(1, 'one'),
29.(2, 'two'),
30.(3, 'three')
31. 
32.INSERT INTO @myOutput([id], [value])
33.EXEC [dbo].[Capitalize] @myTable
34. 
35.SELECT * from @myOutput