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.
I'd like to talk to you little bit about creating stored procedures in Microsoft SQL Server. To do this we're going to start of with the keyword CREATE and then the keyword PROCEDURE. After that we need to give it a name. Well, we have lots of options on naming. Almost any combination of letters and numbers and punctuation marks will work. One of the few exceptions is you are not allowed to start with a numeral. So if I try to type in numerals at the beginning, the machine will get upset with that and put that little red squiggly line in there.
That is because you're not allowed to start with numerals. You are allowed to end with numerals or have numerals anywhere else in the name. But just as a matter of preference I typically avoid both punctuation marks and numerals and go with just text. Once we've decided on a name we will need the keyword AS and the keyword BEGIN. I typically put those on separate lines, but that's not necessary. You could put all of this on one line. I just find it makes it a little easier to read spaced out like this. After BEGIN, we have to actually do the work of the stored procedure and just about any T-SQL statement is valid.
We're going to start off easy and just do a very simple one line that will return some text. Then in single quotes the word hello. At the end of the stored procedure we need to type in the keyword END and that signifies to the machine this is the end of our stored procedure. We will then click on the red exclamation point for Execute and we get at the bottom a green check mark that says Query executed successfully, and the phrase command completed successfully.
So it would certainly seem as we were successful. We've got two forms of positive feedback. The stored procedure is now stored in our database. We can see that by expanding the myDatabase menu to the left. Then we expand Programmability. Inside of there we will have Stored Procedures. It's not coming up yet. I bet if I click Refresh, there it is, a stored procedure called myTest. So there we can see the stored procedure exists. The next logical thing to do would be to execute the stored procedure, and in order to execute you can just type in the keyword EXECUTE and then the name of the stored procedure, click the red exclamation point again and we see our data returned to us.
We ask the stored procedure to return the word hello. It did in fact return the word hello. So it seems as if things are going well for us. The keyword execute can be abbreviated. You can type in just EXEC and it will accomplish the exact same thing. So that's an alternative way to execute our stored procedure. So these are the basics of creating a stored procedure. In the next section we will work on modifying a stored procedure.
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.