Introduction

In this blog, we are going to learn how to create a stored procedure that is encrypted.

Step1

Create a table in SQL Server with a version of your choice.

  1. create table Products
  2. (
  3. ID int primary key identity(1,1),
  4. ProductName nvarchar(50),
  5. ProductImage nvarchar(50),
  6. Description nvarchar(max),
  7. Category nvarchar(50),
  8. Price int
  9. )

Step 2

Create a stored procedure in it.

  1. create procedure spAddProduct
  2. (
  3. @ProductName nvarchar(50),
  4. @ProductImage nvarchar(50),
  5. @Description nvarchar(max),
  6. @Category nvarchar(50),
  7. @Price int
  8. )
  9. as
  10. begin
  11. insert into Products(ProductName,ProductImage,Description,Category,Price)
  12. values(@ProductName,@ProductImage,@Description,@Category,@Price)
  13. end

Step 3

Alter the stored procedure to make it encrypted.

  1. Alter procedure spAddProduct
  2. @ProductName nvarchar(50),
  3. @ProductImage nvarchar(50),
  4. @Description nvarchar(max),
  5. @Category nvarchar(50),
  6. @Price int
  7. WITH Encryption
  8. as
  9. begin
  10. insert into Products(ProductName,ProductImage,Description,Category,Price)
  11. values(@ProductName,@ProductImage,@Description,@Category,@Price)
  12. end

Check the stored procedure text

sp_helptext spAddProduct

SQL Server

Step 4

Check if the stored procedure is encrypted.

sp_helptext spAddProduct

SQL Server