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

Newsletter signup (with exclusive discounts): signup.iamtimcorey.com/ (your email will be kept safe and you will not be spammed).
Facebook Page: / iamtimcorey
Want to see a full C# application built from scratch?
Check out this course: iamtimcorey.com/courses/cshar...
Microsoft SQL can be confusing. There are a TON of features, options, and settings to consider when attempting to use it. One of my favorite features is stored procedures. These pre-compiled queries allow you to safely and securely access and modify data. In this video, you will learn the basics of how to build a stored procedure, as well as what the benefits and drawbacks of stored procedures are. You will also learn some best practices, including how to secure your entire database in just a few simple commands.
Thanks to Ralfs HBK for timestamping the video:
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

[@SmokeySpace]

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!

[@IAmTimCorey]

I am glad my training is helpful.

[@shepniel]

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!

[@IAmTimCorey]

I will definitely be continuing. I'm glad you are enjoying the videos.

[@cbuosi]

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.

[@IAmTimCorey]

I appreciate the kind words.

[@jakubsvoboda6569]

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

[@bluemoon1357]

This is by far the best video i have ever seen on this topic, Thank you Tim

[@IAmTimCorey]

You are most welcome. Thanks for watching.

[@thenujaliyanage2207]

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!

[@IAmTimCorey]

Thanks! Will do.

[@asyncawake9011]

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!

[@IAmTimCorey]

You are most welcome.

[@leozhang4574]

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.

[@IAmTimCorey]

Awesome!

[@DevineAbyss]

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.

[@IAmTimCorey]

Fantastic! Thanks for sharing. Please check out my other training videos - kzbin.info/door/-ptWR16ITQyYOglXyQmpzw

[@gordonhope9219]

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!!

[@tomthelestaff-iamtimcorey7597]

Thanks for sharing based on your experiences. This type validation helps others understand the importance of the lessons and concepts Tim shares.

[@johnnyvcrow]

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.

[@IAmTimCorey]

You are most welcome. Thanks for watching.

[@joshuawillis7874]

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!

[@IAmTimCorey]

Awesome! Glad I could help.

[@gr8one73]

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!!

[@IAmTimCorey]

Thanks for the compliment. I appreciate it.

[@juanminglao3699]

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!

[@IAmTimCorey]

You're very welcome!

[@NinjaGhostWarrior123]

Srs. You are one of the best explainers and teachers out there. So precise and slow enough to follow. And the concepts are presented so nicely. Great job man! :)

[@tomthelestaff-iamtimcorey7597]

Thanks for your trust in Tim

[@shader3020]

Thanks for the lesson. I enjoy that you teach more of the WHY and not just the HOW.

[@IAmTimCorey]

You are welcome.

[@donaldputout519]

Clear and efficient explanations. One of the best tutorial I saw till now on this subject. Thank's very much!

[@IAmTimCorey]

You are most welcome. Thanks for watching.

[@niyaz8]

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

[@IAmTimCorey]

Excellent!

[@ghulamali7676]

The way you explain things is amazing.

[@IAmTimCorey]

Thank you!

[@charliegrayii2124]

Great channel, excellent mentor, coach, and trainer without a ludicrous price tag. Thank you, Thank you, and Thank you.

[@IAmTimCorey]

You are most welcome.

[@giaptiennbros]

Hi Tim! You explain things and make it so simple, thank you !

[@IAmTimCorey]

You are welcome.

[@andyhudsonsynthpop]

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.

[@IAmTimCorey]

Awesome! I'm glad it was helpful.

[@denitsageorgieva7209]

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 😆

[@cmsinghrawat1367]

Sir need your help to understand procedure n fuctions

[@opietwoep1247]

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

[@philipdeihle6991]

44 😮

[@jasper5016]

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.

[@IAmTimCorey]

I appreciate the kind words.

[@modibosanogo3491]

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.

[@IAmTimCorey]

I am glad you found it valuable.

[@umitgulsen9869]

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.

[@IAmTimCorey]

I'm glad to be of help.

[@sebastianweise4790]

Almost 3 years later, i found this, and... well...subbed and THANK YOU! It helped me a lot!

[@sebastianweise4790]

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. :)

[@IAmTimCorey]

I am glad it was so helpful!

[@mehrtashsouri6862]

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

[@IAmTimCorey]

Thanks!

[@sick911]

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.

[@tomthelestaff-iamtimcorey7597]

