Comment affecter un résultat select à une variable?

Comment stocker une valeur de champ sélectionnée dans une variable 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 Server 2005 qui fait ce qui suit:

  1. obtient la liste des pièces d'identité des factures de la table de facturation et des magasins au curseur
  2. Fetch invoice id from curseur -> tmp_key variable
  3. pour chaque tmp_key trouver le numéro d'identification de la personne-ressource principale du client de la table client
  4. met à jour la clé de contact client avec l'id de contact principal
  5. fermer 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 PrimaryContactKey et l'utiliser à nouveau dans la clause set de la déclaration de mise à jour suivante? Est-ce que je crée une variable curseur ou juste une autre variable locale avec un type int?

67
demandé sur Tshepang 2009-04-30 20:08:33

5 réponses 

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

EDIT:

Cette question a obtenu beaucoup plus de traction que je l'aurais attendu. Notez bien que je ne préconise pas l'utilisation du curseur dans ma réponse, mais plutôt de montrer comment attribuer la valeur fondée sur la question.

41
répondu squillman 2017-07-18 15:39:24

j'ai juste eu le même problème et... 

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

ou même plus court: 

declare @userId uniqueidentifier
SELECT TOP 1 @userId = UserId FROM aspnet_Users
83
répondu Pawel G. 2015-02-24 17:07:05

Essayez Cette 

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éclareriez cette variable en dehors de votre boucle comme une simple variable TSQL standard.

je dois également noter que c'est la façon dont vous le feriez pour n'importe quel type de sélectionner dans une variable, pas seulement en traitant avec les curseurs.

18
répondu TheTXI 2009-04-30 16:20:31

Pourquoi avez-vous besoin d'un curseur? Votre segment entier de code peut être remplacé par ceci, qui s'exécute 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'
13
répondu GilaMonster 2009-05-01 19:08:46

pour assigner une variable en toute sécurité, vous devez utiliser la déclaration SET-SELECT: 

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

assurez-vous que vous avez à la fois un début et une fin entre parenthèses!

la raison pour laquelle la version de SET-SELECT est la façon la plus sûre de définir une variable est double.

1. La sélection retourne plusieurs postes

que se passe-t-il si la sélection suivante aboutit à 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 last post dans le résultat.

en fait @PrimaryContactKey se verra attribuer une valeur par poste dans le résultat, donc il contiendra la valeur du dernier poste que la SELECT-command traitait.

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

avec une instruction SET-SELECT votre variable sera définie à null .

2. La sélection retourne pas de messages

que se passe-t-il lorsque vous utilisez la deuxième version du code, si votre select ne renvoie aucun résultat?

dans un contrairement à ce que vous pouvez croire la valeur de la variable ne sera pas null - il conservera sa valeur précédente!

c'est parce que, comme indiqué ci - dessus, SQL assignera 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 pas de postes. Ainsi, la variable aura toujours la valeur qu'elle avait avant que vous n'exécutiez l'instruction.

avec L'instruction SET-SELECT la valeur sera null .

voir aussi: définir par rapport à choisir lors de l'attribution des variables?

9
répondu Erk 2017-05-23 11:47:15