Discussion :

[Sergejack]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Et cette fois tout se passera bien.


Bon amusement.

Serge.

[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?

[Sergejack]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

38 blabla
42 blabla 3
43 blabla 2

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.