Batches

Video Transcription

Welcome back everyone. In this video, we’re going to be starting our discussion on programmable objects. And we’ll be covering a lot of different topics in this video series. But first, we’re going to talk about batches. So batches aren’t necessarily a programmable object. But we really do need to understand how sequel runs, queries in batches. So we can understand scope, and how programmable objects are actually functioning. So batches are chunks of SQL queries, or a series of SQL queries that are are sent to the database server as one unit. So we could have five SQL queries in a row. And those could be sent as a batch to our Microsoft SQL Server service. So far, what we’ve used with batches is just highlighting SQL queries and running it or each of the, like Jupyter Notebook cells, that is, when you press run on a cell that is considered to be a batch. Or if you have a sequel file without any ghost statements, that entire file would be sent to the database server as a full batch. But the addition of the Go syntax is going to allow you to separate SQL queries inside one file into batches. And this is really important when we start talking about scope of things like variables, for example, also certain programmable objects and CTS and things like that. But it does allow us to separate things out. And this can help logically organize our SQL queries into smaller chunks.

When we are actually executing them on the server, some things actually need to be fully processed first before we can actually execute queries on them. So particularly difference between DDL and DML. So the data definition language, so that’s the create tables, and DML. So select queries, insert, updates, and deletes. So without batches, we can actually function properly when we’re working with both of these kinds of statements in one file. So if we have a create table, query, and then right after that CREATE TABLE query, I have an insert query, the CREATE TABLE query must finish executing first before the insert query actually executes. Likewise, similar issues arise with updates deletes, selects, all those sorts of things. So we can’t mix and match DDL and DML, the DDL has to be executed first. And then the DML can follow afterwards, assuming that the tables haven’t been created yet. And so the resolution of these table names and things like variables, functions, all of those sorts of things, that that naming resolution is done per batch. So just like if we declare a variable inside of a function, the scope of that variable is within that function. Very similar idea for when we work with batches in SQL. So the scope of a variable declared locally is going to be within a given batch. And there are ways we can declare global global scopes. And we’ll also show that here in a future video.

But for now, let’s take a look at some examples of batches and the effects that batches have on the way we write SQL queries. First off, I’m just going to show a very simple toy example here. So if I execute this, we notice that we have three different SQL queries, right, are actually three different batches. Sorry. And within those batches, you can see the total execution time and the number and the things that were affected by that batch. So each one of our batches here actually has just one SELECT statement. So here is batch one, batch two, and batch three. But notice how the second batch has an error, but the other two batches are not affected by that error. And this is really important. Because if I MC go over here to my results, you can see that I get Hello, and hello again. But I don’t get this message here, right. I don’t get this message here. But if I actually take out this batch operator, let me comment this out real quick. So if we comment that out, run this again. You notice Now I only have two things that actually executed. Here’s my first batch. So line one, and then here is my second batch. But notice that I only get Hello out as a result. So this batch executed completely and successfully, this batch executed, but the first query in that batch failed. And since the first query in that batch failed, the rest of the batch was not able to finish executing. So this is equivalent to a function throwing an exception, and the rest of the function doesn’t doesn’t finish executing because an exception occurred. Very similar idea here as an SQL. But the same idea here, this is why variables aren’t visible across batches.

So if I open up this example, here, I have this variable called greeting, which is called Hello. And notice I separated this out as a batch. And so if I execute this, I get an error again, right must declare the scalar variable greeting. So the scope of greeting exists only within the batch, it was declared, it does not exist in this batch, the second batch here. So the scope for our declare statement is local only to the batch that it is that is declared in. And so if I take out this batch operator again, and give us a run, now we actually see our query execute successfully. So this is really some of the fundamental uses of batches. And why batches are important to note the separation between different sets of sets of queries. And because it is really quite common, that we have multiple queries in one single dot SQL file. So this is really common usage. And so we can use that ghost statement to separate those out. So if we also, let’s see here, try this example here. Data Definition Language statements, creating objects must be the only things inside of the batch. So if I have additional things in my batch, we run this thing here. Haha, right, we get some issues, right, we get some issues.

So when we create database objects, that can be the only thing inside of that batch. So creating a table, creating a schema, so on and so forth. And so if I need to do other things like data manipulation, language queries, like selects, or even other DDL queries, like drop schema, we have to actually separate those out into batches. So if we try this, hmm, so we actually see that there is error here. But let’s go ahead and take this back out as a batch. There we go. Hmm, right, because I can’t actually create a table on a schema without the schema existing first. And so if I take out this batch here, but I take out that go statement, it actually gives me a syntax error. Because the schema must be created in its entirely entirety, before we can actually add tables to it. So this is the reason why we separate these things out batches. Because if we sum if we submit all of that at once, the queries don’t actually get to view the end results of the objects being created until after the queries have been finished executing until the batch finishes. And so if we want to create things and create database objects, and then run queries on that database object in the same file, they must be separated out in batches. So let’s take a look at one more example of our batches and action here. So this is just to showcase some more issues or more information on naming resolution with batches. And so if you notice, when I run this, I get an error down here, I get a couple batches that work successfully.

