SQL Stored Procedures - What They Are, Best Practices, Security, and More...

Microsoft sequel server can be a scary place all this technology and terminology surrounding what really should be a simple topic storing and retrieving data the thing is the corporate world connects Microsoft sequel server to c-sharp a lot that means if you want to learn c-sharp to use in the business world you should know Microsoft sequel as well one of the features of sequel I 10 talk about a lot is stored procedures in this video I'm going to introduce you to what a store procedure is and how to build one but that isn't in the most important part of this video I'm going to cover why you should use store procedures and how they can make your database move to the next level for those of you who are new to my channel my name is Tim quarry and my goal is to make learning c-sharp and other related languages as easy as possible for you if that sounds good to you hit the subscribe button if you want to go further join in my list by clicking the link in the description below that's where you get insider information and discounts on my courses now let's jump right in the sequel here I'm using the latest version of sequel sir Manta studio which is version 17.1 against a 2016 version of the sequel server developer edition now all of this is free developer edition is free and sequel server management studio is free now guide database here called samples into the sample database we have just one table the dbo dot people table into this table it's really simple as have eight records and they are just first name and last name of course an ID as well so that's all we have in this database right now just one table eight records really simple so let's start by creating a new store procedure and then we'll talk through what exactly it is now I could do it right here and I will do it right here inside a query window but if you want to you can go programmability expand up and say store procedures right here inside here you'll see that i have a whole bunch of system store procedures but no actual stored procedures none that are mine now system store procedures come from Microsoft so these are Microsoft provided there's a lot of them this videos not going to go into what all these are but it's an awful lot of tools in here to help you with your database okay there's an awful lot of tools in here that will help you do different things but for now we're going to concentrate just on user create store procedures so as none right now and we could say right-click on so procedure and say new store procedure and creates a template for us at templates kind of nice I especially like this right here because it reminds you to comment on your store procedure what exactly does is who create it and when that's really helpful so with this template you can just fill in the different parts however we're going to start from scratch so we'll leave it open just we can see it and reference it but we'll come over here to this blank query window now what is a store procedure a store procedure is simply a precompiled bit of code so for example normally we might say something like this select star from DB o dot people that's a pretty standard pre simple query if you run that you get these eight people so this is a standard query if you one that would be dynamic or easily changed now if you put some like that's inside your c-sharp code that can be a problem because what if you specify the columns specified ID first name and last name all right so this when you specify which is a good thing to do into a star so if you specify these three columns and later I add a fourth column for email you wouldn't have that in your code so you'd have to go over to your c-sharp code open it up make the change and then recompile if you wanted to have that in your code so but that's not really green why you shouldn't have this new code and we'll get into the reasons why you would do a store procedure over this later on but for now let's look at how you do something similar in a stored procedure so let's cut this out well I should take away our window down below and third essay create procedure dbo that's the owner which by default is dbo notice over here for a table it's dbo dot people that's the owner of the table so dbo dot that's a namespace and we'll call this SP people underscore gets all now I'm starting with SP to indicate this a stored procedure versus no prefix for it as a table so my way of doing things is just to say SP but what you do not want to do is you do not want to say SP underscore and the reason why is because SP underscores actually reserved prefix and that's reserved for system level stored procedures store procedures that can be accessed from any database and so we we don't want to use that prefix we don't want to step on toes for a database specific store procedure so key takeaway there is don't start with SP underscore SP is fine though so SP people underscore get all and this is the way I name my store procedures I start an SP to indicate the store procedure then I give it the table name of the primary table in question and I'd say primary because you have two or more tables that you may be working with but in this case it's the people table so therefore SP people and I have an underscore and what does in this case is going to get all the records from the people table so now I say as begin and then I'll do my actual store purse or my actual call so select ID first and last name from dbo people and then at the end I will say end and that is my create procedure statement this creates a stored procedure so it's the same statement as the same right there it just wraps it in this create procedure so let's call this to actually create the store procedure now if commands completed successfully and if we refresh down here we'll see I have DB OSP people underscore kits all now so now if I were going to create a new query window in test samples database I could say exe c DB o dot SP people on our score gets all now say yelling because I don't it says I don't know what that store procedure is but if I do a ctrl shift our it will refresh the cache of what items are available and now it says ok no that is so calling us store procedures a little bit different than calling the actual code so instead of things select from we just say execute or exe c DB o dot and the name of store procedure so let's run this notice it returns the same number of Records it's returned the same values everything what's the difference between a store procedure and a direct call well this direct call right here has to be compiled and then run now Microsoft sequel server is getting more and more intelligent about how to compile these quickly and how to remember even dynamic sequel thing like this in memory so it's it's compiled with called multiple times but a store procedure is always compiled and so if you're going to call this it will be a little bit more efficient to call store procedure than it will be to call a dynamic sequel statement the first time and again we'll cover more of the benefits and drawbacks near the end of this video byline is point out a couple of those right now now in our store procedure it may not be obvious but down here in our messages it says eight rows affected that's actually a second set of data that's returned with this stored procedure now often we don't need that in fact most the time we don't need that and so even in the template by default it adds this statement right here set no count on that's not something we did here so we're going to add that so we don't return the number of rows affected so let's change our store procedure and say sets no count on now I can't do a create here they've already created a store procedure but I can do an alter if you an alter procedure what does it changes the store procedure to this new value or as new setup so let's execute this and now to come back over here and execute this again it returns all eight statements like before and the messages it says command completed successfully it doesn't give me the count or a number of rows that have been affected so that makes the calls that a little bit more efficient and it helps not mess up your your code when I'm asking for a set of data and you're getting a set of data and rows affected alright so now let's look at creating another store procedure so I close out the template and you know I will close this out as well they'll create a new query window I'd like a few extra lines in my initial window let's say create procedure DB o dot SP people underscore get by last name we'll set it first the same way as begin select ID first name last name from DB o dot people and we're going to do an end here now I want to limit this by last name so I just say what's the last name that I want to limit by and that's where we pass in a variable at last name and then the type so at last name n varchar' 50 because at the length of my field if you don't know like you're feeling go to people columns see the last name is an N varchar' 50 so I'm passing in a Unicode variable length character string they call last name and so in here I can say where last name the field so the field in DB Oh duck people equals last name at last name which is the value being passed in so this is a variable so given a variables name I have to start the axe symbol and then I give it the variable type which in this case is a Unicode variable-length character or a string essentially with a maximum length of 50 characters so now that I have this variable populated I can use it to limit my query in this case where last name equals in the past in value by semicolon at the end let's create that store procedure and now I can call this so it's a control shift are now comment out this one so don't accidently hit f5 and run it as well exe c DB OSP people underscore get by last name but that says you know what you can't do that yet because it expects the parameter last name which was not supplied now i could say at last name equals like so and that will work but if I'm going to pass in all the parameters now pass them in in order then I don't have to specify the name I can just pass in the actual value so this case it says there's only one parameter so the first value being passed in that goes in that parameter so let's run this and notice that this is now four people long Tim John Chris and Maggie Cory so I've limited my list based upon last name which was passed in as a variable now if I wide to I could pass in more than one variable so for example let's alter this and we'll actually pass in first-name as well they put comma after my last name and a type and the next line output first name varchar' 50 now usually if I put first and then last name but I'm assuming it's stored procedures already in place in which case I wouldn't want to mess up the order because remember I said it comes in in order so if I had said you know what I want first name first and last name and I already had existing called out there they were calling a stored procedure that would put Cory in the first name instead of the last name and that would be a problem so let's alter that we'll run it and now if we do ctrl shift our it's going to yell us and say you need the first name parameter so I'll say Tim and now if I run this isn't do anything different and the reason why I can actually use the first name variable but it still took both values so now I can say where last name equals last name and first name equals first name we can even do an indentation here just make it a little nicer we can alter that and now if we run this we get one value back so that's the basics really of a stored procedure you have the initial line which either create or alter and specifies the name and then you have right below that name you have your variables separated by comma if you want to do a default value you can just equals the default value then you have your add statement and a begin and end and everything between the beginning end will get executed so so the actual statement now that means that your store procedure can be a insert a delete and update a join a select all those things can have an insider stored procedure in fact you can have multiple things happen inside your store procedure you can do both an insert and delete think about bank transaction so you're transferring money from a savings account to a checking account well in a bank transaction what would happen is it would remove it from your savings account and it would add it to your checking account all in one transaction so that might happen inside of one store procedure so they're not limited to just one call now here's the other nice thing about a store procedure and that's that it's a one transaction meaning that you can say this transaction has to fully happen or I roll it back and when I get any transactions into this in this video but essentially what you do is start right here and say begin trans and they have to complete the transaction and so on now what that does is allows you to say the whole thing has to happen as one chunk think again back to that Bank example what if the stored procedure crashed halfway through that process it's removed the money from your savings account but then it crashes before it puts it into your checking account you wouldn't want your mind to disappear a store procedure allows you to have that transaction that atomic level transaction happen in one place you can do it in code but the risks of problems are much much higher so another great benefit of a stored procedure so that's really all there is to creating store procedures so far I've created - we've got the get all and I also get by last name which is kind of misnomer now is you have to get by last name but ask for last and first name but oh well not a big deal so that shows us how to do a store procedure but let's talk about some of benefits of doing store procedures and the very first bath I want talk about is security and this is one that a lot of people miss so if I have access let's close us out if I have access to do this let's run that so here I have these eight records I just done a select star from people well what if there is a social security number or a credit card number or something else sensitive in one it columns well if I have access to all the columns in DB ODOT people I can see all those columns now a lot of people say that a login to a database essentially be an administrator and they create all their logins with administrative level access that's a problem now the next thing I do is we'll say well I'll only allow them to have access to certain tables well that's great but whatever they have access to they have full access to usually because if you want to allow the application they use to insert data you have to allow them to insert data using the login so essentially if they're using an application unless you take great pains even so it's kind of iffy if you can do this if they use the application and that the permissions of that user that talks to the database if they use those same permissions in sequel server management studio they can do a lot of stuff essentially the same stuff the application can do now the application may say you can't delete this record because of this business rule in sequel server manages to do that business rule doesn't exist therefore you can go ahead and delete that user there been a number of times I've used an application where I couldn't do something because it was locked down but I really needed to do it so I use those same permissions to get in the sequel server manager studio and go ahead and do that action now wasn't being malicious but those permissions really gave me more access than the designers originally intended for me to have so that's why I'm not a big fan of giving out credentials that have all this access in fact I don't like people to even see what tables are available or what data is in those tables I don't like them to even have read access to my tables now the question is how do you do that and yet still give them access to use your application and this is why I love store procedures and this is why I default to using store procedures first before I move away from them so let's start by creating a new role for database Mattox not familiar database security I'll try and walk you through it in the simplest terms possible but the reality is you don't need to know a whole lot about security in order to really lock down your database so let's start by Korea a database role now a role is it's a name for essentially kind of a group where you say these permissions are given to this role and then anyone that has that role has those permissions so for example a role might be delete users and then that role has access to delete from specific tables then you could say tim has the role delete users which means that tim can delete from these specific tables so let's create a new role for us to use and it's really simple create role now role is specific to a database or at least in this case it is we're not going to a serve a role to a database role so create role and if you notice down here in the security you'll see roles you'll see DB underscore and that name data writer DD l admin deny data writer and all the rest these are the standard roles so I create a role I'm going to call it D be stored procedure only access I won't do DB underscore the same kind of principle at the SP underscore I just don't want to have that habit and I also know now that this is my role not a role given to me by Microsoft so now I can just hit the execute button and that creates that role if we come down here to database roles and hit the refresh button will see that I have the DB stored procedure I pull it store not stored oops there we go I'll create a new role and I'll delete that old row so let's right-click and say delete hit OK and now we have DB stored procedure only access so now that I have this role I can now say grant execute to and that role name so this gives this grant right here saying I'm going to give you something give you access to something in this case I'm going to give you the execute action to this role meaning this role will now be able to execute remember that when we call the stored procedure we said exe C and the store procedure name so that's execute that's the permission we're giving it and I run that and command completed successfully so now that role the DD services your only access role allows a user to call the store procedures so now we need to do is come down to our server not the database now the database users but the server users so when our security for server logins we're going to right click and say new login going to call this login bill now if you're not familiar creating users I essentially have two methods for Korean user one is windows authentication so I'm currently logged in under the name Tim so if I were do a search for Tim I would find that user I can use Windows authentication meaning if I'm logged into Windows then I will have access to the sequel server I don't want to do that I want to create a log in the sequel specific meaning using the sequel server authentication not Windows so I'll say Bill the password I'll say is Bill as well which actually the bad password is a bad login and password combination but I'm going to uncheck the boxes that enforce password policy meaning the password won't expire and I'm not going to have requirements of a application a lowercase letter and number and a special character or in a certain length so I'm not going to do any of those I'm going with login and password both of Bill come down here as server roles nothing just let it be public user mapping in the samples database I'm going to check the box says DB store procedure only access so I apply this that rule to this account so now bill will have that store procedure only access and I hit okay so now let's close out so I disconnect from this server and reconnect close this out as well going to reconnect but say of logging and windows authentication which is what I do my Tim login has admin permission to this server instead I'm no login with sequel server authentication logging is Bill password is Bill I hit OK I go databases open up a sample database and look at the tables notice there's no tables in this list let's go to store procedures I see to store procedures so let's open up a new query window I'm going to try and first of all cheat the system I know that there is a table called people as a select star from DB ODOT people intellisense doesn't work but no big deal hit f5 and it says select permission was denied on the object people so I don't have access to read the people table doctor came and see it but left it is instead DX CC DD OSP people on a score get all their or all the people I could also do exe c DB OSP people get by last name pass in quarry and at first name of john and i will run this highlight just that one then I get just John Cory back so I can access I can run these store procedures but I don't have access to do things like this delete from DB o people nope can't do it so you see the power here with just a couple of commands the create role and grant execute to that role and then applying that role to a user I have said that this user these are Bill can only call store procedures that means that bill can't do anything except whether explicitly said he can do so he can't delete rows he can't insert rows he can't update rows he can't do filtering by only first name all he can do is whatever store procedures I already granted him to do now that execute role allows me to say that he can do any store procedure so there's a store procedure in his list bill can do it so if I have stored procedures said SP people underscore delete all and delete every row from the people table bill could run that so if you want to be more granular instead of giving that grant execute two you could specify only certain store procedures that are granted to execute to so you'd say only these store procedures can build access or can this role can access so you can get rid granular if you want but right out of gate with two lines of code create role and grant execute to with those two lines of code you've created a rule that locks down your database to only what you've allowed people to do so that in my mind is a really compelling reason to use store procedures because if you use only store procedures to access update and delete data then you can lock your database down very very simply very quickly and yet fully to the point where they can see which tables are available so that's a big benefit in my mind store procedures are also faster than writing select star as far as executing inside of sequel server not all the time sometimes are just as static if it has stored that same select statement but in general store procedures are faster the other thing that still procedures are benefit of store procedures is they're clearly defined you know exactly what's going to happen at the sequel level because it's defined exactly what's going to happen in one more bit of security that comes along with store procedures is the lessened possibility of a sequel injection attack it's not a complete security but it's much much safer to use a store procedure when it comes to protecting against sequel injection attack so at this point you've seen a lot of benefits of store procedures not all of them but a lot of them they've also kind of heard my view on why I use store procedures the security the speed the reusability clearly defined protection against sequel server injection attacks and all the rest but you will hear some drawbacks to store procedures so not cover them and at least talk to you about them and then you can decide if the drawbacks are worth it so the first drawback is probably one that affects me the most the one that I wish had a better story that it does and that is this store procedures are not under source control your c-sharp code you can put in git and then you can say it's version and under source control and you can have different branches you can have all this stuff around protecting your code and making sure your code is discoverable and making sure you have copies of the older versions of your code and all the rest of stuff that that the source control provides but there's not a similar thing for sequel server does not get for sequel server well at least not really now there is a paid option you can do and that is from Red Gate and if you can afford it it's a great option Red Gate provides the ability to have source control right in our database it's really powerful it's really easy to use and it kind of works in the same flow now the hard part here is databases are radically different than code and so there are some quirks that come along with this so that's one option but it's not cheap it's a little expensive another option would be a little bit cheaper and also has more features to it will be going the route like the sequel examiner suite sequel examiner will take a different approach to source control and that is it does differences between your database and say your development database or your development database and your production database and says here's the changes that have happened and creates a script for you you can actually lock that script into your source control and run it from the sequel examiner suite so it's a little cheaper it's a little more full-featured it does more than just source control it actually does some comparison you can does data comparison and it creates some great statements that allow you to recreate the changes in a new database and the reality is you should be making live changes on your production server in fact approximately shouldn't be making live changes on your staging or even development server instead make your changes on your local database and then start that process early of committing your code and replicating it to your development database and on through to your production database so there are ways to do source control well with sequel server it's not the same and it's not the great story we have with c-sharp or other programming languages where source control is just a natural part of the process but it can be done it can fit well into your deployment process and it will it can work with latest third party tools or even just manually the next drawback that I hear a lot is one that I have a little bit of a fundamental conflict on and that is this business logic doesn't belong in a database the databases job is to store data you shouldn't have logic in your database and I really don't agree with this and here's why I see these store procedures down here really as the user interface to my database so I do allow the idea that business logic could be in these store procedures now I don't include a ton but I do like the idea of checking the data before it goes in the database I don't like the idea of trusting anyone when we talk about creating user interfaces in c-sharp one of my tenants they always talk about is don't trust the user if you give them a first name field they're going to put in their age they're going to play nothing they're going to put in a book of information whatever it is that's being wrong the ground tries not necessarily maliciously just because and users do wacky things that I don't expect in the same way why would I trust an application why would I trust it only an application is talking to my database again let's come back to our execute only with the store procedures if I didn't have that and someone ran this code right here right in SSMS it work they could drop or delete all the people from the people table table that doesn't make me feel warm fuzzy so I do want to have some sort of protection against that that tactility called business logic I'm okay with that if you're familiar with building websites especially JavaScript powered websites we have the idea of front and validation meaning in jQuery we have the jQuery validate or with other tools we have other ways of validating but essentially if you have a form and you say for first name field nothing else and you hit submit before actually submit it's going to say nope you can't submit because you have put something in that first name field that is client-side or front-end validation the JavaScript is saying you can't even talk to the backend yet because you've not given me the right information however once you give the right information and JavaScript says yes that's a valid data and you hit submit the backend does validation as well and the reason why is because the backend can't trust the front-end because you can bypass JavaScript and you could call the form directly instead of calling it through the friend form and there's a lot of ways of getting around that friend dilation and so we actually do validation twice especially in asp.net MVC so we do validation on the JavaScript side and then you pass data in we do validation again and then we do something that we know that data is valid well in the same way I kind of want to do some validation again when you send data to the database because I can't trust that your application fully validated data and I can't trust that's an application that's sending their data so that's why I kind of push back on that idea of no business logic in the database and the last drawback I hear sometimes is that tool like entity framework and other large ORM s don't like to use too procedures they can they don't like to they like writing their own select and insert and update statements and here's my deal I really push back on the idea of using entity framework if you've watched my other videos you'll see that I really don't like entity framework now it's a really amazing tool like itself done some amazing things with it but at the end of the day it doesn't allow me to secure my database it's slow and it writes the sequel for me instead allow me to specify exactly what I want in my sequel now it does save a step in that you're not coming over here to SMS and writing queries but the drawback there is you've got this black box of I ask for data and it comes until it doesn't once it doesn't I've got a probably got a debug so at any framework add a lot of complexity at it slows things down tremendously and it doesn't allow me to secure my database the way I want so that's why I don't really care that you can't use any framework to its full potential with this method now you could use entity framework still just doing only in-store procedures but it's not really benefit there so those are the drawbacks source control story isn't great business launching the database if that's a concern for you it's not for me and again not really good story for entity framework but personally I am ok with that so those are drawbacks the benefits they're fast reusable they're clearly defined they're safer when it comes to sequel injection and it's much much more securable from a database perspective so those are store procedures I think it's a really compelling reason to use them and I think they're actually pretty simple to do but I'd love to hear your feedback on have you used them what do you think have you seen any other benefits or drawbacks oh you'd like to talk about or giving our questions so leave all those those questions and comments down below I'll try to answer every single one that you post don't forget if you haven't already I love a thumbs up to this video and also subscribe thanks have a great day

Channel: IAmTimCorey

Views: 287,556

Rating: 4.9497375 out of 5

Keywords: .net, C#, Visual Studio, code, programming, tutorial, training, how to, tim corey, C# training, C# tutorial, sql, microsoft sql, mssql, ssms, sql server management studio, stored procedure, sp, sql security, best practices, sql2016, sql server 2016, developer edition, free, query, select

Id: Sggdhot-MoM

Length: 44min 33sec (2673 seconds)

Published: Wed Sep 20 2017