Click here to Skip to main content
15,904,346 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a function in SQL server and I used it in an application that I don't have access to its source file,so I cannot change the application. this function is likes below:
SQL
create function f1(param1 int)
Returns  @rep Table (Id Int,T_Date DateTime,Value Decimal(18,3))
as
begin
   insert into @rep(id,T_Date,Value)
   select id,T_Date,Value from t1 order by id; 
return
end


What I have tried:

problem is that when I call this function, the result has different order every time. I know that I can use order by after calling the function, but as I said, I don't have access to the source of the application. So I can only change the database's items.

I used order by within the function but it does not work properly.
Posted
Updated 4-Nov-22 1:06am

1 solution

You can't. Any query that selects from a table, view, or table-valued function without explicitly specifying an ORDER BY clause will receive the results without any particular order applied.

There may be some cases where it seems like SQL applies a "default order"; but these are ephemeral, and subject to change at any time. For example, you may find that the apparent "default order" changes as the amount of data in the table grows.

If you want the records in a particular order, then you must use an ORDER BY statement when you select the records. In this case, than means you will need to get the authors of the application to update it to specify an ORDER BY clause when they select the data.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900