Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  .NET Inside SQL Server (2005)  Help!!! Troubles with table-valued functions

Author  Topic  vl18 Starting Member 2 Posts Posted - 2008-04-17 : 09:13:27 Hi! Here's my function. The trouble - I can not make ORDER BY the "visits_count", "properties_count", "enquiries_count" fields. May be some one could help me with this? CREATE FUNCTION [dbo].[GetPagedStatistics] ( @start_index int, @count int, @condition nvarchar(255), @order_field nvarchar(255), @date_from datetime, @date_to datetime ) RETURNS @total_stat TABLE ( username nvarchar(255), first_name nvarchar(255), last_name nvarchar(255), properties_count int, enquiries_count int, visits_count int, id_user int) BEGIN INSERT @total_stat SELECT top (@count) dbo.users.username, dbo.users.first_name, dbo.users.last_name, ISNULL(COUNT(DISTINCT dbo.advertisement.id_advertisement), 0) AS properties_count, ISNULL(COUNT(DISTINCT dbo.enquiry_emails.id_enquiry_email), 0) AS enquiries_count, ISNULL(COUNT(DISTINCT dbo.property_statistics.id_statistics), 0) AS visits_count, dbo.users.id_user FROM dbo.property_statistics RIGHT OUTER JOIN dbo.advertisement RIGHT OUTER JOIN dbo.users ON dbo.advertisement.id_user = dbo.users.id_user LEFT JOIN dbo.enquiry_emails ON dbo.enquiry_emails.id_advertisement = dbo.advertisement.id_advertisement ON dbo.property_statistics.id_advertisement = dbo.advertisement.id_advertisement WHERE 1=@condition and (dbo.advertisement.creation_date <= @date_to and dbo.advertisement.creation_date >= @date_from ) and ( (dbo.enquiry_emails.creation_date <= @date_to and dbo.enquiry_emails.creation_date >= @date_from and dbo.property_statistics.view_date <= @date_to and dbo.property_statistics.view_date >= @date_from ) or (dbo.property_statistics.view_date is null) or (dbo.enquiry_emails.creation_date is null) ) and (ISNULL(dbo.advertisement.id_parent, 0) = 0) GROUP BY dbo.users.username, dbo.users.first_name, dbo.users.last_name, dbo.users.id_user order by case when @order_field='username' then dbo.users.username end, case when @order_field='first_name' then dbo.users.first_name end, case when @order_field='last_name' then dbo.users.last_name end, case when @order_field='properties_count' then 1 end, case when @order_field='enquiries_count' then 1 end, case when @order_field='visits_count' then 1 end RETURN END