Rajah Donalt sends in a stored procedure he came across in a production system. I'll bet you didn't know that this is the fastest way to select a row from a table from a primary key. At least, according to the author of the procedure.
CREATE PROCEDURE sp_get_order (
select * into #temp_order from Orders
delete from #temp_order where OrderID<>@OrderID
select * from #temp_order
drop table #temp_order
[Table and column names have been changed to protect the innocent]