SQL Stored Procedures | CREATE PROCEDURE Statement| SQL Server Tutorial for Beginners

hello guys welcome to amit things in this video we will see how we can work with stored procedure in sql server and what are these so a stored procedure in sql is a prepared sql code that you can easily create and save so that you can reuse your code again and again that means it's like a function okay let's say you have a query which you need to write again and again what you'll do you won't write it again and again you will basically create a procedure so that you can just call it and execute it whenever you need it so this is what we call a stored procedure okay for stored procedure we use the create procedure command okay so let us see an example here and we have our amit db database you can see i'm a db within that we have our employee table let us see the records enter the table using use omit db use command space database name that is db is our database name execute successfully now let us show the records of the employee table select star from employee semicolon and just show it execute okay guys now at first we will create a procedure for this command only because we need to use again and again so so we will create a procedure use the command create procedure let us give it a name all records use the alias and type the same command and type the same command select star from employee that's it press enter right go semicolon so now just select it and click on execute you can see query executed successfully to actually execute it and show the records use the command excc that is execute and then the name of and then the name of the procedure all records semicolon and just execute this here and you can see all the records are visible by just only typing exec keyword and the name of the procedure or the name of the function that's it okay now let's say we will create a stored procedure with a single parameter for that let us first display all the records here it is similar now what we will do we will create a stored procedure again similar way i'll just copy it with the parameter so right now we have no parameters here you can see we have no parameters here we will select records on the basis of a city name so for that use add the rate city okay so we want it for a specific city so i'll mention the condition where city is equal to at the rate of city okay that is the following here and you can see it is showing an error i need to add the data type also let's say it's varchar20 it's fine now okay now what i'll do i'll just use the execute command way all records but it is having a parameter now so the parameter is at the rate of city is equal to let's say we want for city name abc now let us change it to all records too so that we have a new stored procedure so here and you can see guys we created all records to procedure and we added the type also to remove the error for the city and here in we will now execute it executed successfully let us see now and all the records for city abc will be visible under our stored procedure with a single parameter okay guys now we can also add multiple parameters let's say i'll create this again okay this is a third one at first i'll just display the records here it is okay so now i'll add multiple parameters to our storage procedure and let us name it to three for our example okay so now we are fetching records that will select employees from a specific city okay specific city with a specific name okay so this is the third one city we already added so we will add multiple parent let's say emp name is what we are adding okay where care 255 so we added two parameters here in city city and we are using the and emp name is equal to at the rate of emp name so this is so this is what we added okay here in what i'll do i'll add in a similar way at the rate of similar way at the rate of emp name okay is equal to i told you for a specific emp name so city abc is having two employees will and you can see will and tom both are having city abc so let's say i'll fetch the tom one is tom now we have a specific stored procedure for it with two parameters okay so now i'll just select it execute command completed now i will just execute this let me add a semicolon here both select it execute you can see we selected that specific records so we have created three stored procedure for example here it is for select start from employee n for single parameter and the third one for multiple parameters so guys in this video we saw what is the stored procedure how we can create it we also saw how we can create a simple stored procedure with a single parameter and frame with and with multiple parameters also so guys thank you for watching the video if you liked it do subscribe to our channel with things

Channel: Amit Thinks

Views: 68,980

Keywords: SQL, SQL Tutorial, SQL SERVER Tutorial, 2022, SQL Tutorial for Beginners, Amit Thinks Studyopedia

Id: MSbzErdcb6g

Length: 6min 15sec (375 seconds)

Published: Wed Feb 23 2022