 Hey guys, welcome to SS Unit X to see this side and this is continuation of SQL Server real-time scenarios So today we are going to see how we can validate the first time login and second how we can validate login with the encrypted data So basically in the last video we have seen how we can do the encrypt and decrypt inside the SQL Server on the data level So if you haven't watched that video, so I would strongly recommend to watch that video You can get the link right on the top side Now go to on the SQL Server and we will try to implement these two So if you have remember in the last video we have created this login detail table And here we are having four columns, first is the business entity ID Second will be the login ID Third will be the login ID hash value And fourth will be the password So these four columns will be going to have over here Now we need to create one more column on this So that we can alter the table So that will be like alter table Then the login details And here we could add one more column And that column will be going to have flag as integer So this will be going to indicate if user is going to login the first time or not So we can execute that And now let me try to execute this So as of now it is having blank values on this flag Now go to here and let me try to create one SP And that SP will be going to take two input parameters First is the login ID and second will be the password For example, if user is going to login on the UI side So that user will be going to supply the user ID and password And this SP will be going to check if that user is login the first time or not If user login the first time then we can return one If user is successfully login then we can return two Otherwise we can return zero So it will be going to return three status So let me try to create the SP Let me try to comment this code first And after that we will try to create the SP So let me try to create Then we can specify proc Then proc name So as proc we can start with that Then we can call this SP as Validate login users like that Now let me try to have two input parameters The first parameter value that is login ID So we can have this login ID And the data type of this should be Wirecare 1000 Next will be the password So we can have this password And after that this will have Wirecare 1000 again Now it looks good We can start our SP Like that Now Here first we need to check If user is login the first time or not So for that how we can check that So basically we can declare a variable And that variable will have flag And data type should be integer And default value we can set this as zero Now Here what we need to do as If you have remember the hash key For the decrypt or encrypt is SSUnitec So this will be the hash key value Now what we need to do first We need to check if Exist Then we need to check select One from our table So table is login details And here we need to check Where login ID Hash value Should be equals to With the login ID From the input parameter And password should be equals to At the rate password So this will be going to check And flag is none It means we are going to login The first time on the system And we need to check one more thing Like and at the rate password value This value should be SSUnitec At the rate one to three Now it seems good Now what we need to do here We can have begin Then we can set At the rate flag value That should be one And we can have like and So we have done for the first time login Let me try to copy this code We are going to check For the second time Or for the validate purpose So here instead of using this Flag and this password We can directly remove that And it seems good now But here as we have already know These two values are the encrypted values So we need to do the decryption on this value So how we can do that For that we can call the function As the decrypt By pass pass So here the first that will be the Has key value So that value is SSUnitec We can specify directly over here And after that we can close the bracket Now we need to do the convert On this as well So we can cast this And here let me have this as And where can 1000 Now everything looks good Let me try to copy this And paste that in the password And instead of the login id We can have the password Now let me try to Copy these two lines And use the same on the Below check So here If this value is going to satisfy Then we can have this as 2 So now it seems good And We can directly Select this Add the rate Flag value And call this as Status Now everything looks good But here we need to focus one thing First it is going to check for this And second we are going to check for this But we don't want to Check these two again and again If user is going to login the first time Then it will check only the first code And will return from there So how we can do that So here we can create a scope with the SSU And colon And here We can directly use the Go to SSU And Here We can directly use Go to SSU So this is the standard that you could follow So what this SP is going to do First it is going to take two input parameters Like login id and password And here we have declared this variable That is Add rate flag and value default is 0 Now it is going to check If the login details table Will found this login id and password And after that if the flag value is null And Password is SSU to take Add rate 123 Then on that scenario We can directly go On this and say Flag value as 1 And we can directly jump to the SSU Without executing this second code If user has been logged in And change the password And in the second time for example He made the change on the password And in the third time he made the password again With the SSU But this flag value Will be going to change And it should not be null So this first condition will not be satisfied So it will go on the second code And here it will be going to check If that value is satisfied with the login id and password Then it will be going to set flag as 2 And will return directly right here So let me try to execute this So everything looks good Now let me try to execute this sp So how we can do that Here we can call this Execute and after that we can call this sp And after that here We can specify the login id and password So for the login id and password First let me try to Select this and execute So for the login id let me try to Compete this login id And for the password Obviously it should be SSUnitecAddRed123 Because this is going to login In the first time So that's why it is returning 1 If we have 1, 2, 3, 1 again So it should be going to return as 2 Because we don't have any password With the SSUnitec1 So by using this flag July person can check If the flag values are 0, 1 and 2 So accordingly he can Directly jump and navigate Between the pages that That person is created So here I hope you have Understood how we can check for the First time login and How we can check for the Validate So we will be going to check if We will be going to get the Status of this as 1 So it means the user will be Going to login as the first time So we will need to create one more SP and on that SP We need to create the update statement Where we will directly update This login detail table So that we will see in the next video Thank you so much for watching this video If you like this video please subscribe Our channel to get many more videos And don't forget to press the bell icon To get the notification of our Newly uploaded videos See you in the next video