Mise à jour de plusieurs lignes dans SQL Server avec la clause IN

Question

J'ai une procédure stockée comme ceci :

CREATE PROCEDURE Resume 
    @UserID nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;

    update Candidate set Approved = 'y' where UserId_I in (@UserID)
END
GO

En cela, le @UserID est au format chaîne de caractères. J'envoie la valeur du paramètre @UserID es 1,2,3 du code en tant que chaîne.

J'ai obtenu l'erreur suivante

La conversion a échoué lors de la conversion de la valeur nvarchar '1,2,3' en type de données int.

Veuillez résoudre ce problème

Answer 1

Vous pouvez le faire de plusieurs façons. Vous pouvez soit définir un type de table et transmettre la liste des identifiants d'utilisateur via ce type de table (voir cette question) : INSERT avec LIST dans une procédure stockée ).

Vous pouvez également séparer la chaîne de caractères au niveau des virgules dans un fichier WHILE et ajouter les IDs à un tableau temporaire. Ensuite, vous pouvez utiliser ... IN (SELECT UID FROM @tempTable) . Algorithme brut :

DECLARE @tempTable TABLE (UID INT)

WHILE ([Length of @UserID > 0])
BEGIN
    IF ([@UserID contains comma?])
    BEGIN
        [GET STRING UNTIL COMMA]
        [CONVERT TO INT AND INSERT INTO @tempTable]
        [REMOVE STRING AND COMMA FROM @UserID]
    END ELSE
    BEGIN
        [CONVERT @UserID TO INT AND INSERT INTO @tempTable]
        SET @UserID = ''
    END
END

---

Vous pouvez également utiliser le EXEC mais j'ai tendance à ne pas utiliser EXEC avec des paramètres passés depuis le monde extérieur - vous ouvrez votre code aux injections SQL. La vérification minimale que j'effectuerais est de savoir si le paramètre @UserID ne contient que des chiffres, des virgules et des espaces.

Answer 2

Veuillez mettre à jour en utilisant EXECUTE (Transact-SQL) commandement.

CREATE PROCEDURE Resume 
    -- Add the parameters for the stored procedure here
    @UserID nvarchar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    exec ('update Candidate set Approved=''y'' where UserId_I in ('+@UserID+')')
END
GO

Answer 3

Essayez d'utiliser une table temporaire qui a de meilleures performances que la sous-requête.

DECLARE @userIds NVARCHAR(MAX)
SET @userIds ='1,2,3,4,5,6'

CREATE table #TempUser 
(
    userId Int
) 
DECLARE @SplitOn VARCHAR(1)
SET @SplitOn = ','

While (Charindex(@SplitOn,@userIds)>0)
Begin
    Insert Into #TempUser (userId)
    Select 
        userId = ltrim(rtrim(Substring(@userIds ,1,Charindex(@SplitOn,@userIds )-1)))
    Set @userIds = Substring(@userIds,Charindex(@SplitOn,@userIds)+1,len(@userIds))
End

Insert Into #TempUser (userId)
Select Data = ltrim(rtrim(@userIds))

SELECT * FROM #TempUser

Vous pouvez maintenant mettre à jour vos enregistrements en utilisant cette table temporaire.

Answer 4

Pour un paramètre @UserID de fractionnement, vous pouvez utiliser la fonction Méthode XQuery value()

CREATE PROCEDURE Resume 
  @UserID nvarchar(100)
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE Candidate 
  SET Approved = 'y' 
  WHERE UserId_I IN 
    (SELECT Split.a.value('.', 'int')
     FROM (SELECT CAST ('<M>' + REPLACE(@UserID , ',', '</M><M>') + '</M>' AS XML) AS val    
           ) AS A CROSS APPLY val.nodes('/M') AS Split(a)
     )
END
GO

Voir la démo sur SQLFiddle

Answer 5

L'erreur se produit parce que vous ne pouvez pas utiliser une chaîne (@UserID) comme opérateur de droite de l'opérateur IN. L'opérateur IN nécessite un SET comme opérateur de droite - dans ce cas, vous aurez besoin d'un ensemble d'ints pour savoir si UserId_I fait partie de cet ensemble.

Il y a deux possibilités : soit vous utilisez un paramètre de type tableau-valeur pour le paramètre @UserID, soit vous écrivez une fonction qui convertit/split la chaîne @UserID en un tableau/ensemble d'ints, auquel cas vous obtiendrez quelque chose comme.. :

...WHERE @UserId_I IN dbo.ParseIntSet(@UserId);