Comment affecter le résultat d'une sélection à une variable ?

Question

Comment stocker la valeur d'un champ sélectionné dans une variable à partir d'une requête et l'utiliser dans une instruction de mise à jour ?

Voici ma procédure :

J'écris une procédure stockée T-SQL pour le serveur SQL 2005 qui fait ce qui suit :

1. récupère la liste des numéros d'identification des factures dans la table des factures et la stocke dans le curseur.
2. Récupérer le numéro de facture du curseur -> variable tmp_key
3. foreach tmp_key trouve l'identifiant du contact primaire du client de la table customer
4. met à jour la clé de contact du client avec l'identifiant du contact primaire
5. fermer le curseur

Voici mon code :

DECLARE @tmp_key int
DECLARE @get_invckey cursor 

set @get_invckey = CURSOR FOR 
    select invckey from tarinvoice where confirmtocntctkey is null and tranno like '%115876'

OPEN @get_invckey 

FETCH NEXT FROM @get_invckey into @tmp_key

WHILE (@@FETCH_STATUS = 0) 
BEGIN 
    SELECT c.PrimaryCntctKey as PrimaryContactKey
    from tarcustomer c, tarinvoice i
    where i.custkey = c.custkey and i.invckey = @tmp_key

    UPDATE tarinvoice set confirmtocntctkey = PrimaryContactKey where invckey = @tmp_key
    FETCH NEXT FROM @get_invckey INTO @tmp_key
END 

CLOSE @get_invckey
DEALLOCATE @get_invckey

Comment puis-je stocker la PrimaryContactKey et la réutiliser dans la clause set de l'instruction de mise à jour suivante ? Dois-je créer une variable curseur ou simplement une autre variable locale de type int ?

Answer 1

J'ai eu le même problème et...

declare @userId uniqueidentifier
set @userId = (select top 1 UserId from aspnet_Users)

ou même plus court (2ème ligne) :

SELECT TOP 1 @userId = UserId FROM aspnet_Users

Answer 2

DECLARE @tmp_key int
DECLARE @get_invckey cursor 

SET @get_invckey = CURSOR FOR 
    SELECT invckey FROM tarinvoice WHERE confirmtocntctkey IS NULL AND tranno LIKE '%115876'

OPEN @get_invckey 

FETCH NEXT FROM @get_invckey INTO @tmp_key

DECLARE @PrimaryContactKey int --or whatever datatype it is

WHILE (@@FETCH_STATUS = 0) 
BEGIN 
    SELECT @PrimaryContactKey=c.PrimaryCntctKey
    FROM tarcustomer c, tarinvoice i
    WHERE i.custkey = c.custkey AND i.invckey = @tmp_key

    UPDATE tarinvoice SET confirmtocntctkey = @PrimaryContactKey WHERE invckey = @tmp_key
    FETCH NEXT FROM @get_invckey INTO @tmp_key
END 

CLOSE @get_invckey
DEALLOCATE @get_invckey

Answer 3

Essayez ceci

SELECT @PrimaryContactKey = c.PrimaryCntctKey
FROM tarcustomer c, tarinvoice i
WHERE i.custkey = c.custkey 
    AND i.invckey = @tmp_key

UPDATE tarinvoice SET confirmtocntctkey = @PrimaryContactKey 
WHERE invckey = @tmp_key
FETCH NEXT FROM @get_invckey INTO @tmp_key

Vous déclarerez cette variable en dehors de votre boucle comme une simple variable TSQL standard.

Je dois également noter que c'est ainsi que l'on procède pour tout type de sélection dans une variable, et pas seulement lorsqu'il s'agit de curseurs.

Answer 4

Pour assigner une variable en toute sécurité, vous devez utiliser l'instruction SET-SELECT :

SET @PrimaryContactKey = (SELECT c.PrimaryCntctKey
    FROM tarcustomer c, tarinvoice i
    WHERE i.custkey = c.custkey 
    AND i.invckey = @tmp_key)

Assurez-vous d'avoir une parenthèse de départ et une parenthèse d'arrivée !

La raison pour laquelle la version SET-SELECT est le moyen le plus sûr de définir une variable est double.

1. Le SELECT renvoie plusieurs messages

Que se passe-t-il si la sélection suivante donne lieu à plusieurs messages ?

SELECT @PrimaryContactKey = c.PrimaryCntctKey
FROM tarcustomer c, tarinvoice i
WHERE i.custkey = c.custkey 
    AND i.invckey = @tmp_key

@PrimaryContactKey se verra attribuer la valeur de l'élément dernier poste dans le résultat.

En effet @PrimaryContactKey se verra attribuer une valeur par message dans le résultat, et contiendra donc la valeur du dernier message traité par la commande SELECT.

Le "dernier" message est déterminé par les index groupés ou, si aucun index groupé n'est utilisé ou si la clé primaire est groupée, le "dernier" message sera le message le plus récemment ajouté. Ce comportement pourrait, dans le pire des cas, être modifié à chaque fois que l'indexation de la table est modifiée.

Avec une instruction SET-SELECT, votre variable sera définie comme suit null .

2. Le SELECT ne renvoie aucun message

Que se passe-t-il, en utilisant la deuxième version du code, si votre sélection ne renvoie aucun résultat ?

Contrairement à ce que vous pourriez croire, la valeur de la variable ne sera pas nulle - il conservera sa valeur précédente !

C'est parce que, comme indiqué ci-dessus, SQL va attribuer une valeur à la variable une fois par poste - ce qui signifie qu'il ne fera rien avec la variable si le résultat ne contient aucun message. Ainsi, la variable aura toujours la valeur qu'elle avait avant l'exécution de l'instruction.

Avec l'instruction SET-SELECT la valeur sera null .

Voir aussi : T-SQL - SET ou SELECT pour l'attribution de variables ?

Answer 5

Pourquoi avez-vous besoin d'un curseur ? Votre segment de code entier peut être remplacé par ceci, qui s'exécutera beaucoup plus rapidement sur un grand nombre de lignes.

UPDATE tarinvoice set confirmtocntctkey = PrimaryCntctKey 
FROM tarinvoice INNER JOIN tarcustomer ON tarinvoice.custkey = tarcustomer.custkey
WHERE confirmtocntctkey is null and tranno like '%115876'