How to execute function in SQL with parameters

In this sql server tutorial, we will see, how to execute function in sql with parameters and we will see a few examples:

• How to execute a user-defined function in SQL with parameters
• How to execute a scalar function in SQL with parameters
• How to execute table function in SQL with parameters
• How to execute a function in SQL with multiple parameters
• How to execute a function in SQL with date parameters
• How to execute a function in SQL without parameters
• How to execute a function in SQL with a return value
• How to call a scalar function in SQL Server
• How to call a function in SQL Server SELECT statement
• How to call a function in SQL Server stored procedure
• How to call a function in SQL Server query
• How to call a function in view SQL Server
• How to call a parameterized function in SQL Server

How to execute user-defined function in SQL with parameters

A user-defined function is a function created by a user and will perform an operation specified by the user. In this section, we will learn how to execute user-defined functions in SQL with parameters.

Read: How to create functions in SQL Server Management Studio

• The following are the general steps that are required to create a function in SQL Server 2019:
  • We create a function with the CREATE FUNCTION statement.We give a name to the function.We specify input parameters along with their data types.We specify the data type of the value that the function will return.We start the body of the function with BEGIN statement.We write SQL statements inside the function body.
  • We specify the value returned by the function.
  • We end the body of the function with the END statement.
• Let us look at the syntax to create a function with input parameters.

CREATE FUNCTION function_name(input_parameter)
RETURNS return_type
AS
BEGIN
DECLARE
--Local Variables to be declared--
--SQL statements--
RETURN return_value
END

• Let us understand this with an example.
• The following function can be used to find the area of a circle:

CREATE FUNCTION [dbo].Circle(@Radius int)
RETURNS real
AS
BEGIN
DECLARE
@Area real
SET @Area=3.14*@Radius*@Radius
RETURN @Area
END

• You will see the following output if the function is successfully created:

• Now let us see, how to execute the function:
• We execute a function with the SELECT statement or the EXEC statement.
• We will execute the function with the SELECT statement as:

SELECT [dbo].Circle(5) AS Area

• The Area is the name of the column under which we will display the result. It is optional to specify the column name.
• Keep in mind to always call a function called with fully-qualified name, otherwise, it will throw an error.
• The output of the execution of the function will look like this:

• As you saw, our function returned the expected output.
• We can also execute the function with the EXEC statement.
• For this method, we have to declare a variable and in which we will store the value returned by the function.
• We execute the function with the EXEC statement as:

BEGIN
DECLARE
@area real
EXEC @area=dbo.Circle @Radius=6
print(@area)
END

Thus, in this section, we learned how to create a function in SQL server and how to execute a user-defined function in SQL Server 2019.

How to execute scalar function in SQL with parameters

Scalar functions are user-defined functions that return a single value. In this section, we will create and learn how to execute scalar functions in SQL Server 2019 with parameters.

• Firstly, let us create a scalar function. Execute the below query to create a scalar function:

CREATE FUNCTION Temperature(@Celcius real)
RETURNS real
AS
BEGIN
DECLARE
@Fahrenheit real
SET @Fahrenheit=(@Celcius*9/5)+32
RETURN @Fahrenheit
END

• The above function will take the temperature in degree Celcius as input and return the temperature in degree Fahrenheit.
• The output will be as follows:

• We will execute the scalar function with the SELECT statement.
• We pass the input parameters inside round parentheses.
• We can specify the column name under which we can display the result.
• In our case, Fahrenheit is the column name.

SELECT [dbo].Temperature(37) AS Fahrenheit

Hence, we successfully created a scalar function in SQL server. We also learned how to execute the scalar function in SQL Server 2019. You can create your own scalar functions for the practice.

Read: SQL Server Replace Function

How to execute table function in SQL with parameters

Table functions are the user-defined functions that we create to return a result in the form of table data type. Returning table data type means the function will return a set of rows. In this section, we will learn how to execute a table function in SQL Server with the use of parameters.

• We will create a table function first. Then we will execute it.
• We have created a table OrderDetails having details of the orders placed by a customer.

• We will create a function to retrieve the order details placed by a customer.
• We will pass the Customer ID of a customer as the input parameter whose order details we want to retrieve.
• We will write the query to create the table function as:

CREATE FUNCTION Orders(@CustomerID int)
RETURNS TABLE
AS
RETURN(SELECT * FROM OrderDetails WHERE [Customer ID]=@CustomerID)

• Now let us execute this function

SELECT * FROM [dbo].Orders(4110)

How to execute function in SQL with multiple parameters

In this section, you will learn how to execute functions in SQL Server 2019 with multiple parameters.

• Let us create a function that will take multiple arguments as input parameters.
• Let us take an example of the addition of three numbers.
• We will pass 3 numbers to the function and the function will return the addition of these 3 numbers.
• The code for creating a function with multiple parameters will be:

CREATE FUNCTION [dbo].AddNumbers(@Number1 real, @Number2 real, @Number3 real)
RETURNS real
AS
BEGIN
DECLARE
@Sum real
SET @Sum=@Number1+@Number2+@Number3
RETURN @Sum
END