So my first batch which is this DROP TABLE statement up here at the top that runs successfully. And then I have a second batch here, which is my CREATE TABLE query that runs successfully. And then I have an alter statement, and then a select statement. But notice, right notice that name for One, my IDE doesn’t actually recognize name as being a valid column name. But my ALTER TABLE statement up here clearly adds that column into my table. But within this scope, right within this scope name does not exist yet name will only exist after this batch finishes executing. And so in order to get this error to go away, I have to separate these two queries out using a batch. And now, that actually works. Right now, the name column actually exists before I run the SELECT clause on it. So this is another issue. And again, my ID is my ID still showing invalid column name here, kind of ignore that error, but it actually executes now and I actually have that name column that exists. So just be careful when you’re working with ALTER TABLE statements, and basically any data definition language query. A lot of the times those are going to need to be separated out into their own batches for your rest of your queries to actually execute properly. But that will conclude our discussion on batches. And coming up next we’ll start to explore some more programmable objects and relation to that

Temporary Table Constructs

Video Transcription

Welcome back everyone. In this video, we’re going to continue our discussion on programmable objects. But in turn, we’re going to revisit some things that we’ve already covered around temporary tables. So, temporary table contracts are an important idea because as we get into programmable objects, like stored procedures, we need to understand that not everything that we do with SQL is physical storage, right? A lot of the times, this is logical representation. And in some cases, these are only logical to within within a certain session. So a single connection to the database, or global, which is available to everyone that is connected to the database added at any given time. So different things can be utilized in this sense. So things like things that we haven’t covered yet, our table types, so we can actually define our own custom types within SQL. And those can be made available. We’ve also we’ve done things like use CTE keys, and user defined functions as well, which we’ll also revisit here in just a few. But first off, we’re just going to cover mostly just variables and custom types. But let’s take a look at some examples of these variables that we’ve seen. So far. Most of the time that we’ve used them are scalar type data variables, right, we declare a variable, assign it a value, and then we utilize that as part of our SQL queries. But we can also create tables, and store those tables as a variable as well.

So here is our person table that we’ve typically used in the past. And so we can actually run this and store a full table as part of a variable, right. So this table itself does not exist physically in the database. It’s not part of our schema, but it is local to our current execution right within our batch. So we have this person variable available to us, that has all of these columns, and it will behave and act and just like a table normally would. But it is stored inside of a variable instead of a physical table in our database. The first line here is really the only difference between our our normal variable declaration versus the table variable, right, and we just have table here, instead of like in var car or anything like that. Alright, so now let’s go ahead and let’s go ahead and keep on finishing out creating our demo database that we’ve seen so far. Um, so here we have the address type table. Again, very, very similar, very, very small table. But I will also include the person address table, which has a little bit more complicated, and it starts to show a little bit of the limitations here with variables. So most table constraints are fully supported on table variables. But we are not allowed to actually name them.

So I’m gonna go and run this real quick. So that runs perfectly fine. But if I uncomment, this line, you can see that actually come up with an error. So I can’t actually physically name my constraints. Although I can add, I can have a check constraint, I just can’t name that check constraint. Because again, those these constraints are not physically stored in your database, it is stored as part of the variable, it’s stored as part of the variable. Similar issue goes along with foreign keys. So foreign keys are not allowed. So you’d get an error if you uncomment. This. So just kind of be aware of some of those limitations when you’re working with table variable names. But we can run queries on on these, so we can insert data into our variables. That works just fine. If I did a select, select star, whoops, wrong key, select star from and then let’s go ahead and just pull out the people from the people variable. And you can see that we get all of our data out and it shows up just like a normal SELECT query would on a regular table. So this is pretty useful if we need a quick temporary table to execute some queries on So there, there are some beneficial situations where we can get that from, we can also run some more complicated queries on this. So we have an insert select from. And so we can run a more complicated insert with a join. And then we can also do joins on the temporary tables, through the, through the different variables.

So here I’m joining the person address temp table with person and address type. And so if we execute that, now we can get all of the people and all the all the information for each of the people out along with all their addresses. From from our temporary tables, so getting there are not super common use cases for for a variable, a table variable. But they do provide some extra flexibility when we’re working with that. So this is temporary variables, right temporary, temporary, temporary table variables. But we can also so that’s here. Sorry about that. Brock, if you would mind cutting that last touch out. So those are all of our table variables, these are temporary constructs that do not physically exist in your database. But temp tables are not are not stored in a variable and are stored in your database. So these are physically present. But they do have a specific scope. And some certain scenarios, they’re only visible to a single database connection or a single session. Other temporary tables are global, and so they’re visible to all people who are connected to your database. So let’s take a look at a couple examples of those. So here is an example of a temporary table. Tables that have a pound sign in front of them are going to be are going to be stored in Microsoft SQL servers, temporary database structures. So if we execute this, sorry, let me unhighlight that, execute this, get all of our information back out as normal.

