sql-server – 使用declare和set变量创建存储过程

发布时间：2020-12-12 08:52:43 所属栏目：MsSql教程来源：网络整理

导读：我正在创建一个只有值必须通过的存储过程,我必须从多个表中检索多个值.我试图这样做,但它的显示错误就像 Incorrect Syntax near Begin 和 Must declare the scalar variable @OrderID Declare @OrderItemID AS INTDECLARE @AppointmentID AS INTDECLARE @Purc

我正在创建一个只有值必须通过的存储过程,我必须从多个表中检索多个值.我试图这样做,但它的显示错误就像

Incorrect Syntax near Begin

和

Must declare the scalar variable @OrderID

Declare @OrderItemID AS INT
DECLARE @AppointmentID AS INT
DECLARE @PurchaSEOrderID AS INT
DECLARE @PurchaSEOrderItemID AS INT
DECLARE @SalesOrderID AS INT
DECLARE @SalesOrderItemID AS INT

SET @OrderItemID = (SELECT OrderItemID FROM [OrderItem] WHERE OrderID = @OrderID)

SET @AppointmentID = (SELECT  AppoinmentID FROM [Appointment] WHERE OrderID = @OrderID)

SET @PurchaSEOrderID = (SELECT  PurchaSEOrderID FROM [PurchaSEOrder] WHERE OrderID = @OrderID)

解决方法

您应该尝试此语法 – 假设您希望将@OrderID作为存储过程的参数：

CREATE PROCEDURE dbo.YourStoredProcNameHere
   @OrderID INT
AS
BEGIN
 DECLARE @OrderItemID AS INT
 DECLARE @AppointmentID AS INT
 DECLARE @PurchaSEOrderID AS INT
 DECLARE @PurchaSEOrderItemID AS INT
 DECLARE @SalesOrderID AS INT
 DECLARE @SalesOrderItemID AS INT

 SELECT @OrderItemID = OrderItemID 
 FROM [OrderItem] 
 WHERE OrderID = @OrderID

 SELECT @AppointmentID = AppoinmentID 
 FROM [Appointment] 
 WHERE OrderID = @OrderID

 SELECT @PurchaSEOrderID = PurchaSEOrderID 
 FROM [PurchaSEOrder] 
 WHERE OrderID = @OrderID

END

当然,只有当你返回一个值(不是多个值)时才有效

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!