Create User defined scalar function in SQL Server

Updated on August 30, 2015     Kisan Patel

This tutorial will show you how to create a User defined scalar function in SQL Server?

To create a scalar function, right click the Scalar-valued Functions option from Programmability Functions.

This opens up a function template in the query window. Now change placeholders for name, data type, variables and return variable etc.

	CREATE FUNCTION [dbo].[GetFullName]
	(
	-- Add the parameters for the function here
	@EmployeeID int
	)
	RETURNS varchar(50)
	AS
	BEGIN
		-- Declare the return variable here
		DECLARE @FullName varchar(50)
		-- Add the T-SQL statements to compute the return value here
		SELECT @FullName = FirstName + ' ' + LastName FROM Employees WHERE
		EmployeeID = @EmployeeID
		-- Return the result of the function
		RETURN @FullName
	END

In above case, we are creating a function named “GetFullName” with @EmployeeID asparameter. The function returns a data that is of “varchar(50)”.

Next, we are declaring a @FullName variable and setting its value in the SELECT statement from the Employees table based on @EmployeeID parameter value passed in. Then returning the @FullName value.

To call this function, we can use either SELECT statement or set its value into a variable like below

-- First method
SELECT dbo.GetFullName(4)
-- Second method
DECLARE @fullName varchar(50)
SET @fullName = dbo.GetFullName(4)
print @fullName