But notice that everywhere I actually use a table name, I’m using a pound sign instead in front. Now important part here for this is this here. So if I highlight this and run, here we go. And notice this is this is available to my current session. So that this, I executed all of this first, right created the temporary table construct. So this physically exists in my database now. Go to databases, right? You won’t actually see this here and my schema here. But if you are not, Yep, here you go, here is my Temp DB and table roles. It’s not going to let me expand it here in the file explorer. But this Temp DB right here, that is where this table actually exists. So you can kind of dive dive down into that if you’d like to in your own time. But you can see the results of that right here. So here is and by the way, this SP help. This goes for any table in the in your SQL Server. Okay. So this is a, this is the name of the table. So hashtag person, the owner is DBO so this is owned by the database system. This is a user created table. And this is when that table was created. So this is all in one session. This is all on one session. And so if I highlight this and open up a new new tab and run this, ah, no object at person or pound person does not exist in database Temp DB.

If I go back over here and run this again, on this side, it still works right it still works. So this One database connection. So one session, this tab over here is my second session. So it’s a new connection being made to my database. And so therefore, my temp table only exists within the session that it was created. It is not available to other sessions. Let’s do, let me do some cleanup here. Let me go ahead and drop the temporary table real quick. And then I’m going to create a new one. Now, okay, so here, I’m going to create a another temporary table. So select insert into so doing a SELECT INTO. So I’m pulling out all the customers from the wide world importers database Customers table and inserting it into a and inserting it into a temporary table. So if I give this a run, see everything works. But let’s try this from my other session. Again, still only local to my individual session, not available to all sessions yet. So we’ll get to global ones here in just a minute. But this is probably the most common use case for temporary tables, where you want to make a quick copy of a table. And then you could actually execute example, query queries on that temp table instead of the real one. And so you can try things out to see what happens make sure everything is running properly. But again, it’s is typically a general edge case here, you don’t have constraints either on this temporary table as it is right now. So you would have to add constraints to this, if you want the full copy of your original table does not duplicate the constraints from the table he pull from. So you have to add those manually, you have to add those manually. But nonetheless, this is a handy way to create duplicate or duplicate data from an existing table.

So what if we go ahead and drop that table real quick? What if we wanted the our temporary tables to be available to everyone, right, everyone. So a single pound sign denotes a temporary table that is available to the current session. A table that is created with a double pound sign in front of its name denotes a global, temporary, very global temporary table. So this table works, this is just going to work exactly like my previous example. Everything works fine in this session. But now, if I try to run this query over here in my second session, ah, it works. This is just a quick and easy way to create global temporary tables. Very useful for again, like I said, trying out queries on data that can be destroyed if you need to. So and this way, you can create all of your normal constraints as part of this. So default constraints, check constraints, all those sorts of things can be added here. And so very easy way to try out very quick and easy, harmless way to try out queries, if you don’t have a test database ready to go.

In addition to temporary variables, we can also create our own custom table types. So let’s take a look at this example here. So here we have this type. We haven’t actually seen this syntax before create type. But I can create my own custom types inside of SQL Server. So create type, and I’m calling this a demo dictionary, and then as table. So this type is going to be a table. And within this table I have two columns key and value. Just like what a typical dictionary would have right a dictionary is a key value store. And so we can have a column that represents the key being the primary key make forcing the key to be unique. And then we have a value associated with that. And then we can declare variables of that type. So declare states normal variable declaration here, and then here’s my type. And then, since that is a table type, I can run queries against it, just like my, my table variables that I showcased earlier. So I can run an insert. And then I can also run select on that query as well. Important thing to note here is the different batches. So I if, if I have my old one, I drop it first, before I create it.id, that is in its own batch. And then the Create type, since it’s creating a database object, that also has to be in its own batch. But that is still visible down here, when I create my variable that’s still visible down here. So I can run my insert on my table variable just like I was showcasing earlier. And I can run a SELECT clause on my newly created variable, table variable as well.

So these sorts of things are very useful for as, as I showcased here, creating ad hoc types in our SQL queries. So things like a dictionary is very useful. Things that it may not necessarily store physically, but useful for when executing large and complicated seek sequences of queries as a result, but that is going to conclude all of our examples on temporary table constructs.

Views & User Defined Functions

Video Transcription

