CODEWITHSUNDEEP
sqlsql-server
Jean Moulin
Jean Moulin

Reputation: 49

How to fix "Subquery returns multiple values"?

i'm trying to write a stored procedure who have to verify if name Dont exist in my database and if name does not exist i create a new line with name and pass.

i have this error : Subquery returns multiple values.

    this is my code :

    if((select Nom from prorio where Nom = @p_nom) = '')

    insert into Prorio (Nom, mdp) VALUES (@p_nom, @p_mdp);
    else if((select Nom from prorio where Nom = @p_nom)!='')
    return 2;

Upvotes: 0

Views: 175

Answers (1)

Thom A
Thom A

Reputation: 95579

Seems like the logic you are after is:

IF (@p_nom = '' AND EXISTS(SELECT 1 FROM Prorio WHERE Nom = '')) BEGIN

    INSERT INTO dbo.Prorio (Nom,mdp)
    VALUES(@p_nom,@p_mdp);

END
ELSE IF @p_nom != '' BEGIN 

    SET @OutputParam = 2;
END;

You'll need to add @OutputParam to your SP's definition (as an OUTPUT Parameter) and probably give it a "better" name.

This does seem like an XY problem though. The logic you have means that if the person has a blank ('') name, they can only be inserted if other rows in the table already have a blank ('') name. That seems like a design flaw.

Upvotes: 2

Related Questions