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

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

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

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

Examples related to sql-server

Examples related to sql-server-ce