[SQL] Table-value parameter Sample

CREATE TYPE TVP_STATISTICS AS TABLE(
 STAT_ID Int,
 STAT_NAME VARCHAR(50)
)
GO

CREATE PROCEDURE PROC_USE_TVP(
 @ID Int,
 @TABLE_PARAM AS TVP_STATISTICS READONLY    -- MUST BE READONLY AND NOT for OUTPUT
)

AS
BEGIN
 SET NOCOUNT ON;
 SET XACT_ABORT ON

 BEGIN TRAN

SELECT V.ID, V.NAME, TP.STAT_ID, TP.STAT_NAME
 FROM VIEW V
 INNER JOIN VIEW_STATISTICS VST ON V.ID = VST.VIEW_ID
 INNER JOIN @TABLE_PARAM TP ON TP.ID = VST.ID

COMMIT
RETURN

END
GO