CODEWITHSUNDEEP
sqlsql-server
user131983
user131983

Reputation: 3927

Syntax Errors in User Defined Function in SQL

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

Upvotes: 0

Views: 52

Answers (1)

siride
siride

Reputation: 209445

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.

Upvotes: 2

Related Questions