Table Type
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