User-defined Functions In SQL Server Part 1

User-defined functions also called UDF. In SQL server there are 3 different types of User-Defined functions

a) Scalar function.

b) Inline table-valued functions.

c) Multi-statement table-valued functions.

You can find these functions folder in SQL Server Management studio see below image




Scalar Functions

The scalar function is a function that may or may not have parameters but always return a single value. The return type of scalar function can be any data-type except text, image, cursor, and timestamp. The scalar function may or may not have parameters but it should return a single value.

Why this function called scalar function because it returns a single value.

Example:

In-built function with parameter

Code:

select ABS(-1000) // op 1000

In-built function with 0 parameter

Code:

Select GetDate() //

General Syntax for Scalar function:

Code:

CREATE FUNCTION [schema_name.]function_name (parameter_list)

RETURNS  return-data-type AS

BEGIN

statements

RETURN value

END

How to create User-defined Scalar function in the SQL server?

Code:

Create function GetAge(@bdate nvarchar(100))

returns int

as

begin

declare @age Int

set @age=DATEDIFF(YEAR,cast(@bdate as date),Getdate());

return @age;

end

In the above function, we calculate the age.

To call this function if you only use

Select function-name(parameter); then it will give an error to overcome this error we need to give a fully qualified name or 2 part name or at-least database owner and name append before the function name. In our case, dbo is a database owner.

Code:

 select dbo.GetAge('12/01/1988'); 

Second example: you can use this in select clause as shown below

Code:

select id,first_name,last_name,dbo.getage(dateofbirth) from studentTable

You can do this in store-procedure also but you can not use stored procedure in select clause and where clause.


How to alter function in the SQL server?

Code:

ALTER function [dbo].[GetAge](@bdate nvarchar(100))
returns int
as
begin
declare @age Int
set @age=DATEDIFF(YEAR,cast(@bdate as date),Getdate());
return @age;
end

The alter keyword needs to use instead of create statement and whole function you need to write here.

How to delete or remove a function from the SQL server?


You need to use a drop keyword and function name to remove function in the SQL server. Also, a fully qualified name or database administrator name needs to use along with function name if you use only function name then it will give an error.

Code:

drop function dbo.getage