Passing Expressions to Function Parameters in SQL Server

Q

Can You Pass Expressions to Function Parameters in SQL Server Transact-SQL?

✍: FYIcenter.com

A

Can you pass expressions to stored procedure parameters? The answer is yes.

When executing functions, input values can be written as expressions. But the resulting value data type must match the parameter. The tutorial exercise below shows you how input values should be specified:

USE FyiCenterData;
GO

CREATE FUNCTION Area(@radius REAL)
   RETURNS REAL
   AS BEGIN
      RETURN 3.14*@radius*@radius;
   END;
GO

-- Input value data matches the parameter
PRINT 'Area of a circle: '+STR(dbo.Area(1.5),9,3);
GO
Area of a circle:     7.065

-- Input value data does not match the parameter
PRINT 'Area of a circle: '+STR(dbo.Area('1.5'),9,3);
GO
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Area'.

-- Expressions are allowed
PRINT 'Area of a circle: '+STR(dbo.Area(1.0+0.5),9,3);
GO
Area of a circle:     7.065

 

Using User Defined Functions in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2016-12-18, 270👍, 0💬