CODEWITHSUNDEEP
sqlsql-server-2008stored-proceduressql-server-2012
Slim Bou
Slim Bou

Reputation: 23

SQL ERROR: This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

I got this error in SQL Server 2012:

 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , > , >= or when the subquery is used as an expression.

This is my script:

CREATE PROCEDURE dbo.Update_F_ARTCLIENT

@PK varchar(19)
,@AR_Ref varchar(19)
,@AC_Categorie smallint
,@AC_PrixVen numeric(24,6)
,@AC_Coef numeric(24,6)
,@AC_PrixTTC smallint
,@AC_Remise numeric(24,6)

AS
BEGIN

SET NOCOUNT ON;

UPDATE [dbo].[F_ARTCLIENT]

SET

[AR_Ref] = @AR_Ref
,[AC_Categorie] = @AC_Categorie 
,[AC_PrixVen] = @AC_PrixVen
,[AC_Coef]= @AC_Coef
,[AC_PrixTTC] = @AC_PrixTTC
,[AC_Remise] = @AC_Remise

WHERE (SELECT CONCAT([AR_Ref], [AC_Categorie]) as PK FROM [dbo].[F_ARTCLIENT])= @PK

END

Upvotes: 0

Views: 273

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The problem is clearly in the where clause. Presumably, you want to do the update where this condition is true. One pay is to change the logic to an in:

WHERE @pk in (SELECT CONCAT([AR_Ref], [AC_Categorie]) as PK FROM [dbo].[F_ARTCLIENT])

However, I notice that the table in the subquery is the same as the data being updated. Do you really just mean this:

WHERE @pk = CONCAT([AR_Ref], [AC_Categorie])

That is, I don't think the subquery is necessary.

Upvotes: 0

Adrian Nasui
Adrian Nasui

Reputation: 1095

Try modifying your WHERE condition to be:

WHERE CONCAT([AR_Ref], [AC_Categorie]) = @PK

Upvotes: 0

kiks73
kiks73

Reputation: 3758

As the error description reports, your subquery return more than a value. You can avoid this changing your subquery in this way:

(SELECT TOP 1 CONCAT([AR_Ref], [AC_Categorie]) as PK FROM [dbo].[F_ARTCLIENT])= @PK)

Upvotes: 2

Related Questions