Sql – Need help with Scalar-valued function in SQL Server 2008

sql, sql-server, sql-server-2005, sql-server-2008, user-defined-functions

I need help with this scalar-valued function.

What I want to do is to return the value I get in MaxValue on the line max(Value) AS MaxValue.

The query works and will only return 1 value if ItemId and ListPropertyId exists, but I am not able to create a function of it.

CREATE FUNCTION GetLpivMax (    -- Add the parameters for the function here    @ItemId int,    @ListPropertyId int)RETURNS intASBEGIN  DECLARE @output INT;  WITH U AS (    SELECT i.Id AS ItemId,           lpiv.Value,           lp.Id AS ListPropertyId      FROM ListPropertyItemValues lpiv      JOIN ListPropertyItems lpi ON lpi lpi.Id = lpiv.ListPropertyItemId       JOIN ListProperties lp ON lp.Id = lpi.ListPropertyId      JOIN Items i ON i.Id = lpiv.ItemId)    SELECT @output = MAX(u.value)      FROM U u     WHERE u.listpropertyid = @ListPropertyId        AND u.itemid = @ItemId  GROUP BY u.listpropertyid, u.itemid  RETURN @outputENDGO

Best Solution

I've upvoted your question, but I don't like any of the answers. You should be changing your code to an inline table-valued function instead. This code will be considered procedural every time, and it's just a single query.

I wrote about it at http://msmvps.com/blogs/robfarley/archive/2009/12/05/dangers-of-begin-and-end.aspx, but I'll quickly explain what you should do:

CREATE FUNCTION GetLpivMax  (     -- Add the parameters for the function here     @ItemId int,     @ListPropertyId int ) RETURNS TABLE AS RETURN (SELECT MAX(lpiv.Value) as LpivMax    FROM ListPropertyItemValues lpiv     JOIN ListPropertyItems lpi ON lpi.Id = lpiv.ListPropertyItemId      JOIN ListProperties lp ON lp.Id = lpi.ListPropertyId     JOIN Items i ON i.Id = lpiv.ItemId    WHERE lp.id = @ListPropertyId       AND i.id = @ItemId GROUP BY lp.id, i.id );

Now use it like this:

SELECT ip.*, m.LpivMaxFROM ItemsAndProperties ipCROSS APPLYdbo.GetLpivMax(ip.ItemID, ip.ListPropertyID) m;

You could use OUTER APPLY if you don't want to eliminate rows from your data set. You can add extra aggregates that will be completely ignored if you don't refer to them. But most of all, the Query Optimizer will simplify out your query, so that if it can avoid doing a big chunk of the work, it will.

Hope this helps...