Syntax Errors in User Defined Function in SQL

Question

I'm unable to understand why this code to calculate the Volume of a cube results in the Error Procedure or function 'CubicVolume' expects parameter '@CubeLength', which was not supplied..

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(
@CubeLength decimal(4,1), 
@CubeWidth  decimal(4,1),
@CubeHeight decimal(4,1)
)
RETURNS decimal(12,3)
AS
   BEGIN
RETURN(@CubeLength * @CubeWidth * @CubeHeight)
END

I then try to Execute it using EXEC CubicVolume, which is how one would Execute a Stored Procedure. I know some Syntax is wrong, but I'm unable to tell where.

Thank You

What RDBMS are you using? Please add an appropriate tag to the question: mysql, oracle, sql-server, etc. Didn't you see the red box reminding you to do this when you wrote the question? — Barmar
– Barmar, Commented Nov 10, 2014 at 0:29
Did you pass any parameters when you executed your function? — rikitikitik
– rikitikitik, Commented Nov 10, 2014 at 0:32
BZZZZZT!!! doesn't work! Please don't write "doesn't work". Please explain why it doesn't work. Error? unexpected outcome? Please explain. — Nick.Mc
– Nick.Mc, Commented Nov 10, 2014 at 1:06
@Nick.McDermaid: in this case, it's fairly obvious what the problem is, though in general, I am annoyed by "doesn't work" (both on this site and in my day job). — siride
– siride, Commented Nov 10, 2014 at 1:51

siride · Accepted Answer · 2014-11-10 00:35:01Z

I'm assuming SQL Server.

You defined a scalar function, but are trying to invoke it like a stored procedure. The two are not the same. Stored procedures are basically batches of SQL statement that execute sequentially, and optionally send resultsets back to the client. Scalar functions behave like built-in functions (e.g., LEN(), CHARINDEX(), ABS(), etc.). That is, they belong in an expression inside a SELECT statement, or moral equivalent. So you need to use your function in a SELECT statement where you'd use a column or an expression. Examples:

SELECT dbo.CubicVolume(12, 5, 3) AS vol
-- result is: 180

SELECT CubeName, l, w, h FROM dbo.SomeTable WHERE dbo.CubicVolume(l, w, h) > 200
-- result could be something like: MyCube, 10, 10, 10 etc.

Basically, it's equivalent to using a mathematical expression based on columns or constants. You cannot use EXEC on it.

Collectives™ on Stack Overflow

Syntax Errors in User Defined Function in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related