How to Write Stored Procedure in SQL Server for Beginners

Hello, I'm Kindson the TechPro and today I'm going to teach you how to write stored procedures using SQL server in less than five minutes I personally cannot believe it when I learnt how to write stored procedures and SQL server. Sometimes even if you are a programmer or a new DBA you may think that writing Stored Procedures is very difficult. Right now I'm going to show you that is really one of the easiest things you can do in a database so go ahead to open Microsoft SQL server management studio i'm using 2008 r2 remember they the data we have an Excel you can import it into SQL server that I've don't have imported the data into SQL Server if you like to have this data look at the link in the description below and then you'll be able to download this day I use it to follow along alright so I imported it to a table in the database kttpro and the table is called tblMembers as you can see if you go ahead to right-click and say select top 1000 you see the data is right there. ok. so how do you write Stored Procedures in SQL Server. first know that Stored Procedures are simply queries that are saved so if you have if you know how to write a query this time you're writing a query and you are saving it right there inside your database so that you can reuse it so what you do you just click on your database expand it and then go ahead to expand programmability and then the first one there a stored procedure if you expand it you find out that you don't have anything except system stored procedures so what do you do to create your first stored procedure just right click and then say new stored procedure all these are very confusing they are a whole lot of crap so just go ahead to select everything and delete and that is it so let's write our first stored procedure take note of the syntax you simply say create procedure and give it a name let's call it our first okay let's just call it getallmembers and then you say AS after you enter AS then you write your query that is as simple as that so I just say select * from tblMembers so if you execute good, so you have created these stored procedure so eat doesn't execute as a query but if you go ahead to highlight just a query there and execute it actually executes and gives you what you want but now we've executed that this CREATE Stored Procedure statement so let's go ahead to check so just do a refresh refresh and then expand you see GetAllMembers Stored Procedure how easy is that so that is so fine The Next thing and we are going to do is to write a stored procedure that actually takes a parameter let's assume you want to update my name or my surname to something else let's look at our data again so that will be able to make updates this time we're writing a stored procedure that will accept a parameter so let's see you see Kttpro is a full name and now i'm going to write a Stored Procedure that takes a first name and then changes the Kttpro to something else so it follows the same what I normally do is to open existing stored procedure by right-clicking and say modified so it adds all this junk you might either leave it or you can just delete it for me I like deleting them to avoid confusion the Alter statement change you to create because we want to create a new one we say UpdatesMember but this time are the name of the Stored Procedure is UpdateMember before the AS before the ass AS keyword just say just specify the Parameter you want to use or just like that the function the arguments of the stored procedure so let's say our first name take note that always use @ to start the variable name at first name VARCHAR(50) okay VARCHAR(50) and then we want to make updates so of course you know how to write Update statement if not just look at the previous video from the link in the description below so that you see how the Write Query so you just say updates tblMembers SET firstname = @firstname take note that @firstname is different from firstname @firstname is a parameter that has to be provided by the user @firstname where we have Kttpro where firstname equals Kttpro. If I'm too fast please pause the video or just rewind so at this point we have a stored procedure that says a Updatemember and i'm going to execute it and command completed successfully so if you're refresh and Stored Procedures will see the to Stored Procedure now if you right-click on any of them you actually can execute the stored procedure so let's execute the first one execute it brings out this dialog box just say okay and then execute as you can see for the second one lets execute it and we see that it will ask us to enter a parameter so let's execute yes so this is it parameter firstname so value i'm going to specify let's say Kindson that's my name and then I say okay how it execute so how do I know? if I go to look at all members again all members let's take a look where my name was Kttpro or i see so you can see the record 12 changed so this is exactly how to write stored procedure let me just show you one thing you can do but it's not necessary delete all these crops avoid confusion you can use begin and end to surround your query ok so if I execute it's fine is also ok so that is how to write stored procedures if this has been informative for you subscribe. just click on the subscribe button down they and like the video share with your friends i remain Kindson The Tech Pro and I want to thank you for joining me

Channel: Kindson The Tech Pro

Views: 369,633

Keywords: Stored Procedure, SQL Server, Stored Procedures, Database

Id: 2OuhVHJGuS0

Length: 7min 36sec (456 seconds)

Published: Thu Dec 22 2016