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, 758👍, 0💬

sys.sql_modules - Getting User Defined Function Definitions Back in SQL Server
How To Get the Definition of a User Defined Function Back in SQL Server Transact-SQL? If you want get the definition of an existing user defined function back from the SQL Server, you can use the system view called sys.sql_modules, which stores definitions of functions, stored procedures, and views.... 2016-12-18, 1268👍, 0💬

DEFAULT - Providing Default Values to Function Parameters in SQL Server
How To Provide Default Values to Function Parameters in SQL Server Transact-SQL? If you add a parameter when creating a stored procedure, you can provide a default value so that the execution statement is not required to pass input value to this parameter: To define a default value to a parameter wh... 2016-12-18, 1166👍, 0💬

Creating User Defined Functions with Parameters in SQL Server
How To Create User Defined Functions with Parameters in SQL Server Transact-SQL? Very often, you need to create a function with one or more parameters so that the function can be more generic. You only supply values to those parameters at the time of executing the function. User defined functions wi... 2016-12-18, 878👍, 0💬

Generating CREATE FUNCTION Scripts on Existing Functions in SQL Server
How To Generate CREATE FUNCTION Script on an Existing Function in SQL Server Transact-SQL? If you want to know how an existing user defined function was created, you can use SQL Server Management Studio to automatically generate a "CREATE FUNCTION" script The following tutorial shows you how to do t... 2016-12-18, 821👍, 0💬

Passing Values to User Defined Function Parameters in SQL Server
How To Provide Values to User Defined Function Parameters in SQL Server Transact-SQL? If a user defined function is created with parameters, you need pass values to those parameters when calling the function with one of two formats listed below: expression... function_name(value_1, value_2, ... valu... 2016-12-18, 816👍, 0💬

Passing Expressions to Function Parameters in SQL Server
Can You Pass Expressions to Function Parameters in SQL Server Transact-SQL? 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 exerc... 2016-12-18, 759👍, 0💬

"ALTER FUNCTION" - Modifying Existing Functions in SQL Server
How To Modify an Existing User Defined Function in SQL Server Transact-SQL? If you find a mistake in an existing function previously created, you can drop (delete) it and create it again correctly. But dropping a function may affect other database objects who are depending on this function. So the b... 2016-12-18, 749👍, 0💬

"RETURNS TABLE" - Creating Inline Table-Value Functions in SQL Server
How To Create an Inline Table-Valued Function in SQL Server Transact-SQL? To create an inline table-valued function, you need to use the "RETURNS TABLE" clause in the "CREATE FUNCTION" statement. There should be no function body, except for a RETURN statement with a SELECT subquery: An inline table-... 2016-12-18, 695👍, 0💬

Syntaxes of Creating Table-Valued Functions in SQL Server
How Many Ways to Create Table-Valued Functions in SQL Server Transact-SQL? SQL Server supports two syntaxes of creating table-valued functions: 1. Inline Table-valued Functions - A table-valued function created with a single SELECT statement: CREATE FUNCTION function_name( @parameter_1 data_type, @p... 2016-12-18, 693👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.