Problem with Function - SQL Server Forums

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

Transact-SQL (2005)

Problem with Function

Author

Topic

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-11 : 16:05:46

Hello all.

Here is my issue:

I have 28 rows of data in a table with columns Name and Cost.

All I want are the distinct names and the total of the resulting Cost column.

So, if the table has data

Name Cost
--------- ----------
Op1 0.17
Op2 0.18
Op3 0.19
Op3 0.19
Op2 0.18

I want to see a total of .54.

I couldn't figure out how to do this with select, so I wrote a function, but the function is giving me trouble.

I will show the function in the next post.

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-11 : 16:06:45

OK, here is my code:

My function will not execute. I get an error saying MUST DECLARE Scalar Value @resultSet.

Here is the code:

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

I looked up several syntax examples on the net, but this one doesn't work.

I hope I provided enough info.

Thanks for any help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 16:23:07

You are probably getting that error because of this line of code: SELECT @pricePerPiece=Sum(@resultSet.Cost)

You'd want that to become:

SELECT @pricePerPiece=Sum(Cost)
FROM @resultSet

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-11 : 16:25:26

I am bowing in reference right this very moment, O Goddess of SQL!

This was only my second time writing a complex function. I knew I was overlooking something simple.

Well, that little correction compiled great!

Thank you very much!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-11 : 16:27:33

No need to praise me via written text, I accept praise via paypal.

Glad to have helped so quickly! I just did a brief check of your UDF and that's what stood out as wrong.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-12 : 01:54:43

Functions? Why not just a derived table?

DECLARE	@Sample TABLE ([Name] VARCHAR(10), Cost SMALLMONEY)

INSERT	@Sample
SELECT	'Op1', 0.17 UNION ALL
SELECT	'Op2', 0.18 UNION ALL
SELECT	'Op3', 0.19 UNION ALL
SELECT	'Op3', 0.19 UNION ALL
SELECT	'Op2', 0.18

SELECT	SUM(Cost) AS Cost
FROM	(
		SELECT DISTINCT	[Name],
				Cost
		FROM		@Sample
	) AS d

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources