functions tsql - 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

General SQL Server Forums

New to SQL Server Programming

functions tsql

Author

Topic

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-16 : 14:45:43

ive created a function OrdersAmount that will accept two parameters: the start date and the end
date, and that will return the total order amount between those two dates. i am testing
the function by listing the rows from the table and the function result.
but i am getting some syntax errors

create function dbo.OrdersAmount(@startdate datetime, @enddate datetime)
returns int
as
begin
declare @return int
set @return = (select sum(od.UnitPrice * od.OrderQty * (1-od.unitpriceDiscount)) as totalorderamount from Orderdetails od
inner join orders o on od.salesorderid=o.salesorderid
where orderdate between @startdate and @enddate)
return @return
end

Msg 8117, Level 16, State 1, Procedure OrdersAmount, Line 6
Operand data type nvarchar is invalid for multiply operator.

would anybody know why?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-16 : 19:38:22

your column UnitPrice or OrderQty is a nvarchar ? ?

KH
[spoiler]Time is always against us[/spoiler]

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-16 : 20:38:58

unit price and order qty are both nvarchars.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-16 : 20:43:04

then use convert() or cast() to convert to numeric before performing any calculation

KH
[spoiler]Time is always against us[/spoiler]

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-16 : 20:59:14

the function appears to work fine now but running this test gave this error:

SELECT dbo.OrdersAmount ('11/3/2006 12:00:00 AM', '11/4/2006 12:00:00 AM')

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

would you be able to see where the function went wrong?

create function OrdersAmount(@startdate datetime, @enddate datetime)
returns FLOAT
as
begin
DECLARE @SALESORDERID NVARCHAR
SET @SALESORDERID = (SELECT DISTINCT SALESORDERID FROM ORDERS
WHERE ORDERDATE BETWEEN @STARTDATE AND @ENDDATE)
RETURN @SALESORDERID
DECLARE @SALESORDERDETAILID NVARCHAR
SET @SALESORDERDETAILID = (SELECT DISTINCT A.SALESORDERDETAILID FROM ORDERDETAILS A
WHERE A.SALESORDERID = @SALESORDERID)
DECLARE @RETURN FLOAT
SET @RETURN = (SELECT SUM(CAST(ORDERQTY AS INT) * CAST(UNITPRICE AS FLOAT) * (1-CAST(UNITPRICEDISCOUNT AS FLOAT)))
FROM ORDERDETAILS A
WHERE A.SALESORDERDETAILID = @SALESORDERDETAILID)
return @RETURN
end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-16 : 21:10:24

quote:
SET @SALESORDERDETAILID = (SELECT DISTINCT A.SALESORDERDETAILID FROM ORDERDETAILS A
WHERE A.SALESORDERID = @SALESORDERID)

the sub query here return more than one rows for the specified condition. What do you want to do here ?

KH
[spoiler]Time is always against us[/spoiler]

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-16 : 22:46:29

I am creating a function OrdersAmount that will accept two parameters: the start date and the end date, and that will return the total order amount between those two dates.

and using the select statement to test the function by listing the rows from the table and the function result.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-17 : 00:49:19

1. Why are you using nvarchar for UnitPrice & Qty ? Don't you think it will be better to store these data using numeric data type rather than string data type ?

2. Your original function is using proper INNER JOIN. Why sudden change ?

3. Do you know that float is approximate value ? For dollar amount you should use money or numeric or decimal data type.

CREATE FUNCTION ORDERSAMOUNT
(
	@STARTDATE 	datetime, 
	@ENDDATE 	datetime
)
RETURNS float
AS
BEGIN
	DECLARE @RETURN float

	SELECT	@RETURN = SUM(CAST(ORDERQTY AS int) * CAST(UNITPRICE AS float) * (1 - CAST(UNITPRICEDISCOUNT AS float)
	FROM	ORDERDATE A
		INNER JOIN ORDERDETAILS B	ON	A.SALESORDERID	= B.SALESORDERDETAILID
	WHERE	A.ORDERDATE BETWEEN @STARTDATE AND @ENDDAT

	RETURN @RETURN
END

KH
[spoiler]Time is always against us[/spoiler]

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-17 : 10:03:17

Thank you khtan,
do you mean that instead of returning float, i should return money instead?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-17 : 10:20:01

quote:
Originally posted by lisa_baria03

Thank you khtan,
do you mean that instead of returning float, i should return money instead?

NO. I mean you should use either money or numeric data type for your column UnitPrice, OrderQty and not nvarchar

KH
[spoiler]Time is always against us[/spoiler]

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-17 : 10:31:54

thanks ktan

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-19 : 16:39:50

quote:
Originally posted by khtan
NO. I mean you should use either money or numeric data type for your column UnitPrice, OrderQty and not nvarchar

khtan, why on Earth would someone think that the MONEY data type should be used to hold amounts of MONEY to charge for something (like UnitPrice)?

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-19 : 19:33:40

why can't money as datatype be used?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 22:01:07

quote:
Originally posted by KenW

quote:
Originally posted by khtan
NO. I mean you should use either money or numeric data type for your column UnitPrice, OrderQty and not nvarchar

khtan, why on Earth would someone think that the MONEY data type should be used to hold amounts of MONEY to charge for something (like UnitPrice)?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-19 : 22:03:16

quote:
Originally posted by lisa_baria03

why can't money as datatype be used?

Yes you can use money data type for unit price if you wanted a 4 decimal places else just use decimal(10,2) and define the precision and scale.

And for OrderQty, i would assume it is an integer value (use int data type) unless your OrderQty contains decimal places (use decimal data type) ?

KH
[spoiler]Time is always against us[/spoiler]

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-19 : 22:16:16

its nvarchar datatype

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-19 : 23:20:47

That's why people tell you better to change it.

lisa_baria03
Yak Posting Veteran

60 Posts

Posted - 2007-11-20 : 12:04:54