?
Solved

T-SQL : Sorting with UDFs

Posted on 2008-11-07
25
Medium Priority
?
764 Views
Last Modified: 2012-05-05
Does any one knows how I can sort data using user-defined functions? I saw it is not possible inside the UDF so I tried using a stored procedure that calls the UDF. And for that I have to use dynamic sql. The trouble then is that I don't manage to pass the parameters to the UDF.
0
Comment
Question by:karakav
  • 7
  • 6
  • 5
  • +3
25 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22903644
>>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()
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22903682
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
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22903716
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. :)
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 4

Author Comment

by:karakav
ID: 22903860
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.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22903888
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?
0
 
LVL 4

Author Comment

by:karakav
ID: 22904403
I don't get any error,  just shows like it executed but I doesn't .
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22904836
Can you post code?
0
 
LVL 22

Expert Comment

by:dportas
ID: 22907641
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.

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22908059
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.  :)
0
 
LVL 4

Author Comment

by:karakav
ID: 22908102
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

Open in new window

0
 
LVL 22

Expert Comment

by:dportas
ID: 22908377
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?
0
 
LVL 4

Author Comment

by:karakav
ID: 22908543
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.
0
 
LVL 22

Expert Comment

by:dportas
ID: 22908643
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 ;
0
 
LVL 4

Author Comment

by:karakav
ID: 22908707
My code is not working as you can see it if you test it.
0
 
LVL 22

Accepted Solution

by:
dportas earned 1000 total points
ID: 22908826
I see, you just need to put the ORDER BY at the end:

  SET @QUERY =
  'SELECT * FROM (
    SELECT ' + @Columns + ',
        ROW_NUMBER() OVER (ORDER BY '+ @OrderColumn + ') AS RowN FROM ' + @Udf + '( '''
    + @ContactName + ''','
    + @OrdDate  + ')) T1
    WHERE T1.RowN BETWEEN ' + @StartIndex + ' AND ' + @EndIndex
    + ' ORDER BY '+ @OrderColumn + ' ' + @OrderDirection;

Make sure you understand about the implications of SQL Injection and test for it in your application. You are possibly making yourself extremely vulnerable this way. Some of your variables could be parameterised rather than dynamic and parameterising is a safer way to do it. The definitive guide is:
http://www.sommarskog.se/dynamic_sql.html
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22909911
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.
0
 
LVL 22

Expert Comment

by:dportas
ID: 22911417
acperkins, re-read the thread. Results of a UDF are unordered so ordering in a UDF is not possible.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22912835
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...

Open in new window

0
 
LVL 22

Expert Comment

by:dportas
ID: 22912951
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22913323
@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.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22914645
>>acperkins, re-read the thread.<<
I just did and my point still stands.

Most people here call me by my name: Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22914653
But Mark is right unless the author can post their highly confidential code, there is not a lot we can do to help.
0
 
LVL 4

Author Comment

by:karakav
ID: 22916081
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.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1000 total points
ID: 22916284
There is nothing wrong with dynamic SQL - it is more a case of being the right time and right place, and the right kind of security / precautions are put in place. Certainly more so at "risk" if dynamic SQL is being generated "client side" - for all the obvious reasons - ie it is dynamic, it is going to execute SQL code, and it could execute SQL code that you are not 100 % in control of. If hoever, it is all server side, protected from "mere mortal" user access, then some of that risk dissapates pretty quickly. Being hidden inside a SP is a reasonable start, but you probably do need to build in a few checks - for starters make sure the field / variable definitions have the right "type" to help prevent to inclusion of strings (such as orddate and startindex, endindex). The do some simple checks on the parameters themselves, and you should be OK.

Certainly Anthony is correct in saying the best advice can be given by seeing the real problem at first hand... However, can appreciate the security aspects, and while you do not want to show the code, but why can't you answer a few of the questions we have raised ? For example, why do you think there is a need for a SP to call the UDF when you can simply form the query with a few parameters anyway ? But if you just want to persue a specific thread, then you might not be getting all the best that we could be offering.

In terms of generating dynamic SQL - the you need to say why it is not working. In fact unless you can provide some more specific feedback it is just a guessing game... e.g. your code in the SP is missing a few things, for starters, the parameters being passed to the stored procedures should be single quote encapsulated, second, the startindex and endindex cannot be strung as integers - hence you have nominated varchars, but really should be integers, then there is the row number function against a udf which does require an additional criteria (remember it is like a subquery) so need to add in top 100 percent, or similar... So, just guessing, think you will get further with the following... and a small hint moving forward, if you do a PRINT instead of the execute you sill see the resulting query string in the "messages" tab and can copy / paste that to see if it works.

	SET @QUERY = 'SELECT * FROM (
	SELECT TOP 100 PERCENT ' + @Columns + ',
				ROW_NUMBER() OVER (ORDER BY '+ @OrderColumn + ') AS RowN FROM ' + @Udf + '( '''
		+ @ContactName + ''','''
		+ convert(varchar,@OrdDate,106)  + ''')
		ORDER BY '+ @OrderColumn + ' ' + @OrderDirection + ') T1 
 
		WHERE T1.RowN BETWEEN ' + convert(varchar,@StartIndex) + ' AND ' + convert(varchar,@EndIndex )

Open in new window

0
 
LVL 4

Author Closing Comment

by:karakav
ID: 31514301
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question