paintbrushe

[paintbrushe]

Change the data stored in a database

Welcome to presume technologies - I am venket. This is part 57 of signal server in this session, we'll learn about transactions in sequel server. Before continuing with the session, I strongly recommend to watch part 56 of this video series. So, what's a transaction, a transaction is a group of commands that change the data stored in a database. A transaction is treated as a single unit. A transaction ensures that either all of the commands succeed or none of them. If one of the commands in the transaction fails, all of the commands fail and any data that was modified in the database is then ruled back in this way, transactions maintain the integrity of data in a database. Let'S look at an example. I have this table TBL product, which has got information about laptops and desktops. 

At the moment the quantity of laptops available is 100. Now let's say I want to change the quantity to 200 and to do that, you know we are issuing this update statement. Update TBL product set quantity available is equal to 200, where product ID is equal to 1. Now, when I execute the statement, you know you can see that one row effected when we select the data back, you should see the quantity available is 200. Now this update statement is not part of any transaction. So as soon as you execute this update statement, the change is made permanent to the database immediately. On the other hand, you can make this update statement belong to a transaction by including begin transaction. So now, let's try to change the quantity to 300. So now this update statement is part of the transaction when we execute these two statements together. Look at this. It says one row affected now from within the same vendor. Within the same connection, when I select the data back from the table. Look at this. It shows 300, but then let's try to make another connection to the sequel server. So this is a different connection. 

Now, when I try to select the data from the same table, look at what's going to happen. It says executing query and then, if you look at the product table, it has got only two rows to select that rows from that table. It should take less than a second. But if you look at this it's almost more than ten seconds now, it is still executing that's because you have initiated this transaction, but you did not either commit this or roll this back, because of which other users will not be able to see that uncommitted data By default and that's because sequel servers default, isolation level is read, committed, meaning read only committed data, but here, if you look at this, there is some uncommitted data in this TBL product table. 

That'S why I'm not able to see the data from that table. So let's cancel that and if I want to see the data, what I can do is basically set the transaction isolation level to read uncommitted, data; okay. So let's do that set transaction isolation level to read uncommitted, so, let's execute this command completed. Now, if I execute the select star from TBL product, I am able to see the read. You know uncommitted data now when I roll this transaction back, execute this command completed successfully. Now look at this when I execute select star from TBL product before we execute select star from TBL product. 

This is the previous data. This is that uncommitted 300. Now, when I execute this query, look at this. The change is undone so now, whenever you're updating sir delete statements whenever these are part of a transaction now the change is not made immediately permanent to the database until you commit that transaction. So, if you want to make the change permanent, you come with the transaction. If you want to undo the change, then you roll back the transaction. So, for example, let's say at the minute you know the quantity available for laptops is 200. Let'S try to change it to 300 as part of this transaction and then to make that change permanent. I basically have to say commit transaction at this point. The change is made permanent to the database. Now transaction processing follows very simple steps. 

You begin a transaction using begin transaction and then you process the database commands you might have one or more sequel server commands. You know inserts updates and deletes and then you check for any errors. If there are errors, you roll the transaction back, otherwise you commit the transaction. Let'S look at an example. I have two tables here: TBL physical address and TBL mailing address. Now. In reality, let's say we are shopping online. Most of the e-commerce sites have mailing address, and you know billing address, and most of us will have the same mailing and billing address along the same lines here. If we look at this, we have physical and a mailing address. 

As look at this employee. 1. 0. 1, he has the same mailing and physical address. Now let if you look at the London you know the city spelling London is misspelled here and obviously I want to correct this to correct this. I have a stored procedure here and if you look at the stored procedure, it's a pretty simple one. All we are doing here is, we are updating, mailing address, table and physical address table and we are setting the city to correct spelling. Okay and if you look at these to update statements, these are wrapped between begin transaction and commit transaction blocks, and this block is further wrapped between begin, try and end try and then following the try block, is the catch block where we are ruling the transaction back. We have spoken about handling errors using try catch blocks in the previous session of this video series. If you are new to error handling, please check that video first okay. 

