User Defined Table Type

--How to use USER DEFINED TABLE TYPE

--Create table type

CREATE TYPE [dbo].[udtId] AS TABLE(

       [Id] [int] NOT NULL,

       PRIMARY KEY CLUSTERED

(

       [Id] ASC

)

--Create table Customer and insert data

CREATE TABLE [dbo].[customer](

       [ID] [int] NOT NULL,

       [Name] [varchar](50) NULL,

       [Add] [nvarchar](50) NULL,

       [b] [bit] NULL

) ON [PRIMARY]

GO

INSERT INTO [dbo].[customer]

           ([ID]

           ,[Name]

           ,[Add]

           ,[b])

     VALUES

           (1,'HYD','UK',1),

           (2,'Wembley','London',1),

           (3,'HYT','USA',0),

           (43,'Cardiff','Wales',0)

GO

--Create Sproc using table datatype

CREATE PROC tblCustomer

@PlanIds udtId

AS

BEGIN

       Update dbo.customer SET Name = 'Pram' WHERE Id in (SELECT id FROM @PlanIds)

END

--Now Execute Store Proc

DECLARE @PlanIds udtId

INSERT INTO @PlanIds SELECT Id FROM customer

       EXEC tblCustomer @PlanIds = @PlanIds