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)

Where is `@OrderID` declared? Please post the entire procedure, there is no `BEGIN` in the code you posted. Also why are you assigning to variables then never using the results anywhere? Should these be output parameters? — Martin Smith, Feb 11 '12 at 12:51
Since I assume there may be many order items, appointments, or purchase orders for an order, can you describe which one you want to assign to the variable? It also might help to post all of the code as @Martin suggested and also to post your actual goals, some sample data and desired results. — Aaron Bertrand, Feb 11 '12 at 14:53

score 19 · Answer 1 · answered Feb 11 '12 at 12:50

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!)

score 10 · Answer 2 · edited Jan 14 '16 at 18:31

10

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

edited Jan 14 '16 at 18:31

Community

1
1

answered Feb 11 '12 at 12:49

twilson

2,062
14
19

I dunno, I guess it's personal preference, but it's clearer what's going on using `SET`, especially for a single value. Otherwise it gets hidden in the whole `SELECT` statement. – twilson Feb 11 '12 at 12:57
1

On the other hand, your result is undefined because you've used `TOP` without `ORDER BY` - it will pick an arbitrary row. I would prefer to leave out the `TOP` - this way if you meant the inner query to only select one row, you get an error otherwise. With the `SELECT @OrderItemID = ` or `TOP 1` approaches, you get the arbitrary row silently. For cases where `OrderID` is the primary key it's one thing, but in other cases I think we need a better definition of which AppointmentID / PurchaseOrderID is actually desired. The oldest? The most recent? The one with the highest value? – Aaron Bertrand Feb 11 '12 at 14:52

Creating stored procedure with declare and set variables

2 Answers2