SQL Create Function

Question

Wants to create a function of the average product standardcost for the same color, and using 'red' to test . But NULL returned. If only run the SELECT,FROM,WHERE,GROUP BY, it will work file and return a value.

Here's my code :

CREATE FUNCTION avgv (@Color CHAR)
RETURNS CHAR 
AS 
BEGIN
        DECLARE @AVG CHAR ;
        SET @AVG = (SELECT P.Color  AVG(P.StandardCost)  
        FROM AdventureWorksLT.SalesLT.Product P
        WHERE P.Color = @Color
        GROUP BY Color)

        Return @AVG

END
GO
SELECT dbo.avgv('red')

Please let me know how to fix, thanks!

You forgot to specify length in both input parameter and returned result. Also, I assume, you should use VARchar. CHAR same as VARCHAR withoud explicitly defined length means length=1 (one symbol). — IVNSTN
– IVNSTN, Commented Nov 3, 2016 at 8:06
And as already mentioned - your result does nor look like character since you are computing AVG. — IVNSTN
– IVNSTN, Commented Nov 3, 2016 at 8:08

M. Rezaeyan · Accepted Answer · 2016-11-03 09:15:20Z

1

Input parameter: char -> varchar(100)
Returns -> char -> int for integer result /decimal(18, 2) for float result
Remove Group By

Function

CREATE FUNCTION avgv (@Color varchar(100))
RETURNS real
AS 
BEGIN
        DECLARE @AVG real;
        SET @AVG = (SELECT AVG(ISNULL(P.StandardCost, 0))  
        FROM AdventureWorksLT.SalesLT.Product P
        WHERE P.Color = @Color)

        Return @AVG

END
GO
SELECT dbo.avgv('red')

Procedure

CREATE PROCEDURE avgv (@Color varchar(100))
AS 
BEGIN
        SELECT AVG(ISNULL(P.StandardCost, 0))  
        FROM AdventureWorksLT.SalesLT.Product P
        WHERE P.Color = @Color
END

edited Nov 3, 2016 at 9:15

answered Nov 3, 2016 at 7:31

M. Rezaeyan

4081 gold badge3 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sun Louie Over a year ago

But, still NULL returned.

M. Rezaeyan Over a year ago

SELECT AVG(ISNULL(P.StandardCost, 0))

Sun Louie Over a year ago

this works after using RETURN REAL, also, can you help me to convert it to the create procedures? I want to call it from other language, so we need procedure can hold the parameter(COLOR) to be entered.

Collectives™ on Stack Overflow

SQL Create Function

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related