存储过程中的问题 [英] problem with the store procedure

问题描述

在该查询中，select附近的语法不正确.任何人都可以解决我的问题吗?

I have incorrect syntax near select in that query.. can any one can sort out my problem

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

推荐答案

我假设您所有的表和列都存在.
不知道您是否可以更具体，但请尝试将子查询放在方括号之间，如下所示.

I am assuming all your tables and columns exist.
Not sure if you can''t be more specific, but try putting your subquery between brackets, like so.

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

如何?一:

How about this one :

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
 declare @uid int
 select @uid = 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, @uid)
	SELECT @@IDENTITY
END

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)

根据您的查询，从其中CustomerID = @ CustomerID可以返回多个结果的用户中选择用户ID.

谢谢.

based on your query, the select userid from users where CustomerID=@CustomerID can return more than 1 result.

thanks.

这篇关于存储过程中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！