So obviously, when we execute the stored procedure, what's going to happen, it's going to execute the first update statement and then it's going to execute the second update statement and if there are no errors, the transaction will be committed. If there are errors, whenever you are executing this first or second update statement, what's going to happen, the control will immediately be transferred to the cache block and within the cache block, we are rolling the transaction back. Okay. Now, since these are two simple update statements which you know both of these statements should succeed and correct the city spelling to London - let's look at this, so I have these two tables physical address and mailing address. 

So if you look at this at the minute, the city spelling is incorrect and we have the same stored procedure that we have seen on the slide, which updates the mailing address and the physical address tables and then, in addition, we also have print transaction committed just To print a message you know to prove that it committed the transaction. So let's create this stored procedure now and now before we exit execute, let's quickly check the data and TBL physical address and mailing address, you know the spelling is incorrect. So, let's execute the stored procedure, so one row affected one row affected, so the first one row affected is when we updated the mailing address. 

The second one is when we updated physical address, and then we have that transaction committed message printed by this print statement, which means the transaction has been committed. So now, when we check the physical and mailing address you should see the spelling is now corrected. Now, let's do let's try to introduce an error intentionally if you look at the second update, it's the same stored procedure, except that the second update statement has gone. You know here we are setting the city to London, London, okay. Basically, what we are trying to do here is, if you look at the column, length the city column length in TBL physical address table. So if you look at the city column length, it's ten characters, but now I am trying to update the physical address to London. London and London Space London, is definitely more than ten characters. 

So obviously, when this update statement is being executed, we will get an error now, let's change the mailing address to London one now when we change the stored procedure, let's change this so alter procedure. Now, when we execute the stored procedure, what's going to happen, it comes here. It updates the mailing address, okay to London, one which is fine and then when it comes to updating the physical address. What'S going to happen, okay, the city column, let this stand, but but the value is more than ten, so this is going to throw an exception. So when an exception occurs, what's going to happen, the control immediately goes to the catch block and the catch block is ruling back the transaction. 

So when we roll the transaction back, what's going to happen, whatever change that was made by this first update statement is also going to be undone, so this update statement changed it to London one, but then it will be rolled back. So it will remind London and then it will finally print transaction rolled back. So let's see that in action, let's execute this. You see that one row affected, then it says: zero zero rows affected, so it first updated city to London, one and then when it was trying to do this, we got that zero rows affected so because there was an error when we were trying to execute this Immediately, the control will go to the cache block, rollback transaction and you should see print. 

You know transaction rollback, as a result of which, if you now select the data from these two tables, you would see as if we didn't update anything because the transaction is rolled back. So both of these statements are treated as a single unit, because the transaction ensures either both of them succeed or none of them and in this way, transactions maintain the integrity of the database. Now we we discussed that a transaction is a group of database commands. Now what database commands are part of a transaction is determined by an asset test, meaning the commands that belong in a transaction have to be atomic consistent, isolated and durable. What we mean by this acid test, we will look at in the next video session on this slide. You can find resources for asp.net, c-sharp and sequel server. Interview questions, that's it for today. Thank you for listening have a great day.

[paintbrushe]

Stored Procedures - What is a Stored Procedure and why do we need them?

[paintbrushe]

Stored Procedures with SQL

Welcome to presume technologies, I am venker. This is part 18 of sequel server in this session, we'll understand what a stored procedure is. A simple stored procedure example creating a stored procedure with parameters altering a stored procedure, viewing the text of a stored procedure and finally, we will see how to drop a stored procedure. A stored procedure is a group of transaxial statements. If you ever have a situation where you have to write the same query over and over again, you can save that specific query as a stored procedure and call it just by its name. Let'S understand what we mean by this with an example. Now I have this table called TBL employee, which has got you know the ID name, gender and department ID columns. 

