 hey guys welcome to ssvintx, we will decide and this is continuation of SQL Server real-time scenarios so today we are going to see about the encrypt and decrypt inside the SQL Server so encrypt is very important feature inside the SQL Server so if you want to load the data and that data is a confidential data so we should use the encrypt on that data so user cannot see that data directly by selecting the table so today we are going to see in the real-time where we can use that so before going to SQL Server so we need to understand about these two functions so first is going to use for the encrypt and second is going to use for the decrypt so this is basically encrypt by pass pass and decrypt by pass pass so basically it is going to take two parameters first parameter will be the hash key value so this hash key value very important because while we are going to do the encrypt so the same hash key value will be going to use while we are going to do the decrypt so go to on the SQL Server and we will see in the real-time where we can use that and how we can implement that so for example here we are having a table that is the login detail so inside the login detail we are going to maintain the only three columns or four columns and the first column as you could see the business entity id second will be the login id and third will be the login id hash value and next will be the password so password will be the encrypted value so while we are going to load the data into this table so we should load the data by using a hash key value and do the encrypt on the login id and password column so before going forward so as I told you we need a hash key value so you should always remember this hash key value otherwise you will not able to decrypt the value that you are going to insert on this login detail table basically we are going to get the data from the human resource dot employ table so as you could see so it is having around 290 records so we want to load the data from this table so the first column as we have already seen so that is the business entity id second will be the login id so these two columns we can get it directly from this table but for the next column value that will be the hash value of the login id it means the encrypted value for the login id so as I told you we need to use the encrypt by pass pass then we can start the bracket so the first parameter which will be the hash key value we can directly specify the variable where we have specified the hash key value then put common second that should be the login id so login id the column so before going to use that we need to do the convert on this login id so this login id we can cast this as where care might be hundred so that will work and we can call this column and post this bracket one more time then call this column as login underscore h now it seems good the next so the next value should be the password so for the password we should be going to use the encrypt by pass pass then the hash key value so we can specify this variable then put comma at the first time we can specify any static value on this password column so I am going to specify ssunitech at rate one two three so this password will be going to use while user are going to login at the first time and immediate it will ask to change the password like in the UI side or inside the sequence and we can specify as password now it seems good so let me try to select that and execute this so here we could see we are having the login id we have the business entity id and the highest value of the login id and password so now I am going to insert that data into the table that is the login detail now let me try to execute this query from here so now everything looks good in the message we could see 290 rows affected so it means data has been inserted successfully on this table now let me try to execute this so here it should have 290 records so as we could see so now everything looks good and we have inserted the data now let me try to decrypt this so how we can do that so for the decrypt we can use the select a stick from our table that is login details table put comma here we could use the decrypt by pass pass here let me try to decrypt the login id column so first we need to specify the has key value so let me try to declare this variable as the first input parameter so everything looks good the second thing that we need to cast this as worker so let me cast this as worker 100 like that push comma and let me try to copy this and checking for the password as well instead of the login id it should be the password this time now let me try to execute that so here as we could see here we are going to have the nulls inside the login id because instead of the login id we have encrypted the value inside the login id edge column so now let me try to execute that so here we should see the data as we have decrypted this like login id or here from this login id edge and password from this to this so it seems good and the real time while we are going to load this the first time we can use this code directly and we can create a sp and on that sp we will be going to take two input parameters the first parameter will be the business entity id and second will be the change password that is going to entered by the user and it will be going to update the existing table or the password by using this business entity id and where we could directly use this has key value so the important thing that you need to take care about this has key value and after that you can use the encrypt by pass pass and decrypt by pass pass so that you can use and thank you so much for watching this video if you like this video please subscribe our channel to get many more videos if you need this script it will be available on the description of my video so you can directly get that from there thank you so much see you in the next video