Don't sort the result set of function in SQL Server

Question

I have table in SQL Server called source with source_id as primary key and source_name.

This is my function which is generating an incorrect result.

This is insert statement using below function:

insert into ppm_schema.dim_source (source_id, source_name)
values (ppm_schema.generatesourceid('clinical'), 'clinical');

insert into ppm_schema.dim_source (source_id, source_name)
values (ppm_schema.generatesourceid('qivia'), 'qivia');

insert into ppm_schema.dim_source (source_id, source_name)
values (ppm_schema.generatesourceid('ab'), 'ab');

insert into ppm_schema.dim_source (source_id, source_name)
values (ppm_schema.generatesourceid('b'), 'b');

create function ppm_schema.generatesourceid
    (@SourceName varchar(50))
returns varchar(50)
as
begin
    declare @count varchar(50);
    declare @ide varchar(50);
    declare @maxid varchar(50);
    declare @prefix varchar(20);
    declare @id varchar(20);

    with cte as
    (
        select top 1 
            count(*) + 1 as number
        from 
            ppm_schema.dim_source 
    )
    select @count = number from cte;

    select @prefix = left(@SourceName, 3);

    if len(@SourceName) >= 3
    begin
        select @ide = @prefix + right('000' + cast(@count as varchar(3)), 3)
    end
    else if len(@SourceName) >= 2
    begin
        select @ide = @prefix + 'x'+ right('000' + cast(@count as varchar(3)), 3)
    end
    else if len(@SourceName) >= 1
        select @ide = @prefix + 'xx' + right('000' + cast(@count as varchar(3)), 3)

    return @ide
end

Result of this function:

source_id	source_name
abx003	ab
bxx004	b
cli001	clinical
qiv002	qivia

I need the result to be

source_id	source_name
cli001	clinical
qiv001	qivia
abx003	ab
bxx004	b

Answer 1

The concerns in the comments are fully valid.

That said, using an order by in your select statement would put them in the order you list. Because your prefix is always 3 characters, you can pull everything on the right - 3 to use the number (cli001) to sort by.

select
    *
from ppm_schema.dim_source
order by RIGHT(source_id, LEN(source_id)-3)

Results

source_id	source_name
cli001	clinical
qiv002	qivia
abx003	ab
bxx004	b

If that is everything that your function does, you could put it into a single query.

create function ppm_schema.generatesourceid
    (@SourceName varchar(50))
returns varchar(50)
as
begin
    return (SELECT 
            LEFT(LEFT(@SourceName, 3) + 'xxx', 3) 
                + RIGHT('000' + CAST(COUNT(*) + 1 AS varchar(3)), 3)
        FROM ppm_schema.dim_source)
end

Beyond the scope of the question: The generated ids that I have seen tend to use a sequencer, sequencer table, or at least a lock followed by direct insert.