不允许子查询，仅允许标量函数. [英] subqueries are not allowed only scalar fucntions are allowed.

问题描述

我遇到主题错误...

我不知道为什么

i am getting the subject error...

i don''t know why

ALTER PROCEDURE [dbo].[InsertOrder] 
@CustomerID varchar(50),
@OrderDate datetime,
@FirstName varchar(50),
@LastName varchar(50),
@Company varchar(50),
@Address varchar(50),
@Country varchar(50),
@City varchar(50),
@Province varchar(50),
@ZipCode varchar(50),
@Telephone varchar(50),
@Fax varchar(50),
@totalAmount varchar(50)


AS
BEGIN
	INSERT INTO ORDERS(CUSTOMERID,ORDERDATE,FirstName,LastName,Company,Address,Country,City,Province,ZipCode,Telephone,Fax,totalAmount,userid) 
	VALUES (@CustomerID,@OrderDate,@FirstName,@LastName,@Company,@Address,@Country,@City,@Province,@ZipCode,@Telephone,@Fax,@totalAmount,(select userid from users where CustomerID=@CustomerID))
	SELECT @@IDENTITY
END

推荐答案

在此处查看我对相同问题的回答:存储过程中的问题 [

See my answer to that exact same question here: problem with the store procedure[^]

Your subquery

select userid from users where CustomerID=@CustomerID

可以返回多个结果.那是问题.
例如，某个客户有3个用户，那么它将插入哪个用户ID?
确保您的子查询仅返回1个结果.我真的不能帮上忙，因为我不知道您的结构和要求.

但是可能的解决方案(不一定是正确的)可能是:

can return more than one result. That is the problem.
For example some customer has 3 users, so which userid will it insert?
Make sure your subquery returns only 1 result. I can''t really help with that since I don''t know your structure and requirements.

But a possible solution (not necissarily correct) could be:

select top 1 userid from users where CustomerID=@CustomerID -- Will always return just 1 value.

希望对您有所帮助:)

Hope it helps :)

首先将其放在变量上，而不要使用子查询

宣告@UserID int
SET @UserID =(从其中CustomerID = @ CustomerID的用户中选择用户ID)

Place it on a variable first instead of using subquery

DECLARE @UserID int
SET @UserID = (select userid from users where CustomerID=@CustomerID)

INSERT INTO ORDERS(CUSTOMERID,ORDERDATE,FirstName,LastName,Company,Address,Country,City,Province,ZipCode,Telephone,Fax,totalAmount,userid)
    VALUES (@CustomerID,@OrderDate,@FirstName,@LastName,@Company,@Address,@Country,@City,@Province,@ZipCode,@Telephone,@Fax,@totalAmount,@UserID)
    SELECT @@IDENTITY

问候，
爱德华(Eduard)

Regards,
Eduard

根据您的子查询:

从其中CustomerID = @ CustomerID的用户中选择用户ID)

"userid"有可能返回多个导致冲突的数据，这是我的建议:

1.从其中CustomerID = @ CustomerID
的用户中选择前1个用户ID -它仅返回1个值.

2.

based on your subquery:

select userid from users where CustomerID=@CustomerID)

there''s a possibility that the ''userid'' returns more than one data which cause to conflicts, here''s my suggestion about that:

1. select top 1 userid from users where CustomerID=@CustomerID
-it returns only 1 value.

2.

DECLARE @UserID int
SET @UserID = (select userid from users where CustomerID=@CustomerID)

INSERT INTO ORDERS(CUSTOMERID,ORDERDATE,FirstName,LastName,Company,Address,Country,City,Province,ZipCode,Telephone,Fax,totalAmount,userid)
    VALUES (@CustomerID,@OrderDate,@FirstName,@LastName,@Company,@Address,@Country,@City,@Province,@ZipCode,@Telephone,@Fax,@totalAmount,@UserID)
    SELECT @@IDENTITY

3.更改查询并使查询更具体，有很多变通办法.


谢谢.

3. change your query and make it more specific, there''s a lot of work around.


thanks.

这篇关于不允许子查询，仅允许标量函数.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！