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.
Views: 14843 | Post Order: 83