• Call this function with the following query:

SELECT [dbo].AddNumbers(14,97,24) AS Addition

Thus, we learned how we can create a function in SQL Server 2019 with multiple parameters.

Read: SQL Server Convert String to Date + Examples

How to execute function in SQL with date parameters

We can also pass dates as parameters in the SQL Server functions. In this section, we will create a function that will take a particular date as the input parameter.

• We will be using the same table as we used in the above section.
• Suppose we want to retrieve details of the orders that were placed on a particular date.
• We can pass the date on that particular day.
• Consider the following query:

CREATE FUNCTION [dbo].OrderOnDate(@Date date)
RETURNS TABLE
AS
RETURN(SELECT * FROM [dbo].OrderDetails WHERE Date=@Date)

• This function will retrieve the details of the orders that were placed on a particular date.
• Let us execute this function.

SELECT * FROM [dbo].OrderOnDate('2020-10-05')

• Once you have executed the function, the output will look like:

Read: String or binary data would be truncated in SQL Server

How to execute function in SQL without parameters

In this section, we will learn how to execute a function without parameters in SQL Server 2019.

• Let us take an example of a simple function that does not take any parameter and prints a message.

CREATE FUNCTION [dbo].PrintMessage()
RETURNS nchar(35)
AS
BEGIN
DECLARE @Message nchar(35)
SET @Message='Welcome to SQL Server Guides'
RETURN @Message
END

• After creating this function, execute it with the following query:

SELECT [dbo].PrintMessage() AS Greet

Thus, we learned how we can create and execute functions without parameters.

Read: SQL Server Convert Datetime to date

How to execute function in SQL with return value

In SQL Server, we create functions to perform a specific task and return a value. A function must return a value.

A function can return multiple types of values. For instance, text data, numeric data, boolean data, date and time data, table data, etc.

In this section, we will learn how we can use functions to return values with the help of an example.

• We will create a function that will return the area of a rectangle.
• The function will take two integer parameters i.e Length and Breadth of the rectangle
• We use the RETURN statement to return the value.
• The function is created as follows:

CREATE FUNCTION Rectangle(@length int, @breadth int)
RETURNS bigint
AS
BEGIN
DECLARE
@Area bigint
SET @Area=@length*@breadth
RETURN @Area
END

• Now execute the created function. We will give 25 and 20 as input values.

SELECT [dbo].Rectangle(25,20) AS Area

• The rectangle function gave 500 as output. Hence, the rectangle function returned the expected value.

How to call scalar function in SQL Server

In this section, we will learn how to call scalar function in SQL Server 2019.

We call the scalar function using the SELECT statement. This gives the result in the form of a column. We can give the resultant column a name while calling the function.

Let us understand how to call scalar function in SQL Server with an example:

• We will create a function to find the perimeter of a triangle.
• The function will accept 3 integer inputs as the 3 sides of the triangle.

CREATE FUNCTION [dbo].Triangle_Perimeter(@side1 int, @side2 int, @side3 int)
RETURNS int
AS
BEGIN
DECLARE
@Perimeter int
SET @Perimeter=@side1+@side2+@side3
RETURN @Perimeter
END

• Now call the function with the SELECT statement.

SELECT  [dbo].Triangle_Perimeter(12,15,19) AS Perimeter

• In the above example, the result will be displayed under the Perimeter column.

Hope you have understood how to call a scalar function in SQL Server.

Read: SQL Server Convert Function

How to call a function in SQL Server SELECT statement

In this section, we will learn how we can call a function in the SQL Server SELECT statement. In SQL Server, we have a number of built-in functions that we can use. We will use some of them to show how these functions can be called in the query.

• Consider the following table on which we will use the SUM function.

• We will use the SUM() function in the SELECT query to retrieve the sum of all the amounts in the Amount column.

SELECT SUM(Amount) from OrderDetails

We can various other functions like MIN() to retrieve the minimum value from a list of values, MAX() to retrieve the maximum value, AVG() to find the average of a group of values, and much more.

Read: SQL Server Convert Datetime to String + Examples

How to call function in SQL Server stored procedure

We can call a function inside a stored procedure in SQL Server. In this section, we will learn how to call a function in stored procedure in SQL Server 2019.

• We will use a table named Login.
• Let us create a stored procedure that we will use to insert a new row into the Login table.
• The stored procedure will take two parameters and pass them to the checkDetails function.

Use [master]
GO
CREATE PROCEDURE InsertRow @user nchar(20), @pass nchar(20)
AS
BEGIN
DECLARE
@Flag int
EXEC @Flag=[dbo].checkDetails @user, @pass
	IF @Flag=0
		BEGIN
		INSERT INTO [dbo].Login(Username, Password)
		VALUES(@user,@pass)
		PRINT('Row Inserted')
		END
	ELSE
	IF @Flag=1
	PRINT('User Already Exists')
END

