---

Search posts under SQL Server:

---

Create a User defined scalar function in SQL Server

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.

CRETAE FUNCTION [dbo].[GetFullName]
(
       -- Add the parameters for the function here
       @PersonalDetailsId 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 PersonalDetails WHERE
PersonalDetailsId = @PersonalDetailsId

       -- Return the result of the function
       RETURN @FullName

END

In above case, we are creating a function named “GetFullName” with @PersonalDetailsId as parameter. 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 PersonalDetails table based on @PersonalDetailsId 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

The value of @fullName will be printed in the Message tab in the result panel.