存储过程

Question

我正在尝试创建一个stored procedure，它将向

Order TABLE

OrderId INT
,CustomerId INT
,StoreId INT
,OrderType VARCHAR(50)
,OrderDate DATE)

它给出了以下错误

(Msg 102，15级，State 1，第108行，靠近“@StoreNumber”)第108行是@StoreNumber = '1‘。

请帮帮我！这是我到目前为止所拥有的

CREATE PROCEDURE spCreateOrder  (
    @CustFName varchar(10), 
    @CustLName varchar(10), 
    @OrderType Varchar(50),
    @OrderDate Date ) 
AS
Begin
    DECLARE @CustomerID INT = (SELECT CustomerID FROM CUSTOMER WHERE CustFName =       @CustFName AND CustLName = @CustLName)
    DECLARE @StoreID INT = (SELECT  StoreID FROM Store WHERE StoreID = @StoreID)

    INSERT INTO tblORDER (CustomerID, StoreID, OrderType, OrderDate)
    VALUES (@CustomerID, @StoreID, @OrderType, @OrderDate)
    DECLARE @OrderID INT = (SELECT @@IDENTITY)
    INSERT INTO tblLINE_ITEM (LineItemID, OrderID, Quantity)
    VALUES (@LineItemID, @OrderID, @Quantity)
End

Answer 1

你的SP不太像你的意图，也缺少参数。看起来应该是：

CREATE PROCEDURE spCreateOrder  (
    @CustFName varchar(10), 
    @CustLName varchar(10), 
    @OrderType Varchar(50),
    @OrderDate Date,
    @StoreID INT ) 
AS
Begin
    DECLARE @MyTable table (OrderID int NOT NULL);  
    INSERT INTO tblORDER (CustomerID, StoreID, OrderType, OrderDate)
    output inserted.OrderId into @MyTable
    SELECT CustomerID, @StoreID, @OrderType, @OrderDate 
    FROM CUSTOMER WHERE CustFName =  @CustFName AND CustLName = @CustLName;

    -- insert into tblLINE_ITEM (LineItemID, OrderID, Quantity)
    -- select ?, OrderId, ? from @MyTable;
End

一个订单几乎没有包含一个单一的订单项目。您应该重新考虑这一点，并检查基本的Server示例。