Welcome back everyone. In this video, we’re going to be taking another look at views and user defined functions. So remember, a view is a logical construct only. And it is considered also a table expressions. So remember, views are just queries that we created and stored as part of our database. But the data that the view actually pulls from is not actually stored. Again, as part of the view, the view is nothing more than just a query that retrieves data from other tables. But it is a very useful construct, to allow users to interact with the database in a more secure way. Because they don’t have access to the they don’t have direct access to the original tables. Views in general, give a false sense of performance increase, even though views themselves have no benefit at all, towards performance of your of the queries that are actually executing. So I mean, views are very commonly misused in general, in terms in terms of databases. So a lot of times people get the false sense of performance increase. And a lot of times views are kind of abused in that way. And so we have to really do be careful when we’re working with working with views. Views, in general are a good way to abstract the database away, and to allow your users to interact with your database, and directly, but nonetheless, it’s not a bulletproof solution. So when when we actually use views, views are, as I mentioned, right, very easy to do securely.

And so if you want to prevent users from gaining access directly to an individual table, a view can provide that interface between between your user and the actual table itself. So you can provide security to a view, which is a little bit easier to actually conduct, then providing security, you know, access directly to an individual table. Views also help migration plans. So if you have a database that is going to be updated to a new version, whether that be the actual database server version is being updated, or maybe you are transitioning to a different schema structure. And so you want to provide some backwards compatibility between the new version of your database and the old version of your database. Views are a good way to achieve that. However, again, this is where we get in trouble with views, a lot of times views are treated as as a permanent construct here. And if we’re trying to provide backwards compatibility, we don’t want to provide that permanently. Because there’s if you provide that permanently, there’s really no reason to actually upgrade or update to a new version of your database. So we have to really be careful there, when we use views in that way.

Views also can be used to hide some complexity. So if a particular query is requires a significant number of joins, and complex SQL, then we can use a view to abstract that complexity away from your user. So they can interact with a more simplified version of all that data being joined together for them already. So that can be very useful. And it can also be useful when interfacing with a third party applications, which makes the process for programming those a little bit easier to do. And so let’s go back to our examples here real quick. And just to kind of refresh our syntax here of our view syntax. So our syntax here, create view as and then the query that is going to represent the view, and then we need to execute this as part of a batch. So execute this. Now we have our view, and then we can select from NOC view. So this does, you know, abstract the complexity, right, I can pull select star from this view, versus running this big query here that requires a join. So that is a good benefit there as part of it. But again, views are logical constructs only remember that as well. And do be careful when utilizing views because they can be a common pitfall and be misused in a lot of scenarios.

So primarily, just be careful when working with those other things that we’ve covered so far. So we’ve hit views before and we’ve also hit user defined functions before as well. Again, remember inline table valued functions. These we discussed when we talked about before per table expressions. And there are two primary types of user defined functions scalar valued, and table valued. These serve as routines, meaning that those are actually stored physically in your database, the actual function is stored in your database, just like what a view is that that query is stored there in your database. But user defined functions do provide a limit there are there is a limitation here, meaning that we cannot change the state of the database using a user defined function. So that means user defined functions cannot insert, update, delete or create anything as part of your database. Because that changes the state and user defined function. user defined functions are not eligible to change that.

So let’s refresh our memory here about our user defined functions. So these are created very similar to our views. So create, and here I’m doing create or alter in case I already have this function defined as part of my database. If you’re creating this, for the first time, the or alter part doesn’t actually do anything. But here is the name of our user defined function and my demo schema and my demo schema and my parameters for this function. My return value here, this is a scalar user defined function because it’s returning numeric, not a table, and then begin. So here is my actual function, body, and my return. And so all that it is doing here, give us a run, it’s just converting the current time and to milliseconds, current time in milliseconds, a silly silly function, but kind of showcases the the syntax and useful or the use of a scalar user defined function. Also remember that we can create a table valued user defined function as well, this one in particular is a little bit larger. So create or alter function demo.int sequence returns result, that is a table and it takes a that table itself has a column called value, that can’t be null, and it is the primary key. And so we have a couple of a variety of CTS here. So we have power to CTE. And we have power for CTE power, eight power 16, power, 32, and so on. And so what we are going to actually return here is, so here, demo, and sequence 111 100. And then up here. What I’m going to return here, from here, I’m using power 32 CTE and essentially going to res, whatever my, whatever my integer sequence starts as all the way up to.

So if we scroll all the way down, all the way down to 100. This is a very, a very roundabout way of creating a sequence, starting at a starting point. So starting at value, so one, and then going up to our max value here. We’re achieving this through a series of cross joints, right? We’re achieving this through a series of cross joints. And so all of this here, this is the start one through four. And if we this one here is that raised to power four. And so that would continue starting off at five, and so on and so forth. And so we would eventually run out of numbers here to our max number that we can actually represent as part of this, but this is a interesting way. So why create our integer sequence? Well, most of our sequences that we actually do are done as a sequence object, or done as a identity field identity column. But one common use in practice is to include all dates within a certain range, and all dates within a certain range. So one interesting way to involve this integer sequence is this here.

