T-SQL : Sorting with UDFs

>>Does any one knows how I can sort data using user-defined functions?

If it is a scalar UDF, you should be able to do this:


ORDER BY dbo.MyUDF()

And if you are just talking about a table UDF that you have a select statement in that you want to sort, you can most definitely do this in the UDF.

You just need to add TOP to select statement:

i.e.
CREATE FUNCTION func
(      
      @taskid INT
)
RETURNS TABLE
AS
RETURN
(
      -- Add the SELECT statement with parameter references here
      SELECT TOP 100 PERCENT t.*, s.subTaskCompletionStatus
      FROM Task t INNER JOIN SubTask s
      ON s.taskId = t.id
      WHERE t.id = @taskid
      ORDER BY s.subTaskCompletionStatus DESC
)
GO

And if it lets you add the order by statement, but doesn't actually do the sort then you can sort like this without dynamic sql.

SELECT *
FROM dbo.func(1)
ORDER BY 1 --orders by column number 1

SELECT *
FROM dbo.func(1)
ORDER BY subTaskCompletionStatus DESC

But I am probably just confusing what the issue is. :)

I can't use TOP because I  don't know in advance how many columns I will take out. another thing is that I don't which column I will perform the sorting against and in which direction.

Ok, understand now, although I am still unsure what the issue is as this should work in your stored procedure pretty similar except with dynamic SQL; however, the ending query generated by dynamic SQL should look not much different in syntax...

What error or issue are you having getting parameters passed in?

I don't get any error,  just shows like it executed but I doesn't .

Can you post code?

As the name suggests, the result of a table-valued function is a table. Tables are always unordered sets of rows. So you must use order by in the query that references the function to get an ordered result:

SELECT col1, col2
FROM dbo.YourFunction()
ORDER BY col1, col2 ;

Kludges using TOP to apply an ORDER are just that - kludges. The results of such are not guaranteed because if you don't specuify ORDER BY in the query that calls the function then by definition the results of that query are unsorted.

Agree with dportas.  I posted comment on how you could include ORDER BY in a function or view...officially you have to add TOP 100 PERCENT and it works, but not recommended as being pointed out as doesn't do what you want it anyway.  :)

Unfortunately I can´t post my code as it is private and will take me time to change. However, I will try to send some thing similar. The reason I can use you example is that I would like to pass two parameter: one for the column to sort and another one for the direction of the sort (ASC or DESC). And I failed to do that inside a UDF.

I hence create a stored procedure with dynamic statement that will allow me, not only to pass the column and the direction of the sort but as well the name of the UDF I want to use. After hours of statement parsing (as my UDF require more that 60 parameters) I finally couldn't make it as I got the same message from the SP: No sorting at all if I am getting data from a UDf.

See snippet below

--Create the UDF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION OrdersByContact
(	
	@CusId VARCHAR(20),
	@OrdDate DATETIME
 
)
RETURNS TABLE 
AS
RETURN 
(
	WITH Ord AS
	(
		SELECT	C.CustomerId,
				C.ContactName,			
				O.OrderDate,
				O.RequiredDate
				
		FROM Customers C INNER JOIN Orders O
			ON O.CustomerId = C.CustomerId
	)
 
	SELECT * FROM Ord 
	WHERE CustomerId =  @CusId AND OrderDate = 
	CASE 
		WHEN @OrdDate IS NOT NULL THEN @OrdDate
		ELSE OrderDate
	END
)
 
--Create the SP
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE SortUdf 
	@ContactName VARCHAR(20),
	@OrdDate VARCHAR(10),
	@StartIndex VARCHAR(20),
	@EndIndex VARCHAR(20),
	@Udf VARCHAR(MAX),
	@Columns VARCHAR(MAX),
	@OrderColumn VARCHAR(MAX),
	@OrderDirection VARCHAR(10)
