Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
This course investigates several key database-programming concepts: triggers, stored procedures, functions, and .NET CLR (Common Language Runtime) assemblies. Author Martin Guidry shows how to combine these techniques and create a high-quality database using Microsoft SQL Server 2012. The course also covers real-world uses of the INSERT, UPDATE, and DELETE procedures, and how to build a basic web form to connect to your database.
Now that we've created a stored procedure, it's likely I would want to change it at some point. The easiest way to modify a stored procedure is to come back to our favorite menu, the Programmability menu, open that up, locate the stored procedure we would like to change, right-click on it, and one of the options in the right-click menu will be Modify. When I click on Modify, it throws some code up on the screen. Notice that lines 8 through 12 here are extremely similar to what I wrote. The only difference is rather than have the keyword CREATE, it has changed it to the keyword ALTER, which makes a lot of sense.
We are not trying to create a new stored procedure anymore, we are trying to modify a stored procedure, and in the SQL world, we use the word alter for when we want to change things. So I could make a small change to my stored procedure, and click on the red exclamation point again. And it will say that it completed my command successfully, which means the stored procedure has been modified. In order to see this, I will need to execute it again. So we will again do EXEC and the name of the stored procedure, and we see it is now giving the new text; the text from the modified stored procedure which reassures me we have successfully modified the stored procedure.
I could go back to the other window and change it again, Execute and come back to my window where I'm executing the stored procedure and we see change again. When you're working with stored procedures, it's likely that you will be modifying them often, and it would be preferable to not have to bounce back and forth between one window and the other. I don't want to have to go to one window to change my stored procedure, and a different window to execute the stored procedure. We can do that all from one window.
I'll take this code here and a little copy and paste to my other window. And the only thing I need to do is in between them add the keyword GO so that the machine knows these are two separate chunks of code. Everything above the GO is used to modify the stored procedure and then everything after GO is used to execute the stored procedure. When I click Execute, it will run both and so I can modify the stored procedure, and we see the modifications of that immediately.
So during development, this is typically the way that I like to work. It's the quickest and easiest way to modify stored procedure, and immediately see the results of those modifications.
There are currently no FAQs about SQL Server: Triggers, Stored Procedures, and Functions.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.