So let me go and run this. And so now we have this initial one here, by the way, I run this one, sorry, this one here is going to be four rows, four rows, and then down here. So here’s our transpose. Here’s our transposition. So our two tables that we actually have as a result, so we have all a values zero through five with the date. And so here’s, here’s where our integer sequence comes into play. Right, here comes our integer sequence. So we’re actually doing a neat little trick up here. And our CTE is, is that we’re actually counting instead of just counting directly by just an integer sequence here. So here’s our integer sequence. But we’re actually adding that to our our date, right? And we’re transforming that with our, our people. Right? So value, Mike, John, Mark, and Colton. And then once those get once those get joined, so transposed, that gets combined with our, our full information here. So this is kind of a very neat little way to create a lot of a lot of powerful expressions to include sequences of dates, or all dates and range, along with some other data. So just a more advanced usage of a user defined function here. But nonetheless, right, we have user defined functions that return tables, and user defined functions that return scalar values.

If you do have more questions about this particular user defined function, I’m not going to spend too much time in this particular video in diving and detail into each of these CTS. We do have some set operations that we’re executing here, along with some window functions and substring functions. So this is a relatively complex, user defined function here. But if you do have questions, please reach out and we’ll be happy to fully answer those that will go ahead and conclude this video on views and user defined functions. Again, this is first and foremost, primarily a review, to prepare ourselves for talking about more routines that we can store in our database, like stored procedures

Stored Procedures

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at stored procedures. So so far we have talked about some routines like user defined functions. Routines are nothing more than database objects that are stored as part of your database. But we have some differences here that that start to come out when we start talking about stored procedures. So we’ve seen views and user defined functions with both of them do provide some intermittent level of abstraction, it does insulate your database a little bit from your users, so your users don’t interact directly with the table. So it does provide some security as well. But stored procedures have a little bit more flexibility in that regard, we can also keep all of our error handling in one place. And so we can start to do things like exception caching, and handling like that, which we’ll show in another video. But the primary difference between stored procedures and things like user defined functions and views is that it will start to provide some performance benefits as well. So things like caching, and other things start to happen here, and apart from what you see, in user defined functions and views. But nonetheless, let’s take a look at some examples of this working.

So we’ve showcased and reviewed views and user defined functions, stored procedures are created in a very similar way. It’s just create procedure. As I mentioned earlier, you may see these in the NC standard being referred to as routines instead of stored procedures. But I also have executed this setup query as well, that gets all of our tables back into our database from our our demo. So this has like the person addresses person and address and also our person history as well. But our stored procedure, as I mentioned, looks exactly like a user defined function. Really, the only difference here is, instead of create function, we have CREATE PROCEDURE, it does exist as part of the schema, I can have multiple parameters as part of this, this procedure as well. And just like a user defined function, we’re going to execute some query as part of it. And so let’s go ahead and execute this. So there is our our store procedure now created in our database. So if we go over here to our databases, expand cc 520. There we are. So here is our fetch person, procedure that I just created here. So this is stored physically, in our database, at least the stored procedure part, the data is not stored physically as part of the stored procedure, but the query is the query is still physically stored.

So let’s, how do we actually call this function? Well, this is simple enough, all we need to do is say demo dot fetch person, and then the parameter. These are comma separated out, by the way, if we have more than one parameter, so we say exec this procedure, so execute this stored procedure, and then pass this value as a parameter. So if I give this, if I give this exact command to run, we get this as a result. So that’s person one. And so I run that query up here, that person ID, so select the person information from demo dot person, where Person ID matches the one that was passed in. And notice here I do not have a return statement, there is no return statement here. So the again, very similar as a user defined function, are not going to be using CREATE TABLE commands and things like that here, primarily going to be using the data manipulation language, or DML statements. So select insert, update, delete, we can also use named parameters. So if you remember, syntax from Python, we can use named parameters just like we do there. So we name the parameters.

So at Person ID equals one, so this matches the variable name up there in my stored procedure, so at person, I d equals one, and so you also see this here, right? Demo dot fetch person, person ID. And so then we can give this a run Now that works just the same. As I mentioned earlier, as well, we also have the capability of creating a stored procedure with more than one parameter. So let’s give this a run, get that created. And then we can execute it. And again, I could actually delete, I can remove this, I don’t have to create the stored procedure every every time. But executing that’s gives me all of this. And these have default values, right, we can have default parameter values, as we have in things like Python. So that is very useful. So these are considered as optional parameters, because they have default values, they are considered optional. But I can provide, I can provide parameters with them. So if I wanted to say retrieve persons, that has starts with A J, I can give this around and get only the people whose first name starts with A J. Right? Likewise, I could also Alright, so that’s the first name pattern. The last name pattern, again, this is the the parameter passing here is identical to how Python operates.