AS
BEGIN
	DECLARE @QUERY NVARCHAR(MAX)
 
	IF (@OrdDate IS NULL) SET @OrdDate = 'NULL'
	
	SET @QUERY = 'SELECT * FROM (
 
	SELECT ' + @Columns + ',
				ROW_NUMBER() OVER (ORDER BY '+ @OrderColumn + ') AS RowN FROM ' + @Udf + '( '
		+ @ContactName + ','
		+ @OrdDate  + ')
		ORDER BY '+ @OrderColumn + ' ' + @OrderDirection + ') T1 
 
		WHERE T1.RowN BETWEEN ' + @StartIndex + ' AND ' + @EndIndex 
 
EXEC sp_executesql @QUERY
	
END
GO
 
 
--Call the SP
USE [Northwind]
GO
 
DECLARE	@return_value int
 
EXEC	@return_value = [dbo].[SortUdf]
		@ContactName = N'ALFKI',
		@OrdDate = NULL,
		@StartIndex = N'1',
		@EndIndex = N'25',
		@Udf = N'OrdersByContact',
		@Columns = N'*',
		@OrderColumn = N'ContactName',
		@OrderDirection = N'DESC'
 
SELECT	'Return Value' = @return_value
 
GO

Select allOpen in new window

I'm not sure I understand what answer you want. You cannot sort within a table-valued function. Are you now asking how to do something else with a proc?

Actually that's my concern. I am looking for a way of sorting my data. I have more than 15 UDF that I wrote to try me data to use in Reporting Services. It was working fine until the database started to grow big. Now the reports take more than 40 minutes to show up and I am looking for a way to improve things.  One idea is to bring only those rows that are shown on a page and go to look for more on demand. Every work OK until I realize that I need to take care of sorting, a service that was previously done internally in reporting services. That's my real concern about sorting inside UDF.

A function is always part of a SELECT statement, whether you do it in a proc or in Reporting Services or anywhere else. Put your ORDER BY in that SELECT statement. It's possible to make ORDER BY dynamic using CASE expressions but that's a different question and it looks like you already have the code working.

SELECT col1, col2
FROM dbo.YourFunction()
ORDER BY col1, col2 ;

My code is not working as you can see it if you test it.

If the number of columns is a small number there is no need to use Dynamic SQL.  You can just use a CASE statement in the ORDER BY clause, that means that you can also use it in a UDF.

acperkins, re-read the thread. Results of a UDF are unordered so ordering in a UDF is not possible.

Getting very confusing here...

1) cannot sort inside the udf because you do not know what columns to sort on, so,
2) create a stored procedure to sort based on the columns that you do know, and the direction...
3) results of a udf can indeed be sorted
4) cannot create a udf returning a table unless you know the columns / structure of the table to be returned...

The only real problem that I can see is the direction - either asc or desc, but if you are creating some kind of user input so they can decide the columns and the direction to sort on, then creating the SQL to call the UDF should also be easy enough, well at least as easy as calling a SP.

So there is my confusion... Why create a SP to call a UDF because of a Column selection and a direction - and then call that SP with somehow requested columns...

Unless of course it is a reporting services problem and you are thinking that the user input is going to resolve the parameter queries - but that is an entirely different question...

So, how are you interacting with the UDF - is it just a parameterised query ? Why aren't you just adding the order by to the select from the udf (not in the udf) ? Whay is there a need for a stored procedure to make a UDF sort the way you want ?

Try these examples, and if you would care to explain a bit more...

