CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008stored-procedures
MinnesotaMom
MinnesotaMom

Reputation: 41

Creating a Stored Procedure

I am trying to create a stored procedure that will allow me to update the ActualArrivalDate and ActualDepartureDate in a TripStop table where StopTypeCode = Drop in the TripStopOrder table.

Create Proc spUpdateTable
@OrderID int, @ArrivalDate datetime, @DepartureDate datetime

As

Begin 

   Begin Transaction
   Select * From dbo.TripStopOrder
   Join dbo.TripStop ON dbo.TripStopOrder.TripStopID = dbo.TripStop.TripStopID
   Where OrderID = ''and StopTypeCode ='Drop'

Once I find this record I need to grab the TripStopId and pass it into the Update statement. Not sure how to this...can I use a temp table then run another Select statement to pick up the TripStopID?

   Update dbo.TripStop SET ArrivalDate='',DepartureDate=''
   Where TripStopID = ''

End
Commit

Any ideas or suggestions would be greatly appreciated. ~Newbie~

Upvotes: 3

Views: 215

Answers (2)

Melanie
Melanie

Reputation: 3111

Instead of doing the SELECT and then the UPDATE, just change your WHERE clause in your UPDATE statement:

WHERE TripStopID = (SELECT T.TripStopID FROM TripStopOrder O
INNER JOIN TripStop T ON O.TripStopID = T.TripStopID
WHERE OrderID = @OrderID AND StopTypeCode = 'Drop')

Upvotes: 2

Darren
Darren

Reputation: 70718

You can assign the value to a variable such as @TripStopId:

DECLARE @TripStopId INT

Select @TripStopid = TripStopId
From dbo.TripStopOrder
Join dbo.TripStop ON dbo.TripStopOrder.TripStopID = dbo.TripStop.TripStopID
Where OrderID = ''and StopTypeCode ='Drop'

Then you can use it in your UPDATE statement.

Update dbo.TripStop SET ArrivalDate='',DepartureDate=''
Where TripStopID = @TripStopId

Upvotes: 4

Related Questions