Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Contribuez SQL Server Discussion :

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


Sujet :

Contribuez SQL Server

  1. #1
    Membre éprouvé
    [2005] Créer une procédure avec pour paramètre une table
    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.
    Most Valued Pas mvp

  2. #2
    Membre régulier
    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?
    Bruno Petit

  3. #3
    Membre éprouvé
    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


    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 :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


    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 :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


    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.
    Most Valued Pas mvp

###raw>template_hook.ano_emploi###