Let'S say I want name and gender of an employee, so we type in select name agenda from TBL employee. So, every time I want the name and gender of an employee, you know I have to write this query. Okay, instead of that, what we can actually do is wrap this query inside a stored procedure and call that stored procedure rather than you having to write. This query again and again: so how do we create a stored procedure to create a stored procedure? We use, create procedure, command so create procedure, and then you have to give this procedure an name. Okay, so SP, let us say, get employees okay, since this procedure is getting us, you know the employee name and gender, I'm giving it get employees and look at this in the name. I have this letters. Sp. A common naming convention for store procedures is that we usually prefix that, with small letter S and small letter, P, okay, indicating that you know just by looking at this name, you can tell okay. 

This is a stored procedure. Alright so create procedure procedure name and then you will use as begin and and okay. So the definition of your stored procedure goes between this begin and end okay. So this is the body of your stored procedure. Okay. Now, when I execute this command, what's going to happen is a stored procedure with this name gets created in this database, which is nothing but sample that we have selected here. Okay. Now, if you want to look at the stored procedure that you have just created, you know you want to make sure whether if this procedure is actually created or not, okay go into that database which is sample, and then you have this folder called program ability expand That and you should see a folder called stored procedures. 

If you expand that you know we don't have it currently listed there, just refresh that, and you should see that stored procedure which we have just created, which is sp, get employees okay, so anytime, you want the name and gender of an employee. Instead of writing. This query again, what you can actually do is execute the stored procedure. Okay, so if you want to execute, you just need the name of the procedure. So what happens when I execute the stored procedure? Okay, to execute the stored procedure, you just highlight that and click execute and you get the name and gender. You don't have to write that query any more. Now you might be wondering it's a very simple query. Why don't I write that rather than having to create this procedure and then invoke it now, this procedure may be simple in reality, the procedures will be long. 

You know there are very no stored procedures with over three thousands of lines, for example, okay, and not only that there are several other benefits of using stored procedures from security to network, reducing network traffic, etc. We will be talking about the advantages of stored procedures in a very great detail in a later session. Ok, so we use create procedure or create proc statement to create sp, I mean you can either say create procedure or you can just say, create proc for shortcut. Ok to create a stored procedure, we will talk about the naming convention of the stored procedures in just a bit okay and to execute the stored procedure. We have seen how to execute that stored procedure. 

You know you just copy that the name of the stored procedure and you click this execute button and what happens the sequence statement within that short procedure gets executed and it returns the name and gender columns all right. So that's one way to execute it or you can use the exact keyword and then click this or you can use a full, execute keyword and then again, plus f5, or you can also graphically execute the stored procedure. Just right-click on the stored procedure and select execute stored procedure and the moment you do that it shows this vendor. This procedure doesn't have any parameters. Otherwise you will have to supply the values for the parameters. In just a bit. We will see how to create a stored procedure that takes parameters now, when I click OK, it executes that stored procedure. 

Look at that alright, so those are the different ways to execute stored procedure. Now let us look at a simple example of how to create a stored procedure with parameters. Okay, so let's go back to that table TBL employees all right now. What I want to do is I: I want to create a stored procedure which takes two parameters. Maybe gender and the department ID okay, for example, if I pass gender as male and Department IDs one tier stored procedure, it should give me employees only within that gender and within that department. Okay, so your store procedure needs to have these parameters. Okay. So, let's see how to do that so as usual to create a stored procedure, we use create procedure command so create procedure and give your procedure a meaningful name, so SP get employees by gender and department okay. So I want the employees by gender and Department. Now look at this now this procedure. 

Okay, the user who invokes your stored procedure, is going to pass the stored procedure, the gender and the department ID so for them to be able to pass the values for gender and department ID. They should be parameters just like how functions have parameters in c-sharp or any other programming. Language stood procedures can also have parameters. Okay, so one is the gender parameter and if you look at gender, it's text here, so the data type is going to be n, where care of maybe 20 and department ID Department ID is going to be integers or Department ID integer as begin, and so the Definition of your stored procedure goes in between these lines. 