That has always been Tim's goal. Please check out his full channel - kzbin.info/door/-ptWR16ITQyYOglXyQmpzw

[@IAmTimCorey]

You are welcome.

[@elladevan7082]

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...

[@IAmTimCorey]

I am glad it was so helpful.

[@tdlabs]

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 !

[@IAmTimCorey]

Great to hear! Thanks for sharing.

[@psykodiak5333]

First day at work. So glad I found you!

[@IAmTimCorey]

I hope this channel is a lot of help for you.

[@francisf.amunde2619]

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.

[@bartoszbuawa6521]

It is the only yt channel where I always give a like button before I start watching. Many thanks for all tutorials !

[@IAmTimCorey]

Glad you like them!

[@adan-codes]

You are doing the lords work sir. Thank you, I needed this for a project at work.

[@IAmTimCorey]

Thanks!

[@RalfsBalodis]

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

[@IAmTimCorey]

Thank you kind sir!

[@raghum4938]

Very clearly and professionally explained. Thank you so much Tim.

[@IAmTimCorey]

You are welcome.

[@wisemanyt82]

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.

[@IAmTimCorey]

I'm glad you approve.

[@lindatubbing2201]

Omg, after watching your video, SQL Stored Procedure is on my mind. Thank you.

[@IAmTimCorey]

You are welcome!

[@islamghaith9172]

seriously one of the best videos I ever watched with a great and detailed explanation.

[@IAmTimCorey]

Glad it was helpful!

[@picklerick715]

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. 💪

[@IAmTimCorey]

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/

[@smakadhar]

Great explanation of SP in such a simple way. Thanks Tim

[@IAmTimCorey]

Glad it was helpful!

[@supa.scoopa]

Waiting for a full course on SQL by you, because it's brilliantly explained!

[@IAmTimCorey]

I do have a full SQL course: www.iamtimcorey.com/courses/accelerate-microsoft-sql/

[@neoanderson7962]

This is a great video. Excellent work as usual Tim Corey.

[@IAmTimCorey]

Thank you!

[@jcgaming9749]

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?

[@IAmTimCorey]

I don't, sorry.

[@Laggie74]

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.

[@IAmTimCorey]

Yep, there are a lot of hidden gotchas.

[@bigdummyhead2162]

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!

[@IAmTimCorey]

Glad it was helpful!

[@turn1210]

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.

[@IAmTimCorey]

Thanks!

[@JimInIssaquah]

Very informative and useful. I appreciate your candor about EF at the end.

[@IAmTimCorey]

Thanks!

[@esmanmudimu1627]

This was very informative Tim. Thank you so much.

[@IAmTimCorey]

You are welcome.

[@dergimorka6126]

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

[@IAmTimCorey]

Happy to help.

[@debbie1724cham]

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.

[@IAmTimCorey]

Glad it helped!

[@deepakbhagat811]

By far the best sp tutorial i have seen thank you & 1 think i want to mention is we can use visual studio database project template if we want to take an advantage of source control for our database streamline

[@IAmTimCorey]

I have a video on that very topic. Thanks for pointing it out.

[@hixamjocular6591]

Hi Tim I just want to tell you're great developer really Thank you

[@IAmTimCorey]

I appreciate the kind words.

[@rtothec1234]

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.

[@IAmTimCorey]

You are welcome.

[@nicholascasey9784]

this class on stored procedure basics was very helpful. thank you!

[@IAmTimCorey]

Glad it was helpful!

[@joellevi275]

Excellent tutorial as always. Thank you for taking the time putting it together. Regards

[@IAmTimCorey]

You are welcome.

[@andreisipos2535]

Your videos/tutorials are very useful. Thank you for your work!

[@IAmTimCorey]

You are welcome.

[@imadabab]

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.

[@IAmTimCorey]

Thanks for sharing

[@imadabab]

@@IAmTimCorey Thanks for your efforts dear.

[@liberumoratio1704]

Some of the best training videos. A great starting place.

[@IAmTimCorey]

Thanks!

[@willdupleich]

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.

[@IAmTimCorey]

Thanks for sharing!

[@befitmyfriend]

One easy way to add SQL objects (definitions for tables, stored procedures, etc.) to a source code control system is to create a script that exports your SQL object definitions to a directory that is under version control. Every time you make changes to your SQL objects you run the script before you make a commit.

[@IAmTimCorey]

