How to Use the WITH ENCRYPTION Option for Functions in SQL Server: A Comprehensive Guide

You can encrypt a function text. Once, encrypted, you cannot view the text of the function, using sp_helptext system stored procedure. If you try to, you will get a message stating 'The text for object is encrypted.' There are ways to decrypt, which is beyond the scope of this video.

We have a Employee table like below. We will use this table to understand this concept.

Id	Name	DateOfBirth	Gender	DepartmentId
1	Rambo	1980-12-30 00:00:00.000	Male	1
2	Roma	1982-09-01 12:02:36.260	Female	2
3	Inza	1985-08-22 12:03:30.370	Male	1
4	Sara	1979-11-29 12:59:30.670	Female	3
5	Azam	1978-11-29 12:59:30.670	Male	1

Scalar Function without encryption option:

Create Function fn_GetEmployeeNameById(@Id int)
Returns nvarchar(20)
as
Begin
 Return (Select Name from Employees Where Id = @Id)
End

To view text of the function:

sp_helptex fn_GetEmployeeNameById

Now, let's alter the function to use WITH ENCRYPTION OPTION

Alter Function fn_GetEmployeeNameById(@Id int)
Returns nvarchar(20)
With Encryption
as
Begin
 Return (Select Name from Employees Where Id = @Id)
End

Now try to retrieve, the text of the function, using sp_helptex fn_GetEmployeeNameById. You will get a message stating 'The text for object 'fn_GetEmployeeNameById' is encrypted.'

Prerequisite Code:

 CREATE TABLE Employees
(
	Id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	Name NVARCHAR(50),
	DateOfBirth DATETIME,
	Gender NVARCHAR(50),
	DepartmentId INT
);

INSERT INTO Employees
VALUES ('Rambo', '1980-12-30', 'Male', 1)

INSERT INTO Employees
VALUES ('Roma', '1982-09-01 12:02:36.260', 'Female', 2)

INSERT INTO Employees
VALUES ('Inza', '1985-08-22 12:03:30.370', 'Male', 1)

INSERT INTO Employees
VALUES ('Sara', '1979-11-29 12:59:30.670', 'Female', 3)

INSERT INTO Employees
VALUES ('Azam', '1978-11-29 12:59:30.670', 'Male', 1)

Select * from  Employees