CODEWITHSUNDEEP
sql-server
Bashar Abu Shamaa
Bashar Abu Shamaa

Reputation: 2029

Create a Function in SQL Server that return many values to use for sort

I have a stored procedure that has a customized sort (after order by). In other words, after order by I do some substring and length calculations on some fields and sort on two criteria:

    Order By 
 LEFT(InvoiceID, PATINDEX('%[0-9]%', InvoiceID) - 1), --first sort value
 CONVERT(INT, SUBSTRING(InvoiceID, PATINDEX('%[0-9]%', InvoiceID), LEN(InvoiceID))) -- second sort value

The problem is that I need to use the same sort on many other stored procedures. So I want to create a function that applies the same sort. While I can make a function that returns one value, I need a function that returns two values as above.

This sql function does not work:

CREATE FUNCTION [dbo].[fn_AlphaNumreicSort] (@AlphaNumreicValue nvarchar(MAX))
RETURNS (
@SortedVar1  nvarchar(MAX)
,@SortedVar2  nvarchar(MAX)
)
AS
....

What is the best way to handle this?

Upvotes: 1

Views: 77

Answers (1)

GSerg
GSerg

Reputation: 78134

To return more than one value, you need to declare a table valued function.
In your case you can make an inline function too, which has the added benefit of it working much faster, as if it was a part of the query.

create function [dbo].[fn_AlphaNumreicSort] (@AlphaNumreicValue nvarchar(MAX))
returns table
as
return (
  select
    LEFT(@AlphaNumreicValue, PATINDEX('%[0-9]%', @AlphaNumreicValue) - 1) as SortedVar1,
    CONVERT(INT, SUBSTRING(@AlphaNumreicValue, PATINDEX('%[0-9]%', @AlphaNumreicValue), LEN(@AlphaNumreicValue))) as SortedVar2
);

You then use it in the query with outer apply:

select ...
from
  some_table
  outer apply [dbo].[fn_AlphaNumreicSort](InvoiceID) as sort_values
...
order by
  sort_values.SortVar1,
  sort_values.SortVar2

Upvotes: 2

Related Questions