CODEWITHSUNDEEP
sql-serverstored-procedures
mitomed
mitomed

Reputation: 2066

Stored procedure inner join

My first stored procedure (in sql-server). I'm not able to make it work, it raises a warning in a.Id After reading, and not really understand much, I'm not even sure if I can use this inner join inside a stored procedure.

select top 1 b.*, a.*
FROM Bids b
INNER JOIN Auctions a 
ON b.Auction_Id = a.Id
(NOLOCK) WHERE ( a.Ends IS NOT NULL AND a.Starts IS NOT NULL AND a.Starts < @Now AND a.Ends > @Now)
ORDER BY b.CreationTime DESC

Actually, I'll need just b.* but I assume I have to retrieve all the fields?.

Thanks

Upvotes: 0

Views: 1757

Answers (2)

podiluska
podiluska

Reputation: 51494

Take the (Nolock) out, or put it after the table name if you need it.

You can use Select to select any fields you want from any table in your query.

Upvotes: 1

Yuck
Yuck

Reputation: 50825

Change the locking hint to this:

INNER JOIN Auctions a WITH(NOLOCK)

Full query would be:

select top 1 b.*, a.*
FROM Bids b
     JOIN Auctions a WITH(NOLOCK) ON b.Auction_Id = a.Id
WHERE ( a.Ends IS NOT NULL
    AND a.Starts IS NOT NULL
    AND a.Starts < @Now
    AND a.Ends > @Now)
ORDER BY b.CreationTime DESC

Upvotes: 1

Related Questions