The problem with that is that you either cannot include table update scripts in the directory or you can't run the scripts at every commit. I've not found a good way to do it without having a messy implementation. Usually a process like this lasts for only a few changes before it breaks down.

[@amithmihiranga]

Thank you very much! Your explanations are great and amazing! Solid content Tim 🔥

[@IAmTimCorey]

You are welcome.

[@iangabrielalcantara7756]

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

[@tomthelestaff-iamtimcorey7597]

Thanks for trusting Tim as you build your skills

[@polinemuthaiga2752]

for the first time to see Spro well elaborated Kudos Corey

[@IAmTimCorey]

Thank you!

[@ticketisland]

This is excellent, thank you, very clear and concise, one of the best I've seen 🙂

[@IAmTimCorey]

You are welcome.

[@KuyaAyotChannel]

Thanks again bro for another informative video. God Bless and Keep Safe.🤘

[@IAmTimCorey]

You are welcome.

[@michaelshepherdmunemo4414]

Thank you Sir! All your Videos are informative and helpful

[@IAmTimCorey]

You are welcome.

[@shawnmofid7131]

Thank you so much for the content. I found myself having to implement the same tables and SPs to another DB version 2016; mine was 2017. I tried import/export, and the backup feature unsuccessfully. I did not know I could back up my 2017 DB to a 2016 version. Given the scope of my task, I just recreated the DB and SPs on the new version. I did want to mention that I guess replication would not work, because I did not have direct connection from the source to the destination. Not sure if I am right or not. Regardless, I am progressing at a great pace thanks to your videos. After watching the episode on how to ask for help, I was curious about what CodePen was and watched another video on it. I am so impressed and excited about it. I like to use it and learn more about it.

[@IAmTimCorey]

Glad you are enjoying it.

[@samrawitdegu2882]

Thank you so much for your brief explanation.

[@IAmTimCorey]

You are welcome.

[@solomon645]

Very good explanation Mr. Tim, and thank you so much for that.

[@IAmTimCorey]

You are welcome.

[@josephizz4877]

the most underrated channel on earth! thanks man.

[@IAmTimCorey]

I appreciate the kind words.

[@josephizz4877]

@@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 ♡

[@bridgetu9892]

Thank you so much for this tutorial. Concepts are so much clearer.

[@IAmTimCorey]

You're very welcome!

[@DatabaseDesigns4U]

Excellent video! Thank you very much! You helped me a lot! I will be watching more of your videos! Thank you again! :)

[@IAmTimCorey]

You are welcome!

[@Hamza-Shreef]

Tim ! you 're really the best one to explain this. go on

[@IAmTimCorey]

Thanks!

[@andrewshwets1316]

Hi Tim, Thank you for great job, this video was very helpful.

[@IAmTimCorey]

You are welcome!

[@MrPlamor]

You the best Tim , so much love.

[@IAmTimCorey]

Thanks!

[@andreaslang2352]

Hi Tim, thanks for sharing your knowledge with your community. Even as an experienced developer you can always find something new while watching your videos, which is really great. About when to use SP: in my opinion it's a question of the environment and how big the database and project is. For rich clients - yes, SP can be a very good option. For client/server - not really, at least not for everything, because the security aspect is obsolete in this case. Also, the bigger your DB and project is, the faster you'll get lost. Imagine you have a DB with hundreds of tables and many more different queries. You don't want to use SP for all of them. But there can be a point where SP makes sense, when you have high complex queries and also you can always mix SP with plain TSQL in your project. So it varies a lot on what you have and what you want to achive. It's not like saying do or don't do. It matters on your project, environment and the effort you're being able to put in.

[@IAmTimCorey]

The client/server scenario doesn't eliminate the need for security of the connection credentials, it just makes it less vulnerable. Security in depth is important. Yes, if someone gets access to your server, you have a problem but you can limit how much of a problem that will be. Also, getting file access to the server isn't the same thing as logging onto the server but both give you access to the connection string information. As for getting lost in a big project, I don't see the solution there as putting queries in your code. That just means that you have more places to look to find the information you need and it makes it even harder to find. If good naming isn't enough to help you out and good documentation is also failing you, you can develop your server using SQL Server Developer Tools so that you can segment your stored procedures into folders and sub-folders so that you can better organize your objects. This would also work for tables and views. At the end of the day, if your database has enough objects in it to be confusing, spreading out that confusion will not make things better. I do believe that what you do depends on the project, though. I just think there is a heavier weight on taking the secure, optimized route more often and only deviating when you see a significant reason to do so.

