Need help with Scalar-valued function in SQL Server 2008

Question

Hello, 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 int
AS
BEGIN
  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 @output
END
GO

Answer 1

Add these lines to your code and it should work:

Add this to the beginning (just after BEGIN)

DECLARE @output int

Add this to the end (just before END)

RETURN @output

Put it all together like this:

CREATE FUNCTION GetLpivMax 
(
    -- Add the parameters for the function here
    @ItemId int,
    @ListPropertyId int
)
RETURNS int
AS
BEGIN

  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 @output
END
GO

Answer 2

Use:

DECLARE @output INT

BEGIN
  WITH U AS (
    SELECT i.Id AS ItemId,
           lpiv.Value,
           lp.Id AS ListPropertyId
      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)
    SELECT @output = MAX(u.value)
      FROM U u
     WHERE u.listpropertyid = @ListPropertyId 
       AND u.itemid = @ItemId
  GROUP BY u.listpropertyid, u.itemid

  RETURN @output
END

The SELECT @output = MAX(u.value) assigns the value to the @output variable, then you need to return it using the RETURN syntax. An alternative would be to set an out parameter, depending on your needs.

I also converted your JOIN syntax from ANSI-89 to ANSI-99. It's equivalent performance, but accommodates LEFT JOINs better than the older syntax and is widely supported on other databases (making it more portable if there's a need).

Also, try:

  SELECT @output = MAX(lpiv.Value)
    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

I don't think you need the CTE...

Answer 3

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.LpivMax
FROM ItemsAndProperties ip
CROSS APPLY
dbo.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...