Out of all the years of watching computer and programming tutorials, you teach things so much better and explain everything and explain it in a way that makes sense, thanks!
Im a professional, experienced developer and theres always something to learn from your videos, even with 'easy' topics such as this one. Thank you for your videos. Great work.
thats why its so overrated, in uni they think we need to know everything xd then i see people saying who are experienced developers that they learn from the simple videos xD
Seriously this is the best channel about programming, at least for me, hope you continue with these videos that are really helpful. Thank you very much!
0:00 - Intro 1:12 - Demo Data Base overview 2:00 - Creating new stored procedure 7:35 - Calling the stored procedure 10:11 - Changing existing stored procedure 11:12 - Stored procedure with variables 16:51 - Stored procedures: Recap 19:54 - Benefits of stored procedures: Security 22:57 - DB security: Users and Roles 32:49 - Other benefits of stored procedures 34:18 - Drawbacks: No source control 37:36 - Drawbacks: Business logic in DB 41:48 - Drawbacks: Using Entity Framework 43:26 - Summary and concluding remarks
I've been using Stored Procedures for a least 20 years now, so watched more out of curiosity than anything else. However you never know what you might learn and believe me I have learnt plenty from your wonderful videos. The best of this for me was your commentary on entity framework which just reinforces my belief that I'm better off doing what I've always done. Each to their own I guess, but with so many people now only using entity framework I feel like a dinosaur because I'm not.
We're not using EF in the company i work for and it's so difficult to find tutorials for adding that data layer without it! So definitely not a dinosaur 😆
I love working with stored procedures. The front end guys would argue with me how having it in c# was better. Until you try to read their logic and say those 20 lines can be done with a single case statement. Thanks for sharing great work
I'm a MySQL user but I've learned a lot from this video and hope to apply my understanding to MySQL. That bit about granting regular users only access to stored procedures but not access to entire tables, and then only granting them an execute permission, is the crux on which I hope to predicate a solution to a problem I'm working on. Sorry I know I should not end a sentence on a preposition. Whereby, I gotta make some data accessible to people who don't really know how to write SQL. Hopefully I'm smart enough to do the job. Thanks for the video sir.
I have a clear pic of what a stored procedure is by watching this amazing content. I clicked on subscribe option before even close to watch this video. Please keep uploading...
Hi Tim, I've watched a dozen of your videos and they are very useful. I'm also learning from you how to explain complex technical ideas in plain English to non-technical audience and even to junior programmers. Thank you! From Guangdong China.
Amazing video! Not only do you explain what stored procedures are, but you also give detailed background information and use cases. Keep up the great work!
Tim, thanks for another great video!! Although I am now retired and never employed formally as an "engineer", much of my career was around managing a business that was highly secure and mission critical service network. Stored Procs are the way of life in such environments and, like you, NOBODY felt comfortable enough to executed the Entity Framework in such an environment. The business had to be able to fix ANYTHING that happened in the shortest time possible and performance was an issue. With about 1.5M connected end points sending in asynchronous signals, you can imagine how much data was coming into the network and why performance and security were so important. Kudos to you - you are correct about security!!
Ok, i reached the end of the video now, and i just have to THANK YOU AGAIN! Awesome, really, you made it click so many times in my head and filled me with confidence, awesome awesome awesome! Thanks! Please continue doing this, i think i will watch a good chunk of your videos from now. :)
This was one of the most understandable, cohesive, well structured and best introductions I have seen in my whole life. I came with no understanding of anything besides SQL-statements and programming (java etc.) and I feel, I have a decent idea of procedures and role-management now.
Thanks for pointing out that security matters! It's such a big deal today yet I still see so many tutorials where they completely disregard security matters.
Fantastic video! Just had a recent interview for web dev in the financial sector and I have fairly little experience with T-SQL. One of the things they asked for me to do is additional research on Stored Procedures and this is the most clear and comprehensive guide I've seen thus far. I really appreciate it and I hope you're happy to hear how helpful you are for helping me get my next job!
This, and the rest of your videos the videos on this channel, is absolutely golden! The SQL course on your site is worth way more than you're charging. You and Derek Banas are The best source for this stuff. Thank you very much, Corey!!
Very easy to follow introduction to stored procedures. Just enough depth to get the general idea without getting bogged down in the details. Thank you for the great free content you provide!
at 24:00 you created a role : dbStoredProcedureOnlyAccess and granted access to dbStoredProcedureOnlyAccess Role. But how does SQL Server knows that It is stored procedure only acces role?
@@IAmTimCorey ohh okayy... Understood. So we use exec command only for stored procedures right. Thanks for the prompt reply. Your tutorials have been really helpful.
I never watched about procedures before. And I thought it will be necessary to watch several tutorials. But you made me understand watching only one. Thanks
Dear Tim, I think You have a precious gift to explain concepts. also, using Cached execution plans is one of the most important benefits of Stored Procedures
Company I'm working for now uses C# and SQL Server. The reporting team wanted access to use sql to make reports and such because they are pretty tech savvy too. So we just created a "Reporting User" and gave it "db_datareader" only. They can query what they want. No sensitive information... its a database of results returned from robots. There are only 4 of us that can actually modify the database in any way. As far as naming convention I still use "sp_" if I'm creating it in master for a common use across our different databases (more things like backup, turn replication on for a database, etc.) Rule of thumb any changes that can be damaging we run the stored procedure to back up the DB before making changes. Yes, databases are still backed up every night though. If I screw something up like an update without a where clause (i've did this before) I can copy that bak. to my local machine get original values to update the production database back to how it was at the moment I backed it up. Worst case we can restore the backup directly in SQL server but with replication this means pretty much bringing that database down for about 1/2 hour or so and resyncing. Fun fun :)
I'm a DBA and it's good to hear a DEV perspective advocating SPs. :-) ORM tools like entity framework seem to be quite fashionable with developers. I get the appeal but you give up a lot by not using stored procs - explained very nicely in this video.
Thank you tim for all your amzing content, i got a job in IT and your videos were super helpfull both in recruiting and during actual work. Best of luck for you and thanks for helping me turn my life around ! Cheers from Portugal !
This is the first time I've ever seen a meaningful use of stored procedures.Thank you so much Tim Corey.The sql databases are most of time shrouded in secrecy and most of developers just know the fundamentals of them .I have been struggling for longtemps to level my expertise in sql databases up to my mastery in Mongodb but just in vain: I am just left with fundementals.It is really crazy.
Thanks a lot Tim about this great video. I personally build all my ASP.NET projects on simple and complex stored procedures and I will always keep using them, all the reasons you have mentioned are correct 100%, I feel very confident and relaxed when I use stored procedures, and once the stored procedure does its job, I know that it will never give me any surprised error in the futre, they are very controllables, what you write is what you get and no code is behind the scene also they are independant from each others. Great job Tim. Please keep it up as usual.
I had many deliberations on sp. Many dissuaded me from using it. I was convinced that sp provides security. But your video provides a very structured reasoning
I was just preparing to move my classical ASP app ( including SP’s) to MVC with EF. Of course, I’m pleased with speed of execution of SP’s, especially in reports, but DB dependency is another consideration for me. I watched so many videos, even bought some trainings. Then I watched this video. It’s a perfect eye opener for me. I’ll watch your tutorial at first, then decide which way to go. Thank you this valuable information. Best regards.
I didn''t know about the sp_ naming prefix. I gotta stop naming stored procedures that way. Thanks for the help sir. Very helpful for a beginner in stored procedures
I am a beginner of SQL sp coding. Your video is super detailed and concise. Thank you so much for your help. This is exactly what I need now. Instead of linked server, I would try a new SQL authentication account and use as connection string credentials in our custom script. I will update here on the results.
42:28 As far as I know you can execute custom sql since ef 5 or so. I believe the call was context.Database.SQLQuery where you insert your sql query as a string for example
For SQL Server newbies like me that always used Windows authentication locally, when you create a new SQL Server User you also need to allow SQL Server Authentication in SSMS -> Select Server (right click) -> Server Properties -> Security -> Server authentication. For some reason mine was set to Windows mode only by default.
Hi Corey, excellent video. Question though...how come the user gets denied access to EXEC if my stored procedure has a select statement with joins from different schemas. "The SELECT permission was denied on the object ___, database ____, schema ____. What is the solution for this?
Thank you very much Tim! I learnt so much about stored procedures in 44 minutes than I did in my entire semester. Please upload a video about triggers and functions if possible. I'll be looking forward to it. 💪
35:35 Why was the following approach to putting the procedures under source control not mentioned? For each procedure needed, write a .sql file that adds the procedure to the database. When the application starts, if files exist that have not been run against the database, make the application run them. Check the scripts into Git. The application can call all stored procedures whose add file has been run. Works for me.
The reason why is because it was outside the scope of this video. We were talking about stored procedures in SSMS, not about how to put them into source control. The reason why is because SSMS does not have source control by default. That's usually a paid add-on. However, I did do a separate video on how build and manage your entire database in Visual Studio, including allowing that project to be managed in source control: ruclips.net/video/ijDcHGxyqE4/видео.htmlsi=9LgbwvAF45sxWTfu I also then used that technique in the TimCo Retail Manager series here on RUclips.
Just want to say, I love your videos and I totally understand where you are coming from. I've worked on systems that used both SP and Linq to EF. Using SP when performance becomes an issue. But Linq is definitely very addictive because it's easy to read and compose complex queries conditionally. But it's also where the performance issue crops up because it's so easy to nest subqueries without thinking about it.
at 15:57, where you say "Don't change the order of Parameters in case the SP is in use", isn't useful. If an existing call to an SP finds and extra, unexpected parameter, it will still fall over. Any Parameter change will always require a complete check of all calling processes.
@@IAmTimCorey Ah, but then we get into the headache of "loose parameters ", where you risk security and reliability due to "unexpected combination" consequences, and worse, have to build in the logic to handle empty parameters. Usefull sometimes I know, but a real source of pain.
I totally agree with you, we can use visual studio data tools project to use compare changes and have it in source control. Similar to what Red gate does.
Love your videos. I am very new at creating web apps with MVC in C# and I totally agree with you that EF is not the way to go when working with a SQL Server database. I have over 25 years of experience developing SQL server databases and stored procedures are the way to go for all the reasons that you mentioned. Now I need to learn how to used SP with a WebApp.
I completely agree with your position on the 'no business logic in the database' edict. In fact, I would argue that a relational database IS an implementation of business logic in itself, since each table represents a logical data type. Entities such as 'customer', 'product' and 'customer-order' are an integral part of most business models!
Exactly, “business logic” encompasses the whole application, both the database and the UI, then each layer performs the role best suited to it to govern that logic
Hi Tim, im a fresh IT graduate and started learning C# language. I loved watching your videos because you just dont show how to do it but also you explain how it works. By the way in terms of security, do you have a videos where you show how to validate user inputs from client side just just before accessing to a database. Thanks.
Not specifically, but that sounds like a good suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
Thank you so very Much Tim. You have contributed so much to my career, truly i can't thank you enough, though i am yet having not much enough to subscribe to your paid programs and articles, i am looking forward to someday. This video is great, i often used Stored Procedures in my code but i found them to be time consuming and extra work to face so i began to do more of the unsecure query, query thing you know... but with this video, i think i will redirect my energy towards understanding it more and leverage on its benefits. Once again, Tim, Thank you a ton.
Hi Tim, this is a really fantastic video and you do such a great job of clearly explaining everything! I’m currently studying my MCSA 70-461 and this has helped massively! I wonder if you have any videos on Triggers and Transaction Isolation Levels?
What if I'm developing desktop app that connects to server in the same network. Server is own by my customer so they have 'sa' account. They can just do whatever they want with tables that my app creates. I want to create database on their server and I want to give them access but not to whole database. EDIT: After looking up for answers I don't think it is possible. If they have 'sa' account they have access to everything and I can't do anything about that. Even if my app would create new user and new role.
Came for the Stored Procedures, now it seems that I'll need to hang around for the C#. This is one of those times when I wanted a better explanation of one thing and wound up with a (needed) kick in the pants about something else. But I'm taking a deep breath and clearing some time on my calendar. 🤔
you can manually add all your procedure scripts to source control. No extra cost except that the source control commands must be done from outside of management studio.
What about when you need to run a dynamically created query from the SP which has been setup to use a execute only role? Is there a workaround for that because you will get a permission denied. Thanks!
I somehow agree with the business logic in the database. But how we would design the validation from front-end to API to database? Of course the API will have all the input validation and business logic but we will do it again in the DB? Or should the other applications that wants to use the database should use the API?
Think of your data storage process as a castle. Layers of defense. If you focus on just one layer, it can be bypassed and your data compromised. So validation logic goes in the UI (for quick "don't do this" feedback), in the API (for "I'm not going to save that" actions) and a bit in your database (possibly. For "I'm going to stop the worst stuff"). Mostly it will be in the API layer, but there can be a place for it in the database. It all depends on your use case.
How about using entity framework for small queries on small tables, and Stored procedures for large data including joins etc. Is it good to execute stored procedures using entity framework in that scenario?
It all depends on how well you know Entity Framework. If you are really good at it then yes, that works. If not, I would recommend creating your SQL outside of your C# code.
I do like the idea that business logic, i.e. data validation is built-in to each dB, as it might change from one dB to another based on business needs..
Having some data validation on the database can be a good option, since you don't always control who calls your stored procedures. Just don't go crazy with it and bog down your server.
A story I heard before is there was a firm using their database to store high resolution photos as BLOBs in their database and one day was saying their database was thrashing constantly and crawling. It was because someone coded a select statement as select * from whatever that contained the BLOBs when just wanting to peer at the meta-data stored with the BLOBs. Because they had a direct select interface to the data, they could do that and wrecked the performance.
Yep, you have to make sure you monitor the performance of your queries. Knowing how to test and evaluate queries is something that every person who is writing queries should know, whether they are creating stored procedures or using Entity Framework.
@36:40 This is out of scope but it relates to what you said about not making changes to a live server. Is there a guide that you know of that can walk us through setting up different DB servers for stages of development (e.g., local db for testing, development db server, production db server -- those you alluded to)?
I've used the "backup/restore" feature to replicate the state of a live db server onto a local machine for testing C# stored procedures. However, I don't know how to keep the servers synchronized (so that changes that add records to tables will add records to my db)
Yes, I will be doing a video on putting your database into source control and deploying it to dev, staging, and production. Look into the Data Tools in Visual Studio if you want to get a jump on it.
Great video! I have a question though. When you are speaking about why you like to use stored procedures because of security considerations - can't you reach the same goal if you use View and why is it neccessery to be stored procedure? You basically implemented simple select statement in this stored procedure so what's the difference between a view and this stored procedure?
A stored procedure is a different element. I find myself not using them that often. You cannot use parameters with them, they cannot be as easily secured, and they aren't precompiled in the same way.
Hello Tim, would you say that creating or using stored procedures is the solution to prevent SQL injection attacks? What more is needed to prevent the attacks? I’m also trying to add stored procedures to a Db that was created by EF.
It is a major part of the solution, yes. In order to fully prevent SQL injection attacks, you need to have layers of defense. Scrub your inputs on the client-side. Don't allow your users to form SQL queries directly or indirectly. Sanitize your inputs again on the server-side. Use Stored Procedures or at least SQL parameters on your SQL calls so that extra commands cannot be passed to the database server. Limit the permissions of the connection string account that accesses the database. Those are all steps you should consider when preventing against SQL injection attacks.
Out of all the years of watching computer and programming tutorials, you teach things so much better and explain everything and explain it in a way that makes sense, thanks!
I am glad my training is helpful.
Im a professional, experienced developer and theres always something to learn from your videos, even with 'easy' topics such as this one. Thank you for your videos. Great work.
I appreciate the kind words.
thats why its so overrated, in uni they think we need to know everything xd then i see people saying who are experienced developers that they learn from the simple videos xD
Seriously this is the best channel about programming, at least for me, hope you continue with these videos that are really helpful. Thank you very much!
I will definitely be continuing. I'm glad you are enjoying the videos.
0:00 - Intro
1:12 - Demo Data Base overview
2:00 - Creating new stored procedure
7:35 - Calling the stored procedure
10:11 - Changing existing stored procedure
11:12 - Stored procedure with variables
16:51 - Stored procedures: Recap
19:54 - Benefits of stored procedures: Security
22:57 - DB security: Users and Roles
32:49 - Other benefits of stored procedures
34:18 - Drawbacks: No source control
37:36 - Drawbacks: Business logic in DB
41:48 - Drawbacks: Using Entity Framework
43:26 - Summary and concluding remarks
Thank you kind sir!
I've been using Stored Procedures for a least 20 years now, so watched more out of curiosity than anything else. However you never know what you might learn and believe me I have learnt plenty from your wonderful videos. The best of this for me was your commentary on entity framework which just reinforces my belief that I'm better off doing what I've always done. Each to their own I guess, but with so many people now only using entity framework I feel like a dinosaur because I'm not.
Awesome! I'm glad it was helpful.
We're not using EF in the company i work for and it's so difficult to find tutorials for adding that data layer without it! So definitely not a dinosaur 😆
Sir need your help to understand procedure n fuctions
I love working with stored procedures. The front end guys would argue with me how having it in c# was better. Until you try to read their logic and say those 20 lines can be done with a single case statement. Thanks for sharing great work
44 😮
I'm a MySQL user but I've learned a lot from this video and hope to apply my understanding to MySQL.
That bit about granting regular users only access to stored procedures but not access to entire tables, and then only granting them an execute permission, is the crux on which I hope to predicate a solution to a problem I'm working on. Sorry I know I should not end a sentence on a preposition.
Whereby, I gotta make some data accessible to people who don't really know how to write SQL.
Hopefully I'm smart enough to do the job.
Thanks for the video sir.
You are welcome.
This is by far the best video i have ever seen on this topic, Thank you Tim
You are most welcome. Thanks for watching.
I have a clear pic of what a stored procedure is by watching this amazing content. I clicked on subscribe option before even close to watch this video. Please keep uploading...
I am glad it was so helpful.
Hi Tim, I've watched a dozen of your videos and they are very useful. I'm also learning from you how to explain complex technical ideas in plain English to non-technical audience and even to junior programmers. Thank you! From Guangdong China.
Awesome!
It is the only yt channel where I always give a like button before I start watching. Many thanks for all tutorials !
Glad you like them!
Amazing video! Not only do you explain what stored procedures are, but you also give detailed background information and use cases. Keep up the great work!
Thanks! Will do.
Tim, thanks for another great video!! Although I am now retired and never employed formally as an "engineer", much of my career was around managing a business that was highly secure and mission critical service network. Stored Procs are the way of life in such environments and, like you, NOBODY felt comfortable enough to executed the Entity Framework in such an environment. The business had to be able to fix ANYTHING that happened in the shortest time possible and performance was an issue. With about 1.5M connected end points sending in asynchronous signals, you can imagine how much data was coming into the network and why performance and security were so important. Kudos to you - you are correct about security!!
Thanks for sharing based on your experiences. This type validation helps others understand the importance of the lessons and concepts Tim shares.
Almost 3 years later, i found this, and... well...subbed and THANK YOU! It helped me a lot!
Ok, i reached the end of the video now, and i just have to THANK YOU AGAIN! Awesome, really, you made it click so many times in my head and filled me with confidence, awesome awesome awesome! Thanks! Please continue doing this, i think i will watch a good chunk of your videos from now. :)
I am glad it was so helpful!
This was one of the most understandable, cohesive, well structured and best introductions I have seen in my whole life. I came with no understanding of anything besides SQL-statements and programming (java etc.) and I feel, I have a decent idea of procedures and role-management now.
Fantastic! Thanks for sharing. Please check out my other training videos - ruclips.net/channel/UC-ptWR16ITQyYOglXyQmpzw
Thanks for the lesson. I enjoy that you teach more of the WHY and not just the HOW.
You are welcome.
Best teacher on RUclips
Thank you!
Great channel, excellent mentor, coach, and trainer without a ludicrous price tag. Thank you, Thank you, and Thank you.
You are most welcome.
Thanks for pointing out that security matters! It's such a big deal today yet I still see so many tutorials where they completely disregard security matters.
You are most welcome. Thanks for watching.
Fantastic video! Just had a recent interview for web dev in the financial sector and I have fairly little experience with T-SQL. One of the things they asked for me to do is additional research on Stored Procedures and this is the most clear and comprehensive guide I've seen thus far. I really appreciate it and I hope you're happy to hear how helpful you are for helping me get my next job!
Awesome! Glad I could help.
This, and the rest of your videos the videos on this channel, is absolutely golden! The SQL course on your site is worth way more than you're charging. You and Derek Banas are The best source for this stuff. Thank you very much, Corey!!
Thanks for the compliment. I appreciate it.
The way you explain things is amazing.
Thank you!
I've been watching your video's since the start of my programming journey 1 and a half years ago. Thank you for yet another great tutorial!
You're very welcome!
Very easy to follow introduction to stored procedures. Just enough depth to get the general idea without getting bogged down in the details. Thank you for the great free content you provide!
You are most welcome.
Waiting for a full course on SQL by you, because it's brilliantly explained!
I do have a full SQL course: www.iamtimcorey.com/courses/accelerate-microsoft-sql/
the most underrated channel on earth! thanks man.
I appreciate the kind words.
@@IAmTimCorey you can't imagine how much you helped in my career, hope your life gets happier and easier like what you doing to ours ♡
at 24:00 you
created a role : dbStoredProcedureOnlyAccess
and granted access to dbStoredProcedureOnlyAccess Role.
But how does SQL Server knows that It is stored procedure only acces role?
When we created that role, we only allowed execute actions. That means you can only call stored procedures.
@@IAmTimCorey ohh okayy... Understood. So we use exec command only for stored procedures right. Thanks for the prompt reply. Your tutorials have been really helpful.
I never watched about procedures before. And I thought it will be necessary to watch several tutorials. But you made me understand watching only one. Thanks
Excellent!
This is the best tutorial series I have seen. Its ridiculous that you have less than 100K subscribers. You should have at least 500K subscribers.
I appreciate the kind words.
Dear Tim, I think You have a precious gift to explain concepts.
also, using Cached execution plans is one of the most important benefits of Stored Procedures
Thanks!
Very clearly and professionally explained. Thank you so much Tim.
You are welcome.
Company I'm working for now uses C# and SQL Server. The reporting team wanted access to use sql to make reports and such because they are pretty tech savvy too. So we just created a "Reporting User" and gave it "db_datareader" only. They can query what they want. No sensitive information... its a database of results returned from robots. There are only 4 of us that can actually modify the database in any way. As far as naming convention I still use "sp_" if I'm creating it in master for a common use across our different databases (more things like backup, turn replication on for a database, etc.) Rule of thumb any changes that can be damaging we run the stored procedure to back up the DB before making changes. Yes, databases are still backed up every night though. If I screw something up like an update without a where clause (i've did this before) I can copy that bak. to my local machine get original values to update the production database back to how it was at the moment I backed it up. Worst case we can restore the backup directly in SQL server but with replication this means pretty much bringing that database down for about 1/2 hour or so and resyncing. Fun fun :)
First day at work. So glad I found you!
I hope this channel is a lot of help for you.
You are doing the lords work sir. Thank you, I needed this for a project at work.
Thanks!
seriously one of the best videos I ever watched with a great and detailed explanation.
Glad it was helpful!
Hi Tim! You explain things and make it so simple, thank you !
You are welcome.
Great video Tim, I’ve never quite understood that “business logic in the UI only” mantra. Business logic should be wherever it’s most appropriate.
Thanks!
I'm a DBA and it's good to hear a DEV perspective advocating SPs. :-)
ORM tools like entity framework seem to be quite fashionable with developers. I get the appeal but you give up a lot by not using stored procs - explained very nicely in this video.
I'm glad you approve.
Thank you tim for all your amzing content, i got a job in IT and your videos were super helpfull both in recruiting and during actual work.
Best of luck for you and thanks for helping me turn my life around !
Cheers from Portugal !
Great to hear! Thanks for sharing.
Thank you Tim! This really helped me with an issue I was having with the Tournament App Tutorial.
You are welcome.
This is the first time I've ever seen a meaningful use of stored procedures.Thank you so much Tim Corey.The sql databases are most of time shrouded in secrecy and most of developers just know the fundamentals of them .I have been struggling for longtemps to level my expertise in sql databases up to my mastery in Mongodb but just in vain: I am just left with fundementals.It is really crazy.
I am glad you found it valuable.
Thanks a lot Tim about this great video. I personally build all my ASP.NET projects on simple and complex stored procedures and I will always keep using them, all the reasons you have mentioned are correct 100%, I feel very confident and relaxed when I use stored procedures, and once the stored procedure does its job, I know that it will never give me any surprised error in the futre, they are very controllables, what you write is what you get and no code is behind the scene also they are independant from each others. Great job Tim. Please keep it up as usual.
Thanks for sharing
@@IAmTimCorey Thanks for your efforts dear.
I had many deliberations on sp. Many dissuaded me from using it. I was convinced that sp provides security. But your video provides a very structured reasoning
Great!
I was just preparing to move my classical ASP app ( including SP’s) to MVC with EF. Of course, I’m pleased with speed of execution of SP’s, especially in reports, but DB dependency is another consideration for me. I watched so many videos, even bought some trainings. Then I watched this video. It’s a perfect eye opener for me. I’ll watch your tutorial at first, then decide which way to go. Thank you this valuable information. Best regards.
I'm glad to be of help.
I didn''t know about the sp_ naming prefix. I gotta stop naming stored procedures that way. Thanks for the help sir. Very helpful for a beginner in stored procedures
Thanks for trusting Tim as you build your skills
I am a beginner of SQL sp coding. Your video is super detailed and concise. Thank you so much for your help. This is exactly what I need now. Instead of linked server, I would try a new SQL authentication account and use as connection string credentials in our custom script. I will update here on the results.
Glad it helped!
Hello Tim I have been watching your videos for a couple of weeks now. Thank you for creating great content and make it easy to understand.
That has always been Tim's goal. Please check out his full channel - ruclips.net/channel/UC-ptWR16ITQyYOglXyQmpzw
You are welcome.
42:28 As far as I know you can execute custom sql since ef 5 or so. I believe the call was context.Database.SQLQuery where you insert your sql query as a string for example
Can you check constrains with stored procedure , Before it inserts any data?
WOOW i don't know what to say u cleared my mind i definitely got the idea of SP thank you so much Tim i recommend ur channel to friends i liked it
Happy to help.
Some of the best training videos. A great starting place.
Thanks!
for the first time to see Spro well elaborated Kudos Corey
Thank you!
For SQL Server newbies like me that always used Windows authentication locally, when you create a new SQL Server User you also need to allow SQL Server Authentication in SSMS -> Select Server (right click) -> Server Properties -> Security -> Server authentication. For some reason mine was set to Windows mode only by default.
Thanks :D
Thanks for sharing.
Hi Corey, excellent video. Question though...how come the user gets denied access to EXEC if my stored procedure has a select statement with joins from different schemas. "The SELECT permission was denied on the object ___, database ____, schema ____. What is the solution for this?
Omg, after watching your video, SQL Stored Procedure is on my mind. Thank you.
You are welcome!
This is excellent, thank you, very clear and concise, one of the best I've seen 🙂
You are welcome.
Always a delight and informative videos Tim :)
Great!
when you made that dbStoredProcedureOnlyAccess how did the system know that the user will only be able to access stored procedure??
Tim ! you 're really the best one to explain this.
go on
Thanks!
Thank you very much Tim! I learnt so much about stored procedures in 44 minutes than I did in my entire semester. Please upload a video about triggers and functions if possible. I'll be looking forward to it. 💪
Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
Great explanation of SP in such a simple way. Thanks Tim
Glad it was helpful!
35:35 Why was the following approach to putting the procedures under source control not mentioned?
For each procedure needed, write a .sql file that adds the procedure to the database. When the application starts, if files exist that have not been run against the database, make the application run them.
Check the scripts into Git.
The application can call all stored procedures whose add file has been run. Works for me.
I should add: This is not negative criticism, and thanks for the video, I appreciate it 😊
The reason why is because it was outside the scope of this video. We were talking about stored procedures in SSMS, not about how to put them into source control. The reason why is because SSMS does not have source control by default. That's usually a paid add-on. However, I did do a separate video on how build and manage your entire database in Visual Studio, including allowing that project to be managed in source control: ruclips.net/video/ijDcHGxyqE4/видео.htmlsi=9LgbwvAF45sxWTfu
I also then used that technique in the TimCo Retail Manager series here on RUclips.
@@IAmTimCorey Thanks! Appreciate it 😊
You the best Tim , so much love.
Thanks!
Excellent tutorial as always.
Thank you for taking the time putting it together.
Regards
You are welcome.
Clear and efficient explanations. One of the best tutorial I saw till now on this subject. Thank's very much!
You are most welcome. Thanks for watching.
Just want to say, I love your videos and I totally understand where you are coming from. I've worked on systems that used both SP and Linq to EF. Using SP when performance becomes an issue. But Linq is definitely very addictive because it's easy to read and compose complex queries conditionally. But it's also where the performance issue crops up because it's so easy to nest subqueries without thinking about it.
Yep, there are a lot of hidden gotchas.
this class on stored procedure basics was very helpful. thank you!
Glad it was helpful!
Great video, question - when creating the role dbStoredProcedureOnlyAccess, how does the environment know it only applies to the Stored Procedures?
This is a great video. Excellent work as usual Tim Corey.
Thank you!
at 15:57, where you say "Don't change the order of Parameters in case the SP is in use", isn't useful. If an existing call to an SP finds and extra, unexpected parameter, it will still fall over.
Any Parameter change will always require a complete check of all calling processes.
Not if you make the parameter optional.
@@IAmTimCorey Ah, but then we get into the headache of "loose parameters ", where you risk security and reliability due to "unexpected combination" consequences, and worse, have to build in the logic to handle empty parameters.
Usefull sometimes I know, but a real source of pain.
I totally agree with you, we can use visual studio data tools project to use compare changes and have it in source control. Similar to what Red gate does.
Yep.
37:55 you are correct. Business logic belongs in the database.
Been using them for 20 years...they are they best...
Great!
Thank you Sir! All your Videos are informative and helpful
You are welcome.
This great video needs a sequel (pun intended) for advanced use of Stored Procedures.
Thank you. I have added this to Tim's list of possible future topics.
Very informative and useful tutorials Tim...
Thank you for sharing and your support!
Thanks again bro for another informative video. God Bless and Keep Safe.🤘
You are welcome.
Love your videos. I am very new at creating web apps with MVC in C# and I totally agree with you that EF is not the way to go when working with a SQL Server database. I have over 25 years of experience developing SQL server databases and stored procedures are the way to go for all the reasons that you mentioned. Now I need to learn how to used SP with a WebApp.
Thanks for sharing!
I completely agree with your position on the 'no business logic in the database' edict. In fact, I would argue that a relational database IS an implementation of business logic in itself, since each table represents a logical data type. Entities such as 'customer', 'product' and 'customer-order' are an integral part of most business models!
Thanks for sharing your perspective.
Exactly, “business logic” encompasses the whole application, both the database and the UI, then each layer performs the role best suited to it to govern that logic
Hi Tim, im a fresh IT graduate and started learning C# language. I loved watching your videos because you just dont show how to do it but also you explain how it works. By the way in terms of security, do you have a videos where you show how to validate user inputs from client side just just before accessing to a database. Thanks.
Not specifically, but that sounds like a good suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
I will. Thanks.
Tim Corey, i love you man.
Thanks!
Thank you so very Much Tim. You have contributed so much to my career, truly i can't thank you enough, though i am yet having not much enough to subscribe to your paid programs and articles, i am looking forward to someday.
This video is great, i often used Stored Procedures in my code but i found them to be time consuming and extra work to face so i began to do more of the unsecure query, query thing you know... but with this video, i think i will redirect my energy towards understanding it more and leverage on its benefits. Once again, Tim, Thank you a ton.
Very good explanation Mr. Tim, and thank you so much for that.
You are welcome.
Hi Tim, this is a really fantastic video and you do such a great job of clearly explaining everything! I’m currently studying my MCSA 70-461 and this has helped massively! I wonder if you have any videos on Triggers and Transaction Isolation Levels?
I don't, sorry.
What if I'm developing desktop app that connects to server in the same network.
Server is own by my customer so they have 'sa' account. They can just do whatever they want with tables that my app creates.
I want to create database on their server and I want to give them access but not to whole database.
EDIT: After looking up for answers I don't think it is possible. If they have 'sa' account they have access to everything and I can't do anything about that. Even if my app would create new user and new role.
If they have sa access, you can't limit what they can do.
Came for the Stored Procedures, now it seems that I'll need to hang around for the C#. This is one of those times when I wanted a better explanation of one thing and wound up with a (needed) kick in the pants about something else. But I'm taking a deep breath and clearing some time on my calendar. 🤔
I'm glad it was helpful.
you can manually add all your procedure scripts to source control. No extra cost except that the source control commands must be done from outside of management studio.
This should make life easier for you: ruclips.net/video/ijDcHGxyqE4/видео.html
This was very informative Tim. Thank you so much.
You are welcome.
Hey Tim can you do a similar video to this for MySQL. That would be awesome!
I do that in my Foundation in C# course on Data Access: www.iamtimcorey.com/p/foundation-in-c-data-access
Also, I was wondering... Do you avoid using the PK_ for user-made primary keys like you do with the SP_ for stored procedures?
Your videos/tutorials are very useful. Thank you for your work!
You are welcome.
What about when you need to run a dynamically created query from the SP which has been setup to use a execute only role? Is there a workaround for that because you will get a permission denied. Thanks!
If you can execute the stored procedure, the code inside of it can be executed.
I somehow agree with the business logic in the database. But how we would design the validation from front-end to API to database? Of course the API will have all the input validation and business logic but we will do it again in the DB? Or should the other applications that wants to use the database should use the API?
Think of your data storage process as a castle. Layers of defense. If you focus on just one layer, it can be bypassed and your data compromised. So validation logic goes in the UI (for quick "don't do this" feedback), in the API (for "I'm not going to save that" actions) and a bit in your database (possibly. For "I'm going to stop the worst stuff"). Mostly it will be in the API layer, but there can be a place for it in the database. It all depends on your use case.
This was a very good video, a lot of the stuff I knew from your other videos on SQL, but I learned a lot of things about Security!
Glad it was helpful!
Thank you Tim.. I aslo would like to use ADO instead Entity framework like you.
You are welcome.
How about using entity framework for small queries on small tables, and Stored procedures for large data including joins etc. Is it good to execute stored procedures using entity framework in that scenario?
It all depends on how well you know Entity Framework. If you are really good at it then yes, that works. If not, I would recommend creating your SQL outside of your C# code.
I do like the idea that business logic, i.e. data validation is built-in to each dB, as it might change from one dB to another based on business needs..
Having some data validation on the database can be a good option, since you don't always control who calls your stored procedures. Just don't go crazy with it and bog down your server.
A story I heard before is there was a firm using their database to store high resolution photos as BLOBs in their database and one day was saying their database was thrashing constantly and crawling. It was because someone coded a select statement as select * from whatever that contained the BLOBs when just wanting to peer at the meta-data stored with the BLOBs. Because they had a direct select interface to the data, they could do that and wrecked the performance.
Yep, you have to make sure you monitor the performance of your queries. Knowing how to test and evaluate queries is something that every person who is writing queries should know, whether they are creating stored procedures or using Entity Framework.
@36:40 This is out of scope but it relates to what you said about not making changes to a live server. Is there a guide that you know of that can walk us through setting up different DB servers for stages of development (e.g., local db for testing, development db server, production db server -- those you alluded to)?
I've used the "backup/restore" feature to replicate the state of a live db server onto a local machine for testing C# stored procedures. However, I don't know how to keep the servers synchronized (so that changes that add records to tables will add records to my db)
Yes, I will be doing a video on putting your database into source control and deploying it to dev, staging, and production. Look into the Data Tools in Visual Studio if you want to get a jump on it.
Great video! I have a question though. When you are speaking about why you like to use stored procedures because of security considerations - can't you reach the same goal if you use View and why is it neccessery to be stored procedure? You basically implemented simple select statement in this stored procedure so what's the difference between a view and this stored procedure?
A stored procedure is a different element. I find myself not using them that often. You cannot use parameters with them, they cannot be as easily secured, and they aren't precompiled in the same way.
Hi Tim I just want to tell you're great developer really Thank you
I appreciate the kind words.
Hello Tim, would you say that creating or using stored procedures is the solution to prevent SQL injection attacks? What more is needed to prevent the attacks? I’m also trying to add stored procedures to a Db that was created by EF.
It is a major part of the solution, yes. In order to fully prevent SQL injection attacks, you need to have layers of defense. Scrub your inputs on the client-side. Don't allow your users to form SQL queries directly or indirectly. Sanitize your inputs again on the server-side. Use Stored Procedures or at least SQL parameters on your SQL calls so that extra commands cannot be passed to the database server. Limit the permissions of the connection string account that accesses the database. Those are all steps you should consider when preventing against SQL injection attacks.