CODEWITHSUNDEEP
sql-serverstored-procedures
user979331
user979331

Reputation: 11851

SQL - Stored Procedures - get values from subquery

I have a stored procedure and EXEC call like:

EXEC Schedule_InsertReservations 
     @ScheduleID = (SELECT ScheduleID FROM Schedule WHERE Job_No = 'ABC'),
     @ScheduleTaskID = (SELECT ScheduleTaskID FROM ScheduleTasks WHERE (ScheduleID = (SELECT ScheduleID FROM Schedule WHERE Job_No = 'ABC')) AND LibraryTaskID = 247),
     @ResourceID = (SELECT Vendor_ID FROM tblVendors WHERE Vendor_Name = 'Blue Line Taxi')

and I am trying to set the values for the stored procedure using sub-queries but I get these errors when I run it:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Can I not use sub-queries in stored procedures?

Upvotes: 1

Views: 4614

Answers (2)

shA.t
shA.t

Reputation: 16958

Execute a stored procedure or function

[ { EXEC | EXECUTE } ]
    { 
      [ @return_status = ]
      { module_name [ ;number ] | @module_name_var } 
        [ [ @parameter = ] { value 
                           | @variable [ OUTPUT ] 
                           | [ DEFAULT ] 
                           }
        ]
      [ ,...n ]
      [ WITH RECOMPILE ]
    }
[;]

value
Is the value of the parameter to pass to the module or pass-through command. If parameter names are not specified, parameter values must be supplied in the order defined in the module.
...
If the value of a parameter is an object name, character string, or qualified by a database name or schema name, the whole name must be enclosed in single quotation marks. If the value of a parameter is a keyword, the keyword must be enclosed in double quotation marks.

I think value can be just a name of an object or a keyword. and you can't set parameters in that way.

And @FireBlade solution is your solution.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Why cant you DECLARE three variables and use it as parameter to the procedure.

DECLARE @sch_id     INT,--change the datatype based on your schema.
        @Vendor_ID  INT,
        @Sch_TaskID INT

SELECT @sch_id = ScheduleID
FROM   Schedule
WHERE  Job_No = 'ABC'

SELECT @Sch_TaskID = ScheduleTaskID
FROM   ScheduleTasks ST
       INNER JOIN Schedule S
               ON St.ScheduleID = s.ScheduleID
WHERE  s.Job_No = 'ABC'
       AND s.LibraryTaskID = 247 

SELECT @Vendor_ID = Vendor_ID
FROM   tblVendors
WHERE  Vendor_Name = 'Blue Line Taxi'

EXEC Schedule_insertreservations
  @ScheduleID = @sch_id,
  @ScheduleTaskID = @Sch_TaskID,
  @ResourceID = @Vendor_ID

Upvotes: 4

Related Questions