September 17, 2011 at 1:39 pm
hi
i m trying to create simple udf scalar function.
CREATE FUNCTION [dbo].[Total] (
@AmtINT
)
RETURNS int
AS
BEGIN
RETURN
sum(@Amt)
END
it gives me error like
Only a scalar expression may be specified as the argument to the RETURN statement.
Any Help?
September 17, 2011 at 1:51 pm
September 17, 2011 at 2:21 pm
goodguy (9/17/2011)
Do the calculation earlier in the function, assign it to another temp variable @ANS and return that @ANS.
Better still, write a set-based T-SQL query instead of trying to use a function.
Functions in SQL Server (aside from in-line table-valued functions) are pretty evil from a performance point of view. Scalar or multi-statement functions that do data access are even worse. The problem is that the function is evaluated in a new T-SQL context for every single row it operates on.
In principle, there's no particular reason for functions to be so slow, but the implementation is poor. Vote for Microsoft to improve this sad situation at:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 18, 2011 at 12:00 am
Hi,
If you want to write function then you need to do small changes in T-SQL.
CREATE FUNCTION [dbo].[Total]
(
@Amt INT
)
RETURNS INT
AS
BEGIN
DECLARE @TSUM INT
SELECT @TSUM = SUM(@Amt)
RETURN @TSUM
END
Shatrughna
September 18, 2011 at 6:57 am
shatrughna (9/18/2011)
Hi,If you want to write function then you need to do small changes in T-SQL.
CREATE FUNCTION [dbo].[Total]
(
@Amt INT
)
RETURNS INT
AS
BEGIN
DECLARE @TSUM INT
SELECT @TSUM = SUM(@Amt)
RETURN @TSUM
END
:blink: What would be the point of that function?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 18, 2011 at 8:42 am
Hi thank you for ur help
the output should be print statement
for example total quantity sold for each product and return a print statement that reads (as an example) a total of 7 monitor(s) were sold you can use any looping construct.
September 18, 2011 at 10:18 am
Thank you everybody for your response
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply