1

I have this stored procedure:

exec T2IntranetViaggi2SelezioneConMagazzinoCompetenza @Societa = 20, @Cliente = 55, @TipologiaData = 'P', @TipoData = 'S', @DataInizio = '2013-10-28 00:00:00', @DataFine = '2013-10-28 23:59:59', @DestinatarioCodice = null, @OrdineRiferimento = '', @RiferimentoInterno = '', @PassaggioTp = 'T', @UnitaMisura = '', @Esitato = 'T', @Consegnato = 'T', @CaricoLocalita = '', @CaricoCap = '', @CaricoProvincia = '', @CaricoNazione = '', @ScaricoLocalita = '', @ScaricoCap = '', @ScaricoProvincia = '', @ScaricoNazione = '', @MagazzinoCompetenza = '', @DocumentoNumero = ''

But I want to order the result by the "Documento" field.

I can't edit the stored procedure.

Sergey
  • 1,608
  • 1
  • 27
  • 40
Antonio
  • 1,181
  • 4
  • 15
  • 34
  • possible duplicate of [SQL Server - SELECT FROM stored procedure](http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure) – Ocaso Protal Oct 29 '13 at 07:57

2 Answers2

4

Store your sp's result in table variable, and then query to that table variable.

Example:

create proc p 
as
select code, name from customer
go
declare @t table
(
    code int,
    name varchar(30)
);
insert into @t
exec p;

select * from @t order by name desc;
Iswanto San
  • 18,263
  • 13
  • 58
  • 79
  • The procedure I'm calling with EXEC itself contains a temporary table. This method worked when many other things I tried didn't. Thank you! – thedude Jul 27 '17 at 17:11
1

Assuming that the stored procedure doesn't, itself, use an INSERT...EXEC, then you can do this:

CREATE TABLE #T (
    /* Columns as per the stored procedure's result set */
)

INSERT INTO #T (/* Column list */)
EXEC T2IntranetViaggi2SelezioneConMagazzinoCompetenza @Societa = 20, @Cliente = 55, ...

SELECT * FROM #T ORDER BY Documento

You also say that you cannot edit the stored procedure - if this is because there are other callers of this procedure, then one thing you might consider is duplicating the stored procedure as a user defined function (UDF), and then changing the stored procedure to just call the UDF.

You could then directly query the UDF yourself in a SELECT query and apply your desired ordering directly.

Of course, if the "cannot change" is for other reasons then this may not apply.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • thanks Damien.... for the performance aspect is correct make a temp table on Sqlserver or order the array into PHP code? – Antonio Oct 29 '13 at 07:59
  • @Antonio - I wouldn't expect PHP to be able to out-perform the SQL Server but whether there's any appreciable *difference* in the performance may depend on the exact structure of your data - number of rows and columns involved, etc. – Damien_The_Unbeliever Oct 29 '13 at 08:03