July 29, 2010 at 10:11 am
Hi GURUs,
I have an sp:
ALTER PROCEDURE [dbo].[ListChartDetailByUsage]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
u.ID,
Count(1) as Total, u.FirstName + ' ' + u.LastName as [Name],
convert(varchar(13),e.CreatedOn,120) + ':00:00' as [Date]
from Eventlog e
inner join u on e.UserLogon = u.[login]
--Where u.ID in dbo.[Top5UserByUsage]()
Group by
u.ID,
u.FirstName + ' ' + u.LastName,
convert(varchar(13),e.CreatedOn,120) + ':00:00'
Order By convert(varchar(13),e.CreatedOn,120) + ':00:00', u.FirstName + ' ' + u.LastName
END
This will return all the users records, however, I want to limit the result to only the top 5 users with most records with them. So I write a function called Top5UserByUsage:
ALTER FUNCTION [dbo].[Top5UserByUsage]()
RETURNS TABLE
AS
-- Add the SELECT statement with parameter references here
RETURN(
SELECT top 5 u.ID
from Eventlog e
inner join u on e.UserLogon = u.[login]
group by u.ID
order by count(1) desc, u.ID)
If I do a select * from Top5UserByUsage, it returns the top five IDs.
Back to my original sp, I tried to put the function in the where clause, it returns me a grammar error in the where clause line:
Incorrect syntax near 'dbo'.
How should I write this query? Is there a better elegant way to do it? Thank in advance.
July 29, 2010 at 11:38 am
Generally, my understanding is that it's better for performance to avoid UDF's and just do it inline:
SELECT u.ID,
Count(1) as Total, u.FirstName + ' ' + u.LastName as [Name],
convert(varchar(13),e.CreatedOn,120) + ':00:00' as [Date]
from Eventlog e
join u on e.UserLogon = u.[login]
join
(
SELECT top 5 u.ID
from Eventlog e
join u
on u.[login] = e.UserLogon
group by u.ID
order by count(e.*) desc
) sq on sq.ID = u.ID
Group by u.ID,
u.FirstName + ' ' + u.LastName,
convert(varchar(13),e.CreatedOn,120) + ':00:00'
Order By convert(varchar(13),e.CreatedOn,120) + ':00:00', u.FirstName + ' ' + u.LastName
If you do want to use your UDF, the syntax issue is that it needs to be ...
where columnName in (select columnName from dbo.myUDF())
You could also use it in a join ...
select whatever
from tableA a
join dbo.myUDF() u on u.columnName = a.columnName
July 29, 2010 at 12:58 pm
Thank you. It works.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply