Encryption and Schemabinding

 1. Encryption :

            Encryption allows user to hide the source code of a stored procedure of a function after the compilation is done.

         WITH  ENCRYPTION -- KEYWORD IS USED ABOVE AS BEGIN

2. Schema binding:

              There are multiple or single table used in a function or a view. But if one of the table is accidentally deleted the function/view wont work, which may cripple the whole system

In order to implement schema binding one has to use the key word :

        WITH  SCHEMABINDING

 and also inside the code of funtion or a view one has to use schema name like dbo.tablename.

see below code snippet:

  alter function fn_setEmployee(@EmployeeId varchar(max),@DID int)

   Returns @EmployeeTable Table(Name Varchar(max),EmployeeId Varchar(max),Place varchar(max))

   with schemabinding

  As

  Begin 

  Declare

  @Name Varchar(max),

  @Place varchar(max),

  @id int

   --select EmployeeId, @Name = Name from Employee where EmployeeId=@EmployeeId

  -- select @Name =Name, @id =EmployeeId from Employee where EmployeeId=@EmployeeId

   select @Place=Place from dbo.Departement where DID=@DID

      select @Name =Name, @id =EmployeeId from dbo.Employee where EmployeeId=@EmployeeId

   

  

   insert into @EmployeeTable select @Name,@id, @Place

   return

   end