Okay, so what do we want from the table? We want the name, and maybe just the gender and also the department ID from which table we wanted from TBL employee table, but we don't want all names genders and department IDs. We want to filter them with what the user is going to pass in is going to pass in the gender and department ID. So we are going to say, the gender column here should be equal to whatever the user is passing in at gender and along the same lines and department ID let's bring this to. Another line is equal to whatever the user is going to pass in okay. So these parameters are like placeholders when users execute your stored procedure, they're going to pass in values for this gender and department ID which will be replaced at execution time. Okay. So let's create the store procedure so to create that select the entire stored procedure. Click execute button command completed successfully. Now, if you refresh the stored procedures folder, you should see SP get employees, Genda and department, okay, now to execute the stored procedure. I just need the name of the stored procedure and look at this. This stored procedure now is expecting gender and department ID parameters. Now look at this. If I don't pass the parameters and if I did try to execute that stored procedure, see highlight that and then plus execute. What'S going to happen, this procedure or function, SP get employees by gender and department, expects parameter, add gender which was not supplied, and that makes sense it's expecting a gender parameter which is not supplied. So we need to pass in the gender parameter since gender is of type and we're care I have to use single quotes, so I want the male employees within you know: department ID 1, so department ID is 1. So these are the two parameters that this stored procedure expects and we need to pass them. So when I press f5 now look at that, I only get male employees within that. You know department ID 1, okay. On the other hand, if I want all the male employees and department ID do a 2, I can do so all right now, when you have parameters like this, you know what you're doing is you're just passing in the parameters. So so so this male value will be taken into at gender parameter where, as this number 1 is passed into department, ID parameter. Okay, now what happens? If I put it the other way on, I am passing one first, okay, so what's going to happen, it will take this one into gender and one is an integer, but gender is of type and where cab and this one will be converted into n we're care. Implicitly, no problem, but it comes to the second parameter male. It tries to take this into Department ID parameter and if you look at the data type of department ID parameter, it is integer okay, so it tries to convert this string into integer and it fails - and it throws an exception - look at this. If I try to execute this now, I get an exception saying that error converting data type where care to integer, so it is trying to convert this mail. You know string of type and where care into integer and we get that error. Okay. So when you have multiple parameters that the stored procedure is expecting and if you're passing just values the values order, you know the order in which you pass them is important. Okay, the first parameter will be used. I mean the value here. The first argument will be used with the first parameter and the second argument will be used with the second parameter. Okay, that's why the order is important, but if you use the parameter names like this, let's say I want to pass one two at Department ID. I can specify the name of the parameter like so and similarly I can specify the name of the parameter for gender. 

So when I execute this now, I will have no issues because you are specifying the name of the parameter. Okay, so sequence of a knows. Okay, this one is meant, you know to be the value for Department, ID parameter and mail is the value for gender parameter. It'S only when you don't specify the names of the parameter. The order of the the order in which you pass the parameter is parameters is important. Alright and okay. So we have seen how to create a simple stored procedure and how to create a procedure with parameters as well, and we have seen how to execute them as well. Okay, now, once you have the stored procedures, let's say I have created two procedures until now. Sp get employees and, as we get employees by gender and Department. 

Now, if I want to view the text of these two procedures, what are the different ways that are available? One way is to simply right: click on that stored procedure, script, stored procedure as create two new query - editor window - this you know, generates the contents of that stored procedure. Look at this. This is the stored procedure. Definition that we have created create procedure procedure named. As begin and and then our query, this is one way to look at the definition of a stored procedure and the other way is to use a system stored procedure. You know these stored procedures that we have created here are user-defined stored procedures. 

These are not system, stored procedures, now, sequel server, you know, has some system stored procedures defined okay and we use it for certain tasks. For example, I want to find the text of a stored procedure. How do I do that? I can use a system stored procedure called SP underscore health text. Okay, so look at this. This is the name of the system store procedure. Sp underscore help text, okay, SP help text and then, if I pass in the name of the stored procedure, there SP get employees and then, when I select them together and execute this look at this, I get the text of my stored procedure. You can then copy that paste it here and see. How does the implementation of the stored procedure looks like okay, so to view the definition of a stored procedure, you can either right-click on that script. Stored procedure as create two new query, editor window or you can use the system stored procedure, SP underscore health text, followed by the name of the stored procedure, which gives you the text of the stored procedure. Okay, now in this slide, if you look at this, you know whenever we name user-defined stored procedure, microsoft recommends not to use SP underscore. 

