Error in storedprocedure

1

Hi following is my stored procedure.
create procedure driversms
(@tid nvarchar(20),
@did nvarchar(20))
as

@did = (select distinct driver_id from Busdetails where tour_id=@tid)
select mobile from Drivermaster where driver_id=@did

my requirement is to fetch driver_id from busdetails by passing tour_id as parameter.
and store it in @did.then fetch mobile from drivermaster using @did.
error showing is
Msg 102, Level 15, State 1, Procedure driversms, Line 10
Incorrect syntax near '@did'.

how to solve this
Regards
Baiju

2

you don't need the @did variable, you can use INNER JOIN to do that

CREATE PROCEDURE driversms
    @tid nvarchar(20)
AS
BEGIN
    SELECT     d.mobile
    FROM       Busdetails b
    INNER JOIN Drivemaster d on b.driver_id = d.driver_id
    WHERE      b.tour_id = @tid
END
3

As khtan has said for the answer, but a couple of other points in case helpful.

IF you did want to assign a value to a variable, as per your code, you need to use SELECT or SET:

SELECT @did = (select distinct driver_id from Busdetails where tour_id=@tid)

If you want a working variable in your SProc you can DECLARE it in the body of the procedure. You have declared it as a [required] parameter so you would have to provide a value (even if NULL) whenever you called the procedure. This is how you would declare it as a local variable:

create procedure driversms
(@tid nvarchar(20))
as
DECLARE @did nvarchar(20)
...
4

one more thing to note, for the above query, If you have multiple driver_id per tour_id , you will receive error

Subquery returned more than 1 value
5

Good point!

If there are multiple rows, and you just want one (the "biggest" or the "newest" or similar) then you can do this

SELECT @did = (select TOP 1 distinct driver_id from Busdetails where tour_id=@tid ORDER BY MySortColumn1, MySortColumn2, ...)