[2005] Créer une procédure avec pour paramètre une table

Version imprimable

Depuis la version 2008 de SQL Server 2008, il est possible de passer comme argument à une fonction ou une procédure, une table définie par l'utilisateur.
Cela est une réponse à l'attente de nombreux développeurs.

Pour ceux qui, comme moi, n'ont pas une version 2008 voici ma solution alternative au problème en ce qui concerne les procédures.

Qautres choses importantes seront à noter.
1. Nous ne définirons pas de procédure, lancer le traitement que nous allons définir se fera par des opérations d'insertion (si, si !).
2. Nous n'aurons pas la possibilité d'employer autre chose qu'une table unique pour renseigner des données à notre traitement.
Vous n'aurez donc pas une table et plusieurs paramètres, vous aurez une table, point final.
3. Le traitement ne pourra pas servir à renvoyer des données.
4. Des traitements différents sur des tables de même structures nécessiteront chacun la définition d'une view propre, même s'il ces view sont parfaitement identique (à l'exception du nom, bien sûr) dans leur définition.

Comment faire ?

Vous devez tous d'abors définir une view qui reprendra la structure de la table utilisée pour le traitement.
Pour la définition de cette view, je vous recommande de définir des colonnes renvoyant des valeurs NULL castées dans le type de la colonne car mettre une valeur non NULL n'aurait pas beaucoup de sens et pourrait semer la confusion de la personne qui relira votre code.
Code:

1 2 3 4 5 CREATE VIEW vWORK_test AS SELECT CAST(NULL AS BIGINT) AS Id
Ma table n'a ici qu'un seul paramètre, Id qui est du type BIGINT.

Si nous essayons maintenant une insertion sur cette table, nous aurons droit à une belle erreur qui ne devra pourtant pas nous effrayer.
Code:

1 2 3 4 5 INSERT vWORK_test(id) SELECT 5 UNION SELECT 10
Citation:

Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'vWORK_test' failed because it contains a derived or constant field.

Mais... Même pas peur !
Cette erreur est la conclusion logique d'une tentative d'insertion dans une colonne constante sur une view. Cette erreur ne sera plus dans la suite de la solution.

Nous allons maintenant définir notre traitement dans un trigger qui sera appellé à la place du comportement d'insertion par défaut (qui nous a vallu ce beau message d'erreur).
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 CREATE TRIGGER vWORK_test_IOINSERT ON vWORK_test INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON DECLARE @tTralala TABLE ( X BIGINT , miX FLOAT ) INSERT @tTralala ( X , miX ) SELECT Id , Id / 2.0 FROM inserted END
Dans notre trigger, nous avons donc à disposition du traitement cet accès que nous désirions à une table en tant que "paramètre", cette table est inserted.

Ce trigger ci n'a par ailleurs aucunne utilisé sinon à servir d'exemple.

Vous pouvez testez.
Code:

1 2 3 4 5 INSERT vWORK_test(id) SELECT 5 UNION SELECT 10
Et cette fois tout se passera bien.

Bon amusement.

Serge.

30/09/2009, 14h22
PickEpique

Je ne comprend cette astuce si cela en est une, quele utilité dans le cadre du passage de table en paramètre d'une procedure à l'autre ou même de quelque chosse d'approchant, c'est le trigger qui fait le lien antre les deux procedure? tu aurait pas un exemple à peine plus concret s'il te plait?

L'intérêt de faire des opérations par table plutôt que ligne à ligne est qu'une optimisation globale peut être faite par le SGDB (SQL Server) dans le premier cas. Cette optimisation peut améliorer considérablement les performances lorsqu'on parle d'un grand nombre de lignes.

Je vais donner un exemple complet.

Si tu veux logger (dans dbo.tLogCommandes) les opérations faites par différents utilisateur (dbo.tUsers) sur une table (disons tCommandes), voici comment tu pourrais définir des "méthodes" d'insertion, suppresion, update... (oui, je t'en mets 3 pour le prix d'une ;))

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
CREATE TABLE dbo.tCommandes
(
	id INT IDENTITY
	, data NVARCHAR(100)
 
	, CONSTRAINT PK_tCommandes PRIMARY KEY(id)
)
GO
 
CREATE TABLE tUsers
(
	id INT IDENTITY
	, data NVARCHAR(100)
 
	, CONSTRAINT PK_tUsers PRIMARY KEY(id)
)
GO
 
CREATE TABLE dbo.tLogCommandes
(
	id INT IDENTITY 
	, userID INT
	, operation VARCHAR(10)
	, commandeID INT
 
	, CONSTRAINT PK_tLogCommandes PRIMARY KEY(id)
	, CONSTRAINT CHK_operation CHECK (operation IN ('insert', 'update', 'delete'))
)
GO

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE VIEW dbo.vINSUPD_tCommandes
AS
	SELECT *
		, CAST(NULL AS INT) AS userID
		, CAST(NULL AS UNIQUEIDENTIFIER) AS DATA_ID
	FROM dbo.tCommandes
GO
 
CREATE VIEW dbo.vDELETE_tCommandes
AS
	SELECT *
		, CAST(NULL AS INT) AS userID
	FROM dbo.tCommandes
GO

