Skip to content

Table Type

November 14, 2022

CREATE TYPE [dbo].[AAMPRD_tipoTabella] AS TABLE(
	[AAMPRDCLIE] [nvarchar](50) NOT NULL,
	[AAMPRDDITT] [nvarchar](50) NOT NULL,
	[AAMPRDPROF] [nvarchar](50) NOT NULL,
	[AAMPRDVOCE] [nvarchar](50) NOT NULL,
	[AAMPRDVISI] [nvarchar](1) NULL
)
GO

This sql script create an user defined Table Type in SQLserver. You can use an user table type in a stored procedure as a parameter

ALTER PROCEDURE [dbo].[AAMPRD_aggiornaVoci] 
	@tabella1 AAMPRD_tipoTabella READONLY
AS

	DELETE AAMPRD 
	FROM AAMPRD INNER JOIN @tabella1 T1 ON AAMPRD.AAMPRDCLIE=T1.AAMPRDCLIE 
		AND AAMPRD.AAMPRDDITT=T1.AAMPRDDITT 
		AND AAMPRD.AAMPRDPROF=T1.AAMPRDPROF 
		AND AAMPRD.AAMPRDVOCE=T1.AAMPRDVOCE

	INSERT INTO AAMPRD(AAMPRDCLIE, AAMPRDDITT, AAMPRDPROF, AAMPRDVOCE, AAMPRDVISI)
	SELECT AAMPRDCLIE, AAMPRDDITT, AAMPRDPROF, AAMPRDVOCE, AAMPRDVISI
	FROM @tabella1

You can declare a variable with table type created: the attribute READONLY is mandatory.

In .Net you can pass the correct value to the table type parameter using a SQLparameter ,i.e. assigning directly a datatable to the value of SQLparameter

You can also test your table in this way

DECLARE @t1 as AAMPRD_tipoTabella

INSERT @t1(AAMPRDCLIE, AAMPRDDITT, AAMPRDPROF, AAMPRDVOCE, AAMPRDVISI)
VALUES ('aaa1', 'aaa1', 'prof1', 'v1', 'S'), 
      ('bbb1', 'bbb1', 'prof1', 'v1', 'S'), 
      ('ccc1', 'ccc1', 'prof1', 'v1', 'S')

SELECT * FROM @t1
Leave a Comment

Leave a comment