SQL SERVER 2017 TUTORIAL: PART 05 (STORED PROCEDURE)
Articles,  Blog

SQL SERVER 2017 TUTORIAL: PART 05 (STORED PROCEDURE)


Welcome to beginners tutorial Microsoft sql server part 5. today We are going to learn about the store procedure how to create it and how to execute stored procedure a Stored procedure is a set of structured query language statements with an assigned name Which are stored in a relational database management system as a group, so it can be reused and shared by multiple Programs. there are so many advantages of using a stored procedure if managed control and a validate data Larger queries can be avoided. it reduces network traffic since they need not be recompiled hence stored procedure increases reusability of code Permissions can be granted for stored procedures, hence increases security first open SQL sever management studio. it will take a while Connect it In previous tutorial we have created a database called test db We are going to create a stored procedure in this database. we have already Created a table is called customer In this table There are some customers names and their address. our goal is to find out customer who lives in the USA. To create stored procedure you have to go to the programmability options and You can find the stored procedure these are system stored procedure which is used by the database itself We will create new stored procedure This is the template already provided by Microsoft we can directly edit this template or use this form Most important thing is Procedure name. Give a suitable positive name for this example sp_filter_customer type a parameter name And parameter type it should be varchar. cause table data Type is varchar Let’s do some manual editing. give a creation date Description of this stored procedure, so that we can easily Identifies the purpose of this stored procedure we are going to use only one parameter so clear unnecessary text Parameter always start with a ‘@’ sign You have to type SQL statement here. According to this sql statement stored procedure will output results set. It is a beginner’s tutorial so we are going to use query designer instead of typing sql statement type filter parameter here Finally press f5 or click execute to finish creation of new stored procedure Refresh the the Object Explorer here is the newly created stored procedure. now how to execute a stored procedure It is very simple right click on the stored procedure select execute Type the parameter value in our case it can be USA But it is showing nothing! Maybe we made some mistakes. Making mistakes while creating a stored procedure is a common thing Let’s check out where we made mistakes and correct those and re-execute Now we are editing existing stored procedure, so we have to use alter procedure instead of create procedure Now everything seams okay, it’s successfully filtered all customer from USA

One Comment

  • Dave Kellett

    the background music is louder than your voice. Please adjust the audio levels as I can barely hear what you're saying.

Leave a Reply

Your email address will not be published. Required fields are marked *