« Would a group of natives run by a chief be considered a monarchy?
Why does this error show up when I try accesing a website? »

Help with SQL Function?

Say, for example, I have a table with 28 items. Each item has a cost and a name. I want each distinct item and a total of the tables cost.

I tried a function but I am getting some errors.

Here is the code:

CREATE FUNCTION dbo.GetPricePerPiece
(
@orderID nvarchar(50)
)
RETURNS float
AS
BEGIN
DECLARE @resultSet table
DECLARE @pricePerPiece float
SELECT @resultSet=SELECT DISTINCT [Name], Cost FROM OperationsInProgress WHERE (OrderID = @orderID) GROUP BY Name, Cost
SELECT @pricePerPiece=Sum(@resultSet.Cost)
RETURN @pricePerPiece
END

It says must declare scalarValue @resultSet. I can’t declare a table as a scalar value?

Here is the code I tried before I tried to write the function

SELECT DISTINCT Name, Cost
FROM OperationsInProgress
WHERE (OrderID = ‘61316′)
GROUP BY Name, Cost

But I can’t seem to get the Sum of the cost columns.

Any help will be appreciated. Thanks!
I updated my function after looking at some more syntax:

CREATE FUNCTION dbo.GetPricePerPiece
(
@orderID nvarchar(50)
)
RETURNS float
AS
BEGIN
DECLARE @resultSet TABLE
(
[Name] varchar(50),
Cost float
)
DECLARE @pricePerPiece float
INSERT INTO @resultSet ([Name],Cost) SELECT DISTINCT [Name], Cost FROM OperationsInProgress WHERE (OrderID = @orderID) GROUP BY [Name], Cost
SELECT @pricePerPiece=Sum(@resultSet.[Cost])
RETURN @pricePerPiece
END

But i still get the MUST DECLARE SCALAR VARIABLE @resultSet
I tried that, but I get multiple rows of data, and the cost column has a sum of each type.
Dave, that was the correct answer. That and the fact that I forgot to put FROM @resultSet. Thanks for the response.

Well, I learned something new today: triggers and udfs!

I think you have incorrect syntax around the @resultSet table variable. Try something like this

SELECT @pricePerPiece = SUM(Cost)
FROM @resultSet

instead of

SELECT @pricePerPiece = SUM(@resultSet.Cost)

This entry was posted on Saturday, November 14th, 2009 at 1:17 am and is filed under select distinct. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Help with SQL Function?”

  1. I think all you are really missing is the SUM operator :

    SELECT DISTINCT Name, SUM(Cost)
    FROM OperationsInProgress
    WHERE (OrderID = ‘61316′)
    GROUP BY Name

    You need to do the GROUP BY on the item that is not part of the aggregate function. Try that and see how it goes – should work, as long as the Cost column is a numeric datatype.
    References :

  2. I think you have incorrect syntax around the @resultSet table variable. Try something like this

    SELECT @pricePerPiece = SUM(Cost)
    FROM @resultSet

    instead of

    SELECT @pricePerPiece = SUM(@resultSet.Cost)
    References :

Leave a Reply