Posted by Erland Sommarskog on 11/26/07 22:42

D. (d@d.com) writes:
> My software creates a temporary table (#MyTable).
> This table should be used by a report engine and printed each time with
> different "order by" clause, depending on some parameters (and the program
> that creates the temporary table obviously knows these parameters...)
>
> Now, I don't want to pass these paramete to the report engine, because
> I want that the logic of the report will stay only in the program that
> create the table (the report engine should onnly do a "SELECT * FROM
> #MyTable").
>
> So, I'm asking if there is a way to define, for a table, a default
> "order by" clause to use when no "order by" clause is specified in a
> "select" query statement on that table.
>
> If not, I think the only alternative is to create a view on that table. Is
> it correct?

As the others have said: the only way to be guaranteed to get an ordered
result is to use ORDER BY. There is no way around that.

However, you could create your table with a rowno column which you
populate with the row_number() function (available from SQL 2005), and
the report engine could do "SELECT ... FROM #MyTable ORDER BY rowno".


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]