Sql Server Stored Procedure

SQL SERVER – Stored Procedure WITH ENCRYPTION

One of the ways to keep stored procedure, function or view schema hidden is using the WITH ENCRYPTION statements. In this way, access to objects' DDL script can be locked, so that unauthorized users cannot see them:

create database procedurepractical

use procedurepractical

create table employee

( id int primary key,

  Name nvarchar(20),

  Class nvarchar(20),

  Dept nvarchar(20)

)

insert into employee values(1,'Sam','B.tech','IT')

insert into employee values(2,'Rahul','B.tech','CS')

insert into employee values(3,'Mahesh','B.tech','Mechnical')

insert into employee values(4,'Ethen Hunt','B.tech','EC')

insert into employee values(5,'Ram','B.tech','ME')

insert into employee values(6,'Jhon','B.tech','IT')

select * from employee

Create proc employeeproc

As

 begin

    select Name,Class from employee

 end

--------------------------------------------------------------------------------       --------------------------------------------------------------------------------

TO create procedure we can type CREATE PROC or CREATE PROCEDURE it depends on you

To execute store procedure just type the name or Execute ProcuderName or EXEC procedure name

select * from employee

 

create proc getemployee_by_parameter

@Class nvarchar(20),

@Dept nvarchar(20)

as

  begin

       Select Name,Class,Dept from employee where Class= @Class and Dept=@Dept

  End

   select * from employee

   --To execute we two way

  getemployee_by_parameter 'B.tech','IT'

  getemployee_by_parameter  @Class='B.tech',@Dept='ME'

 IF i want to view the text or definition of the store procedure to use following queries

 Right clik on store procedure go for script store procedure as--->Create to-->New query Window

 Two above procedure are the UDP

 Another way to view the

 sp_helptext getemployee_by_parameter(store_procedure name)

 sp_helptext getemployee_by_parameter

To change store procedure implementation

  sp_helptext employeeproc

  Create proc employeeproc

As

 begin

    select Name,Class from employee order by id

 end

 After execute above procedure we got error -There is already an object named 'employeeproc' in the database.

 

Now we would not be change store_procedure name just a way to do that

  TO change the definition of the sp we use ALTER

  Alter proc employeeproc

As

 begin

    select Name,Class from employee order by id

 end

 --TO delete procedure (Drop procedure procedureName)

 Drop proc employeeproc

 To encrypt the store procedure---> we use WITH ENCRYPTION

 sp_helptext getemployee_by_parameter

 Alter proc getemployee_by_parameter

@Class nvarchar(20),

@Dept nvarchar(20)

With encryption

as

 begin

       Select Name,Class,Dept from employee where Class= @Class and Dept=@Dept

  End

 

  Once we perform Stored Procedure WITH ENCRYPTION we can't see            the SP text