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
![]()