表值函数中调用自定义表类型Demo

USE tempdb
GO
IF OBJECT_ID('dbo.Fun_Test') IS NOT NULL
	DROP FUNCTION dbo.Fun_Test
GO
IF EXISTS(SELECT 1 FROM sys.types WHERE name='type_table')
	DROP TYPE dbo.type_table
GO
CREATE TYPE dbo.type_table AS TABLE 
(
	c1 INT,
	c2 VARCHAR(10)
)
GO
IF OBJECT_ID('dbo.Fun_Test') IS NOT NULL
	DROP FUNCTION dbo.Fun_Test
GO
CREATE FUNCTION dbo.Fun_Test
(	
	@p1 dbo.type_table READONLY
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT * FROM @p1
)
GO
----------- 以上为创建自定义类型/表值函数 示例 ------------

DECLARE @p1 dbo.type_table
INSERT INTO @p1
SELECT 1,'a'
UNION ALL
SELECT 2,'b'
---- 插入数据到自定义类型 ----

-- 表值函数传入 自定义表类型 查询 --
SELECT * FROM dbo.Fun_Test(@p1)
/*
c1	c2
1	a
2	b
*/