CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Ruchi
Ruchi

Reputation: 146

How to encapsulate the statements and use it in Order by Clause

I need to encapsulate this case statements and make my query short, since i need to generate report using multiple temp tables. I donot want to use the case statement every where.

Select * from #SumED_DB 
order by case  
when [type] = 'Within 30 days' then 1
when [type] = 'Within 60 days' then 2
when [type] = 'Within 90 days' then 3
when [type] = 'Within 120 days' then 4
when [type] = 'Greater than 120 days' then 5
when [type] = 'Blank' then 6
else 7
end

How do I resolve this issue? I know function will help me, but how will i achieve it?

I want my query to look like:

select * from #SumED_DB order by (function);

I Have used a table and joined it and then have used order by clause. How can I do this using function?

Upvotes: 2

Views: 334

Answers (1)

iamdave
iamdave

Reputation: 12243

Other than creating a lookup table to join to for your ordering, I think the function or repeating code are your only other options. In general you want to avoid calling functions though, as not only will they produce a separate call for each row, they also prohibit parallelism which will degrade your query performance.

If your dataset will always be a small summary table - as this looks to be from your naming conventions and requirements - this will probably not be that big of a trade off.

To create your function:

create function dbo.usp_TypeOrder (@Type nvarchar(100))
returns int
as
begin
return (select case @Type  
                when 'Within 30 days' then 1
                when 'Within 60 days' then 2
                when 'Within 90 days' then 3
                when 'Within 120 days' then 4
                when 'Greater than 120 days' then 5
                when 'Blank' then 6
                else 7
                end
        )
end;

and to use it in your query:

select *
from #SumED_DB
order by dbo.usp_TypeOrder([type]);

Upvotes: 2

Related Questions