So if I give this a run, R is passed in as the last name filter. Now, if you want to skip one, if you want to skip a parameter, you could say default. And now I get all of the people with the last name starting with R, and then a, then the first name uses the default value, which is defined as just the wild card up here. So that works out very well as well. You can also skip a parameter, skip an optional parameter using the named parameters. So if I wanted to, instead of passing in default there, I can say at last name. Pattern, give this a run. Oops, need the equal sign there? There we go. Give that a run. And there we go. So we get similar in similar results, and all three ways. But short answer short story here is that the parameter passing two stored procedures, and the definition of the parameters for stored procedures work very similar in nature as what they do in Python. output parameters are also also supported. And so output parameters you might not have covered yet in your programming classes. But out parameters are very useful ways to return more than one piece of information. So typically, in your programming languages, a function only returns one value has one return statement.

But we can return more than one piece of data utilizing output parameters. And so over here, we can have, we have these three parameters here, first name, middle initial, last name, those are normal parameters. And then down here, I’ve denoted these two parameters as output parameters. And so what the benefit there is, is that for these auto fields, right person ID is an identity column created on has a default timestamp created when a record is inserted. And so I can actually pull those and those pieces back out from our stored procedure, right, we can, so if we give this a run, sorry. This run there we go. So now I have my stored procedure, this whole thing here stored procedure, and notice that my Person ID and my created on on manually setting so created on is the created on field for that person that I just created. And the person I just created. We got that ID from scope identity, if you remember that command. So in order to execute this, let’s go ahead and execute this stored procedure. Exec then demo. Sorry, demo dots and Then we have create person. And then we are, I’m just gonna go ahead and put myself in here. So we have first name. And that is equal to Josh. And then let’s do last name, middle initial is an optional parameter because it has a default value of null, last name. And this is going to do, there we go. Execute this, right. So this doesn’t quite work yet, right? This doesn’t quite work yet, because we still have those out parameters.

So if we actually execute this without defining the parameters, for this, we still get the error. So they work just like what we have, without parameters and things like C sharp. So we need to first declare, we need to first declare our output variables. And then we have to actually add those as parameters here to our exec statement, actually move this into a second line here, so it’s easier to read for us. And now we have person IV, and this is going to be, I can actually use an alias here as the parameters as well, new person ID. And then we have created on as new created on. And now, if I execute this, again, just like Python, right, if we start using named parameters, we have to use named parameters throughout the entire as in the entire result here. So we need to set these equal to likes, so there we go. So let’s give this a run. There we go. Alright, so now that we execute this stored procedure, here, you can see the results of the result of this being created. I’ll try to run this again here. And you can see that we get an error, because I’m trying to insert the same, the same record twice. And so we can actually do another person here.

My son loves Daniel Tiger at the moment. So Daniel Tiger tour database, but you can see now I get that new person ID and created en. And if I run this, again, you can see that you can strain is, is is preventing me from duplicating that data again. But you can see how much simpler this store procedure execution is, in comparison to running a full INSERT statement as a result. So the stored procedure does add some really useful abstraction just like what functions provide us and our normal programming languages. And just so you can see as well, all of the records that we have. We can execute this here, and then if we scroll down your I Am, and here is Daniel Tiger. So still pretty useful, overall, in terms of abstracting things away from away from our users that are interacting with our database. Stored procedures and general are going to provide us that extra layer of abstraction that the typically a database designer or database engineer is going to actually add as part of the database. And then usually, a lot of the times are programmers. So if you’re writing like backend code to a website, for example, most of the time, the the core website, programmers are not going to be writing the actual SQL queries. Those are usually done by a database administrator, usually in the form of a store procedure. So that stored procedure is added to the database. And then the programmer can just pass the data along to the stored procedure to get the results of that query and they don’t have to design those queries by hand because Some, not all programmers are going to have in depth knowledge of database design and writing efficient queries and things like that. So that provides an extra layer there when writing real world applications, but that will conclude our video on stored procedures for now. In the following video we’ll talk about another type of stored procedure called triggers.

Triggers

Video Transcription

Welcome back everyone. In this video, we’re going to start taking a look at another type of stored procedure called triggers. As I mentioned, triggers are a special kind of stored procedure, which is actually attached to an event that happens or occurs in our database. So we typically won’t be actually executing triggers manually, they are automatically executed. Whenever the event occurs. Think like a click event handler when you’re programming a user interface, and Java or Python or a website. So, events that can have triggers attached as defined by the NC SQL standard are before insert, update, or delete, and after insert, update or delete. So, before an insert, update, or delete happens, execute this query first or the stored procedure first, or, or conversely, after an insert, update or delete happens, execute this stored procedure. SQL Server Microsoft SQL Server only supports after from the standard. And then instead of before they do the instead of event. Okay, and I’ll talk about some of the differences here with instead of but before can be duplicated in T SQL by using after and are temporary tables.

