How can get ordered by result within a function in SQL server

Question

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 3-Nov-22 23:43pm

Member 10604657

Updated 4-Nov-22 1:06am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2022-11-04T01:06:00

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.