You know prefix for user-defined stored procedures, because the reason is system stored procedures has that prefix. Okay. Now, if you happen to use SP underscore prefix for your user-defined stored procedures, there are two problems number one. There will be an ambiguity between user-defined stored procedures and system defined stored procedures, just by looking at the name. We cannot tell you know. Is this a user define, stored procedure or system defines stored procedure? Okay and another problem is, with future releases of you, know new sequence of a version. There may be name conflicts, you know if you create, let's say SP underscore ket date. Just you know, stored procedure and in the future release there is a system stored procedure, which is you know similarly named SP underscore get date. You know it's going to conflict with the user stored procedure, okay. So, to avoid problems like this, it's always better not to prefix user-defined, stored procedures with SP underscore prefix, alright. So to change the stored procedure. 

Now, once we have created a stored procedure, for example, I have the stored procedure, SP get employees after I have created the stored procedure. Let'S say I want to change its implementation in some way. How do I do that? Let'S say at the moment when I execute this SP get employee stored procedure. I am NOT getting the names sorted, you know I mean the names are basically not sorted. I want them to be sorted, so how do I do that? I will use the order by Clause so order by name okay, so I am changing the implementation of this tour procedure now, if I execute this create procedure once again, look at this, we get an error stating that there is already an object named SP get employees. Why we already have that and we are trying to create that again with the same name. So obviously we get that error. Our intention here is to change the definition of that stored procedure, not to create another stored procedure. So if you want to change the definition of the stored procedure, then you say alter procedure and I press f5. 

The stored procedure gets changed now. If we execute that we should have the name sorted okay, so we use all the procedures statement to change the definition of a stored procedure and to delete the stored procedure. We use drop procedure procedure name just like you know. If you want to drop a table, you will use drop table, table name. Okay, so, similarly to drop a procedure, you will say: drop, drop procedure and procedure. Name, for example, I want to drop or delete SP get employees. You know I just pass it there. I press f5 and then, if i refresh the stored procedures, folder it's gone now it's deleted or what you can do: alternately right-click on that and select delete okay. Now it's also possible to encrypt the text of the stored procedure and it's very simple to do. For example, I have the stored procedure now as we get employees by gender and Department look at this now. 

This is not encrypted at the moment. So when I use SP underscore health text and when I press f5, I am able to get the text of that stored procedure. So that's how the stored procedure is implemented. Now, if I want to encrypt the contents, the text of the stored procedure, I can do that. How do I do that? All you have to do is to use this switch, this option with encryption, okay, and we want to alter that. So I will say alter now when I press f5 look at this command completed successfully and now the moment i refresh this folder look at this. We get a little lock symbol there indicating that this stored procedure is now encrypted. 

Okay, now, if somebody tries to you, know, get the text of the encrypted stored procedure, we get a message saying that the text for the object is encrypted and - and we cannot retrieve the text of that - ok and you get the same kind of message when you Kind of use script stored procedure as create two new query: editor window. Okay, we get this error box, you know. If you look at here. It says that text is encrypted, so once a stored procedure is encrypted. You cannot view the text of that stored procedure, but, however, if you want to delete the stored procedure, you can go ahead and delete it and I'll just right click and select delete it gets deleted, but you cannot view the contents of his stored procedure that is Encrypted all right in the next session, we will see how to create an invocation procedure with output parameters. In this session, we have seen how to create a stored procedure with input parameters in the next session. We'Ll talk about creating stored procedures with output parameters. On this slide, you can find resources for asp.net in c-sharp interview questions. That'S it for today. Thank you for listening. Have a great day,