So if you remember when we briefly talked about history tables, and you have like the updated and deleted or the inserted and deleted tables after an insert, update or delete happens. So inserts, records that are inserted are temporarily inserted into the inserted table. And records that are updated or deleted are inserted into the deleted table, we can actually pull that information back out using the using the after trigger. But instead of is going to execute the stored procedure instead of doing an insert, update or delete. Okay, so when an insert, update or delete happens, execute this stored procedure instead of doing that action. So that’s just something that SQL Server has kind of decided on as far as functionality goes. But most most SQL Server or most SQL Management, database systems are going to have both before and after. Because those are the standard defined as as ancy are, those are the ones defined by the ancy standard. So triggers in general, are typically going to be executed for when records are inserted and deleted or updated. But they also exist for things like data definition languages for create a table and creating databases. However, those are a little less commonly used. Most of the time, we’re going to work with our insert, update or delete queries, and attaching triggers to those for particular tables. And then with our triggers, we have the inserted and deleted tables available in order for us to track things like you know our history.

So when a record changes, we want to keep track of what the record was previously, or when a record is deleted, or after a record is deleted, we want to take a copy of whatever was deleted and stored into a history table. That is a really common use case for a trigger. But like I mentioned, the DDL triggers do exist as well, they are special types. But these in particular triggers for CREATE TABLE and create database are useful to prevent schema changes. So if you have a very large database, and you don’t want people accidentally creating new tables or messing up your database schema, we can have triggers that can be used to prevent that action from actually happening. But let’s take a look at some examples of these triggers in action. The first thing that I am going to need to do here is actually execute the setup query for part two here. So get everything initialized. Before we just finished our example on stored procedures with output parameters. I’ll take that out and replace that with a trigger Example.

Here is the syntax for creating a trigger. So CREATE TRIGGER. And then my naming scheme is going to be TR for trigger, and then the name of the person name of the table, and then what action it is. So this is a trigger for updates on the person table, right? That is my naming convention there. And so after an update happens, I’m going to insert into a person history, the person ID, first name, middle initial last name and version on from the deleted table. Because when you update, when you run an update, the record that is replaced by the new data is added to the deleted table. So a very useful way too. One way to see how this works here is to just do an insert. So if we run and inserts, we see our four records that get inserted. And then if we scroll down here, we see that our person history table here is empty. Right? That is because our trigger only our stored procedure that we created is triggered only on updates. So after an update happens, not inserts, let’s replace this insert here. If we want to trigger our stored procedure for executing, we need to do an update. So let’s update John Doe to be John Deere. And give us a run. Uh huh. There we are. And you can actually see notice our messages here, we have a bunch of different rows here actually affected starting online 12.

So here is our updates. One row that’s updated. One row that’s inserted into our history table. And then here are four rows from our select for my person table. And then here is the one row from that we select from our person history. So when the update happens, we get the updated row, here is the row that is as a result of the trigger actually executing. But I don’t actually see that as a result of my query here, I don’t have a third table being shown here. Okay, that’s just happening behind the scenes when the trigger actually executes as a result of the update. But we can see our old data. So John Doe, is in our parison history table. But now and our person table, John Deere exists. So this is a very useful way to keep track of history of updates, deletes or inserts into our tables. So triggers in general, fire for the whole set up resulting from each statement. If we run this statement here, so update person, Set version on to the current time, execute this. So here are here is my person table. Here is my person history now. But notice that I have a record now for all of the persons that just changed, right, all the persons that just changed.

So here is so John, John Doe was the original that I that was triggered just a little bit ago. But here are all of my records again, as a result, right. So now here’s John Deere. But all of these have the taint. All of these are going to have very similar timestamps as a result, or at least up here now. So all of these how the exact same timestamp now. And these were the last updated before the update happened right before the update happened. So the triggers fire for the whole set of data that is affected, right, the whole set of data that is affected. So if we go back over here to our messages, you can see the number of rows affected by each one. So here is my original, my original update. And then the rows that are inserted as part of my trigger are the rows that are affected as part of my trigger, and then the rows that I’m pulling back out using my select queries, okay, so original update query, or so original update trigger, and then my to select queries that I just ran to get the results, we can also run our we can also run triggers after inserts and updates. So in particular with this one here, I’m going to run a. So I’m gonna create a trigger after either insert or update.

So we can have a compound trigger. So we can have a procedure that is executed on on after, or after insert, update, or delete, so on and so forth. So we can actually have a stored procedure execute for more than one event. But here, I’m just going to again, insert into sorry, insert into my person history table, from inserted. And so when I inserts, right insert those select star, person in person history. So let’s go ahead and give this a run. Ah, I need to rerun my setup here, let’s let’s erase all of our people that we originally started with. And now excuse this. So here are our, all of our records that we inserted, and there’s a fourth fourth one down here, you just can’t see it off the screen. But nonetheless, here we have a trigger that can now be executed after either insert, or update. So this is a very useful way, like I mentioned, to keep track of your history of things that are being actually inserted, updated or deleted in your database. Alright, so we can also fail and update, which is very useful to do. So let’s check out this structure here. So now I’m creating a trigger, tr IU. So insert update on person. If update, first name and update last name, throw this error both first name and last name can’t be updated. So if we’re trying to both if this is just as a silly example here, but if I’m trying to update both the first name and last name method with the same query, I don’t want to allow that.

