Creating stored procedure with declare and set variables

Question

I am creating one stored procedure where only value has to pass and I have to retrieve multiple values from multiple tables  I tried to do like this but its showing errors like      Incorrect Syntax near Begin   and     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

User · Answer

You should try this syntax - assuming you want to have @OrderID as a parameter for your stored procedure:

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

OF course, that only works if you're returning exactly one value (not multiple values!)

User · Answer

I assume you want to pass the Order ID in   So   CREATE PROCEDURE  dbo   Procedure Name         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      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  END

[sql-server] Creating stored procedure with declare and set variables

Examples related to sql-server

Examples related to sql-server-ce