In this article, I am going to create a user defined function to get the next employee id. After creating the function, I can use it on the table to get the next employee id automatically, based on the identity value.

Function in SQL Server is a database object. It is a set of SQL statements that accepts the input parameters, performs calculations on them and return the result. The return type of a function can be a single value or a SQL table. An SQL function cannot be used to insert, update and delete records in the database.

--Create getEmpId function script

ALTER FUNCTION [dbo].[getEmpId](@ID varchar(10))

RETURNS varchar(10)

AS

BEGIN

DECLARE @EmpId varchar(10)

Set @EmpId='EMP00'+@Id

RETURN @EmpId

END

--Create table to use function

CREATE TABLE [dbo].[Employee]

(

[Id] [int] IDENTITY(1,1) NOT NULL,

[Emp_Id] AS ([dbo].[getEmpId]([Id])),

[Emp_Name] [varchar](10) NOT NULL

)

--Insert data in table. It will automatically insert in Emp_Id column

insert into Employee(Emp_Name) values('Abc')

insert into Employee(Emp_Name) values('Xyz')

--Select records from above table

select * from Employee