Et les triggers, dans cet ordere pours les opérations INSERT, UPDATE, DELETE.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
CREATE TRIGGER dbo.vINSUPD_tCommandes_IOINSERT
ON dbo.vINSUPD_tCommandes
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @InsertedID TABLE
	(
		Id BIGINT
	)
 
	-- INSERT tCommandes: Dummy entries
	INSERT tCommandes
	(
		data
	)
	OUTPUT inserted.Id INTO @InsertedID(Id)
	SELECT
		data
	FROM inserted
 
	-- @Link
	DECLARE @Link TABLE
	(
		Id BIGINT
		, DATA_ID UNIQUEIDENTIFIER
	)
 
	INSERT @Link 
	(
		DATA_ID
	)
	SELECT
		I.DATA_ID
	FROM inserted AS I;
 
	-- Random assignement of real id to virtual id
	WITH Assignment
	(
		DATA_ID
		, Id
	)
	AS
	(
		SELECT 
			L.DATA_ID
			, IID.Id
		-- Match Row - Row
		FROM (
			SELECT
				L.DATA_ID
				, ROW_NUMBER() OVER(ORDER BY L.DATA_ID ASC) AS Row
			FROM @Link AS L
		) AS L
		INNER JOIN (
			SELECT
				IID.Id
				, ROW_NUMBER() OVER(ORDER BY IID.Id ASC) As Row
			FROM @InsertedID AS IID
		) AS IID ON (
			IID.Row = L.Row
		)	)
 
	UPDATE @Link 
	SET Id = A.Id
	FROM Assignment AS A
	WHERE [@Link].DATA_ID = A.DATA_ID
 
	-- UPDATE tCommandes
	UPDATE dbo.tCommandes
	SET
		data = I.data
	FROM @Link AS L
	INNER JOIN inserted  AS I ON (
		I.DATA_ID = L.DATA_ID
	)
	WHERE tCommandes.Id = L.Id
 
	-- INSERT tLogCommandes
	INSERT dbo.tLogCommandes
	(
		userID
		, operation
		, commandeID
	)
	SELECT
		I.userID
		, 'insert'
		, L.Id
	FROM @Link AS L
	INNER JOIN inserted  AS I ON (
		I.DATA_ID = L.DATA_ID
	)
END
GO

Testons...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
-- Simple insertion
INSERT dbo.vINSUPD_tCommandes
(
	data
	,userID
	,DATA_ID
)
VALUES
(
	'blabla' -- data for insertion into tCommandes
	, 5 -- user 5 for loggin
	, NEWID() -- required for linking
)
GO
 
-- Multiple insertions
INSERT dbo.vINSUPD_tCommandes
(
	data
	,userID
	,DATA_ID
)
SELECT 'blabla 2', 5, NEWID()
UNION SELECT 'blibli', 2, NEWID()
UNION SELECT 'blibli 2', 2, NEWID()
UNION SELECT 'blibli 3', 2, NEWID()
UNION SELECT 'blabla 3', 5, NEWID()
GO
 
SELECT *
FROM dbo.tCommandes
 
SELECT *
from dbo.tLogCommandes

Citation:

Envoyé par dbo.tCommandes

38 blabla
39 blibli 3
40 blibli
41 blibli 2
42 blabla 3
43 blabla 2

Citation:

Envoyé par dbo.tLogCommandes

14 5 insert 38
15 5 insert 43
16 5 insert 42
17 2 insert 40
18 2 insert 41
19 2 insert 39

C'est bien le résultat désiré.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
CREATE TRIGGER dbo.vINSUPD_tCommandes_IOUPDATE
ON dbo.vINSUPD_tCommandes
INSTEAD OF UPDATE
AS
BEGIN
	SET NOCOUNT ON;
 
	-- UPDATE tCommandes
	UPDATE dbo.tCommandes
	SET
		data = I.data
	FROM inserted AS I
	WHERE I.Id = tCommandes.Id
 
	-- INSERT tLogCommandes
	INSERT dbo.tLogCommandes
	(
		userID
		, operation
		, commandeID
	)
	SELECT
		I.userID
		, 'update'
		, I.Id
	FROM inserted AS I
END
GO

Testons...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
UPDATE vINSUPD_tCommandes
SET
	data = 'bloblo'
	,userID = 7 -- -- user 7 for loggin
WHERE data LIKE 'bli%'
GO
 
SELECT *
FROM dbo.tCommandes
 
SELECT *
FROM dbo.tLogCommandes

Citation:

Envoyé par dbo.tCommandes

38 blabla
39 bloblo
40 bloblo
41 bloblo
42 blabla 3
43 blabla 2

Citation:

Envoyé par dbo.tLogCommandes

14 5 insert 38
15 5 insert 43
16 5 insert 42
17 2 insert 40
18 2 insert 41
19 2 insert 39
20 7 update 39
21 7 update 40
22 7 update 41

C'est bien le résultat désiré.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TRIGGER dbo.vDELETE_tCommandes_IOUPDATE
ON dbo.vDELETE_tCommandes
INSTEAD OF UPDATE
AS
BEGIN
	SET NOCOUNT ON;

	-- DELETE tCommandes
	DELETE dbo.tCommandes
	FROM inserted AS I
	WHERE I.Id = tCommandes.Id

	-- INSERT tLogCommandes
	INSERT dbo.tLogCommandes
	(
		userID
		, operation
		, commandeID
	)
	SELECT
		I.userID
		, 'delete'
		, I.Id
	FROM inserted AS I
END
GO

Testons...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
UPDATE dbo.vDELETE_tCommandes
SET
	userID = 2 -- -- user 2 for loggin
WHERE data LIKE 'blo%'
GO

SELECT *
FROM dbo.tCommandes

SELECT *
FROM dbo.tLogCommandes

Citation:

Envoyé par dbo.tCommandes

38 blabla
42 blabla 3
43 blabla 2

Citation:

Envoyé par dbo.tLogCommandes

14 5 insert 38
15 5 insert 39
16 5 insert 43
17 2 insert 42
18 2 insert 40
19 2 insert 41
20 7 update 40
21 7 update 41
22 7 update 42
23 2 delete 40
24 2 delete 41
25 2 delete 42

C'est bien le résultat désiré.

Voilà, j'espère que ça aidera.