• The checkDetails function takes the two input values and checks if the record is available in the Login table.
• If the record is already available in the Login table, the function will return the value 1 else, if the record is not available in the table then the function will return the value 0.

Use [master]
GO
CREATE FUNCTION checkDetails(@Username nchar(20), @Password nchar(20))
RETURNS int
AS
BEGIN
DECLARE
@Flag int
IF EXISTS(SELECT * FROM Login where Username=@Username and Password=@Password)
	SET @Flag=1
ELSE
	SET @Flag=0
RETURN(@Flag)
END

• Now let’s come to the stored procedure again.
• The stored procedure will check the value returned by the checkDetails function.
• If the value is 0, i.e. the record is not available in the table, the procedure will insert a new row using the arguments provided during the execution.
• If the value is 1, i.e. the record is already available in the table, the procedure will not insert any row and returns a message.
• Now let us execute the stored procedure InsertRow.

USE [master]
GO
EXEC [dbo].InsertRow 'Jenifer','Jenifer@123'

• This record is not stored in the table. So, a new record will be inserted.

• Now execute the same query. This time the record is already stored in the table.

Hence, you might have learned how you can call a function inside a stored procedure. You can try multiple examples to understand this concept.

How to call function in SQL Server query

You can call any function inside a SQL Server query. We will learn with an example how to call a function inside a SQL Server query.

• Let us consider the following OrderDetails table:

• Suppose you want to store the total sales of a salesman along with the salesman ID into a new table.
• You can create a function to retrieve data from the OrderDetails table and store them in the Sales table.
• Let us create the function:

USE [master]
GO
CREATE FUNCTION dbo.RetrieveSales()
RETURNS table
AS
RETURN(SELECT [Salesman ID], SUM(Amount) AS [Total Sales] FROM dbo.OrderDetails GROUP BY [Salesman ID])

• This function will return a table consisting of the Salesman ID and their total sales.
• Now we will use this function inside the INSERT query.
• We will store this retrieved to a newly created Sales table with the INSERT query.
• The Sales table consists of only two records i.e. Salesman ID and Total Sales and is initially empty.

USE [master]
GO
INSERT INTO dbo.Sales
SELECT * FROM dbo.RetrieveSales()

• After using this query, you can see that some rows are inserted in the Sales table.

Hence, with this example, you might have learned about how to use any user-defined function inside a query. You can play with the queries to explore more ways of using the functions inside a query.

How to call function in view SQL Server

We can call a function while creating a view. A view is a virtual table that does not exist in the database physically. Sometimes we have to call a function inside a view. We will understand this with an example.

• We have a function [dbo].[getEmpInfo] that will retrieve all the details of a table Employee.
• You can have a look at the function definition:

USE [master]
GO

CREATE FUNCTION [dbo].[getEmpInfo]()
RETURNS TABLE
AS
	RETURN(SELECT * FROM [dbo].Employees)

• You will need a table Employee for this to work.
• Let us call this function inside a view.

USE [master]
GO
CREATE VIEW myView
AS
	SELECT [Employee ID], Name, Department, Age, Salary
	FROM dbo.getEmpInfo()

• Now see the contents of the view with the following query:

USE [master]
GO
SELECT * FROM [dbo].myView

Thus, in this section, we learned how we can call a function inside a view in SQL Server.

How to call parameterized function in SQL Server

In this section, we will learn how we can call a paramterized function in SQL Server 2019.

A parameterized function in SQL Server is a function that has some parameters defined. The parameters are the values on which a function works. We give some values to the function so that the function performs a specific task.

• For example, consider the following parameterized function to find the circumference of a circle:

CREATE FUNCTION [dbo].findCircumference(@Radius real)
RETURNS real
AS
BEGIN
DECLARE
@Circumference real
SET @Circumference=2*3.14*@Radius
--PI= 3.14
RETURN @Circumference
END

• This function has a parameter @Radius of real data type i.e. radius of a circle.
• Once the function is created, you can call it from with the SELECT statement or with the EXEC statement.

USE [master]
GO
DECLARE
@Circumference real
EXEC @Circumference =[dbo].findCircumference 6
PRINT(@Circumference)

• We stored the value returned from a function in a variable @Circumference and printed it.
• You can also call the function with the SELECT statement.

USE [master]
GO
SELECT [dbo].findCircumference(6) AS Circumference

You may like the following sql server tutorials:

• SQL Server Substring Function

Hence, you might have understood how to call a parameterized function in SQL Server.

• How to execute a user-defined function in SQL with parameters
• How to execute scalar function in SQL with parameters
• How to execute table function in SQL with parameters
• How to execute function in SQL with multiple parameters
• How to execute function in SQL with date parameters
• How to execute function in SQL without parameters
• How to execute function in SQL with return value
• How to call scalar function in SQL Server
• How to call function in SQL Server SELECT statement
• How to call function in SQL Server stored procedure
• How to call function in SQL Server query
• How to call function in view SQL Server
• How to call parameterized function in SQL Server