ALTER PROCEDURE [dbo].[SP](@p_out int)
AS

begin
      select *
      from tbl
      order by 1

    set @p_out = <получить из первой строки предыдущего запроса ид>;

end;

create table dbo.t (id int);
go

create procedure dbo.p
 @id int output

as
begin

 update t
  set
   @id = first_id
 output
  inserted.id
 from
  (select top (1000000) id, first_value(id) over (order by id) from dbo.t order by id) t(id, first_id);

end;
go

declare @id int;

insert into dbo.t values (3), (1), (2);
exec dbo.p @id output;
select @id;
go

drop procedure dbo.p;
drop table dbo.t;
go