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

IF EXISTS (SELECT *

FROM sysobjects

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

DROP PROCEDURE dbo.usp_EmployeePhoneListEncryption

CREATE PROCEDURE dbo.usp_EmployeePhoneListEncryption

@Phone varchar(50)

WITH ENCRYPTION

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

* * * * *

Your Comment To AUTHOR