So if I try to run this query, ah, here is my error message message 5000 level 16 procedure, ie person line seven, both first name and last name cannot be updated. So that’s my custom error message that I had up here my if statement. So, trying to update both, if I if I looked at our if I looked at my history here you should see all of the original. So John Doe did not get updated to John Deere. And that record here also didn’t change record here also didn’t change, you can no see the the version on is identical to what’s in the history table and what’s in the original. So that means that is still the original record that was inserted into the table to start with. So let’s go ahead and give this a try again. Oh zero. Let’s delete this actually here, I’ve got a little too much going on. But this query actually executes successfully. Because here I’m only changing my last name, and then version on I’m only changing last name and version on not both first and last name at the same time.

So we see here, I last name gets updated. And then if I scroll here on this, you can see the history record that was inserted down here towards the bottom with the new last name for John. But this query also starts to showcase off a our ability to add custom error handling and exception throwing. And in the following video, we’ll take a look a deeper look into this example. But this overall will conclude our general discussion on triggers, which are very useful, very useful things to prevent updates or deletes from happening that you don’t want to allow. So we can actually add some data verification and validation on the database side using triggers These, these can be more complicated than what we can achieve using check constraints. Likewise, triggers are extremely useful for keeping track of data history as data gets inserted, updated or deleted from your database on certain tables as well.

Error Handling

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look into error handling and exception handling in our SQL queries. So just like and normal programming languages, like try and try accept or try catch, we can do similar error handling in our SQL queries. So in order to throw a custom error, we’re going to use the word the statement throw, and then we would define an error number message for that error and then state for that error as well. error number has to be bigger than 50,000. This is just kind of the standard number for Microsoft SQL Server. State is a tiny, tiny end with additional identification. And I’ll show an example of what that state is here in just a minute.

In order to handle these errors that are actually thrown, this also accounts for both customers and errors that are just uncaught through normal execution of SQL code. So this begin try catch is going to handle both types of situations. So begin try and try begin catch in catch, since we don’t have curly brackets, or white spacing to denote structure and SQL code, we do this we with begin and end for beginning and for try and begin and end for catch. We can use, there’s some additional mess of functions that we can use as part of the try catch, including error, getting the airline error number, what was actually error, the error message that was thrown originally, as well as the error state as well. But let’s see an example of the try catch and action here.

So I showcased how to throw an error message. Before using this line here, I’m using an F inside of our stored procedure before I’m going to create another stored procedure very similar. So create alter procedure update person, begin try. So this is inside my stored procedure, right. So inside my stored procedure begin try, and then I’m going to try to update person. If row count is zero, that means that the person that I’m trying to update does not exist in my table. Therefore, I’m going to throw a custom error message as a result, right. So if and then if I scroll down here to my try, or there’s my intro, and here’s my begin catch. So I’m going to declare a custom error message here, an error has occurred at line section such when updating the person such and such. And so there is this is also this is a way to essentially create our own custom error message. That’s the result, I don’t necessarily have to rethrow the error. But if I don’t, it’s actually doesn’t showcase this as an actual error.

So if I give this a sorry, give this a quick run. And so here is here is my print statement right here. That is this line here. But then it got thrown yet again, as as the end here. So if I comment this out, give us another run, you see that my query, actually my store procedure executes. And it doesn’t actually show as the return value from the stored procedure, not an error actually occurred. So this is kind of a tricky situation. Because if if I have a human person and executing the stored procedure, I can actually see this error message being printed out here for me. But if I’m executing the stored procedure as part of a program, I don’t get this a different state. So state one, meaning that there’s an error that actually happened. So since the state is not an error and an error state, everything supposedly went fine, even though we caught an exception being thrown. If I if I don’t have this custom error message here, then you can still put things inside of the try catch without this throw that will just catch standard errors that happen.

And we’ve seen all sorts of standard errors when we’ve been running our SQL queries, like for example trying to insert something a duplicate key or what Never that violates a constraint. And those are errors that are commonly thrown, we can catch those as part of our stored procedure, if you would like to, that is very common to produce custom error messages for the people using those stored procedures. But those are not necessarily required. But custom error handling is very useful to generate more useful error messages. Because again, right the standard error message that is produced by SQL Server is not necessarily going to be as helpful as something like this, which provides context for the reason behind the error rather than just the standard error message that SQL Server provides.

But that is all I’m really going to cover for now for Eric custom error messages. This kind of covers the gist of it. This is said typically going to happen only inside store procedures, you’re not really going to have throws and catches or tries to try and catch in anything other than a stored procedure. But again, custom error handling just like what we see in programming languages is a really great way to handle unexpected exceptions and provide error messages that are more meaningful to the people who are using your functions.