Programming in MS SQL Server 2012

WITH ENCRYPTION Option



The following example describes CREATE PROCEDURE statement using the minimum required syntax with one INPUT parameters to return specified employee(s) information based on given parameter unique pattern data value.

WITH ENCRYPTION clause is defined in this stored procedure to encrypt stored procedure’s source code. The encrypted stored procedure cannot be decrypted and cannot be viewed by anyone, including stored procedure owner or database administrator also.

-- Example 122 --

 

USE EBusiness

GO

 

IF EXISTS (SELECT *

           FROM sysobjects

           WHERE name = N'dbo.usp_EmployeePhoneListEncryption' AND type = 'P')

           DROP PROCEDURE dbo.usp_EmployeePhoneListEncryption

GO

 

CREATE PROCEDURE dbo.usp_EmployeePhoneListEncryption

@Phone varchar(50)

WITH ENCRYPTION

AS

SELECT

   EMP.Emp_Code,

   EMP.Emp_Name,

   EMP.Phone,

   EMP.Dept_Code,

   DEPT.Dept_Name FROM EMP

INNER JOIN DEPT

ON EMP.Dept_Code = DEPT.Dept_Code

WHERE EMP.Phone LIKE @Phone


Query Output Screen



The following Transact-SQL statements are used to retrieve employee(s) information based on given input parameter unique pattern data values.

-- Example 123 --

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneListEncryption '%'

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneListEncryption '10%'

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneListEncryption '104%'


Query Output Screen



* * * * *


Email Your Comment To AUTHOR