[@vinuhosanagar1]

Thank you sir. It is an awesome topic and taught by awesome teacher like you

[@IAmTimCorey]

You are welcome.

[@manaskumar991]

Wow!! Thanks a lot, this lecture was really very informative.

[@IAmTimCorey]

Glad it was helpful!

[@choubc1]

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.

[@IAmTimCorey]

Yep.

[@MrFKD]

thanks a bunch man you've really helped me out

[@IAmTimCorey]

You are welcome.

[@tezitube1877]

Very much helpful videos, thanks.

[@tomthelestaff-iamtimcorey7597]

Thanks for watching and supporting Tim.

[@masterchief7746]

hey its amazing to learn from you Tim you are really one of the best teachers out here, i would like to ask you if you can upload a video on how to use stored procedures on the server side with .net 5/6 i used to work with EF before but i think SP really gives you more control and security like you said thanks!!

[@tomthelestaff-iamtimcorey7597]

Please consider posting your recommendation on Tim's suggestion app (suggestions.iamtimcorey.com/). You may find other ideas there that you can vote up.

[@florent9555]

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

[@synthmania7275]

Always a delight and informative videos Tim :)

[@IAmTimCorey]

Great!

[@RajeshS-wt7fi]

Great Information. More than expected. Thank you

[@IAmTimCorey]

You are welcome.

[@devfromthefuture506]

Best teacher on KZbin

[@IAmTimCorey]

Thank you!

[@syrss7510]

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

[@IAmTimCorey]

Great!

[@ivandrofly]

Very informative as always. Thank you sir!

[@IAmTimCorey]

You are welcome.

[@sy5294]

Thanks you. Simple, effective and efficient.

[@IAmTimCorey]

You are welcome.

[@rnrao2966]

Very informative and useful tutorials Tim...

[@tomthelestaff-iamtimcorey7597]

Thank you for sharing and your support!

[@oysteinsoreide4323]

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.

[@IAmTimCorey]

This should make life easier for you: kzbin.info/www/bejne/n5unlHt9rd6ke5Y

[@LuisPerez-ce7fn]

thank you so much!! finally a good SQL video

[@IAmTimCorey]

You're welcome!

[@KirillBezzubkine]

that s crazy awesome. Thank you tons of times. God bless you

[@IAmTimCorey]

You are so welcome

[@arielspalter7425]

Super helpful, thanks. Subscribed!

[@IAmTimCorey]

Great!

[@giftphiri4017]

Thanks for your teaching

[@IAmTimCorey]

It's my pleasure

[@colinrhee98]

Super beneficial video!

[@IAmTimCorey]

Excellent!

[@mikeaus3099]

Great video. As far as source control you can always created a repository of your stored procedures in .sql or even text files.

[@IAmTimCorey]

Yeah, I've tried that but it always becomes a mess. Normally a rollback of a commit means you get the rolled back code but you can't really do that with SQL. Sure, you could put a drop statement in the create for a stored procedure, which would work (just run all scripts then) but that doesn't work for tables unless they are empty.

[@lbmouse]

I was talking about just the SP code. I have pushed my department to store batch and script files into our local GIT repo. I'm even implementing Word and other procedure docs (even mainframe card members).

[@patricechaula3430]

Thank you for the tutorial

[@IAmTimCorey]

You are welcome.

[@joseluizdurigon8893]

Tim Corey, i love you man.

[@IAmTimCorey]

Thanks!

[@SyberPrepper]

Excellent video. Very clear. Do you have a set of code (a template of sorts) that you copy and paste into your stored procedures which checks variables, etc. for SQL injection and other security related things? Thanks.

[@IAmTimCorey]

I don't do variable checks in my stored procedures typically. There is an expense associated with checks like that and typically I don't need to bother with it. If you use the variables directly in your SQL inside of a stored procedure, you are safe from SQL injection. For example, this code is safe: select * from Person where FirstName = @firstName; Where it would not be safe is if I did something like this: set @sqlScript = 'select * from Person where FirstName = ''' + @firstName + ''';'; exec(@sqlScript); In the first example, I'm using the variable directly. That is safe. In the second example, I'm building a query string using the variable for part of it and then I'm executing the query string. That is NOT safe. As long as you don't create SQL strings that you then execute (a bad practice anyway), you should be safe with stored procedures.

[@SyberPrepper]

Very good. Thanks.