--
-- step 1 create a test udf that returns a table in some kind of sorted sequence...
--
create function udf_selections ()
returns @tbl table (asc_id int identity, col1 varchar(10), col2 varchar (60), int1 int, money1 money, datetime1 datetime)
as
begin
   insert @tbl (col1,col2,int1,money1,datetime1)
   select * from
      (
      select 'c1-1' as c1,'c2-1' as c2,101 as c3,100.01 as c4,'19000101' as c5 union
      select 'c1-3' as c1,'c2-3' as c2,103 as c3,100.03 as c4,'19000309' as c5 union
      select 'c1-2' as c1,'c2-2' as c2,102 as c3,100.02 as c4,'19000210' as c5 union
      select 'c1-6' as c1,'c2-6' as c2,106 as c3,100.06 as c4,'19000120' as c5 union
      select 'c1-4' as c1,'c2-4' as c2,104 as c3,100.04 as c4,'19000521' as c5 union
      select 'c1-5','c2-5',105,100.05,'19000105' 
      ) as x order by 5
   return
end
GO
--
-- step 2 select from the UDF to show that it does in fact return data in "date" sequence
--
select * from dbo.udf_selections()
GO
--
-- step 3 simply add the order by and direction to the select from the UDF - why have a stored procedure do it ?
--
select * from dbo.udf_selections() x order by x.int1 asc
GO
--
-- or, if needed some dynamic SQL - which is the same as doing your stored procedure in many ways...
--
 
declare @column_name varchar(255)
declare @direction varchar(4)
declare @sql varchar(max)
 
set @column_name = 'int1'
set @direction = 'asc'
set @sql = 'select * from dbo.udf_selections() x order by x.' + @column_name + ' ' + @direction
exec (@sql)
 
--
-- or create a view even...
--
create view vw_udf_selections
as
select * from dbo.udf_selections()
go
--
-- now select just like any other datasource...
--
select * from vw_udf_selections
 
 
 
-- so, really not sure where the "challenge" is that requires a stored procedure in order to sort the results...

Select allOpen in new window

Mark, just to be clear about "Step 2" in your example. This statement:

select * from dbo.udf_selections()

has no ORDER BY. So by definition the results of that statement are unordered. This is well documented and I don't know why you would think the example demonstrates anything else (if that was in fact what you meant).

You do have ORDER BY as part of an INSERT... SELECT statement but that is irrelevant because the table being inserted to is unordered and no end result is guaranteed. It's very easy to prove examples where query order without ORDER BY doesn't match insertion order.

You are correct in saying that you don't need a stored procedure to order the results. You do however need an ORDER BY clause in your query.

@dportas, well kind of, except they are in ID sequence which was determined by the internal sort... which does give a predicted sorted output, in so much as it will match the inserted sequence even with a heap table will tend to return results in chronologically added sequence, so long as the pages are chronologically correct, and a bit like a clustered index which is meant to be in sorted sequence, but only so far as pages are chronologically in tact on the same partition / same extent without logical or physical fragmentation of those pages when adding. I was going to add a clustered primary key to that table, ie asc_id int identity primary key clustered, but no point...

But you are correct in so much as you cannot absolutely 100% gaurantee the sequence other than using an order by. In fact the ANSI SQL Standard says that it must not be relied upon without an order by.  And you are also correct, as there is no real point in that first example, because the real message is in the subsequent examples.

And that real message behind the examples was, why use a SP when there are a number of other possibilities ? That is where I am a bit confused by the problem... Which could be quickly cleared up if  karakav could paint a clearer picture as to what is needing to be achieved and by what process / interface is being used to launch the stored procedure, or query.

>>acperkins, re-read the thread.<<
I just did and my point still stands.

Most people here call me by my name: Anthony

But Mark is right unless the author can post their highly confidential code, there is not a lot we can do to help.

I am sorry guys, I was absent yesterday and I can see I missed interesting conversations. The code I provided is exactly the same I am using, except that I changed the name of the database and other system objects(tables, UDF, SP). I then don't understand what you don't get right in my post. I tought my comment number : 22908543 brought some details about the issue. So if there is any more details you need about the post, ask it clearly and I will be glad to answer that.

I was almost satisfied by the answer dportas gave under comment number 22908826 but I was then stoped by the reading provided farther about the use of dynamic SQL.

Thanks