[SQL Server] Créer un type personnalisé de type Table

-- =============================================

-- Author:      KHARROUBI Zouhaier

-- Create date: 22/05/2018

-- Description: Créer un type personnalisé

-- =============================================

IF EXISTS(SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TYPE_ARTICLE' AND ss.name = N'dbo')

      BEGIN

            DROP TYPE dbo.TYPE_ARTICLE

      END

CREATE TYPE dbo.TYPE_ARTICLE AS TABLE(

      [id] [int] IDENTITY(1,1) NOT NULL,

      [AR_Ref] [varchar](19)       NULL,

      [AR_Qte] [int]                     NULL,

      [AR_DateCreate] date         NULL

)

GO

DECLARE @MaTable AS dbo.TYPE_ARTICLE

INSERT INTO @MaTable(AR_Ref,AR_Qte,AR_DateCreate)

SELECT 'A1',1,'20180501'

UNION

SELECT 'A2',2,'20180502'

UNION

SELECT 'A3',3,'20180503'

SELECT * from @MaTable

IF EXISTS(SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TYPE_ARTICLE' AND ss.name = N'dbo')

      BEGIN

            DROP TYPE TYPE_ARTICLE

      END