Create Store procedures

Store procedure has some similarities with views in the sense that it allows encapsulation. i.e. to retrieve for further, later use a series of commands, like the select command.

Learn Python  here.

With store procedure, it is possible to branch. It means you can get different statements to run based on specific requirements.

The begin and the end is compulsory in store procedure as it shows where the query starts and where it ends.

Note, you can use a select statement and create a procedure out of it.  Likewise, you can do with views.

The Store procedure is used as a data access layer, which means that it could access the data without going to the table directly.

Let's use the table schema below, to learn more about store procedures.

The TABLE_NAME is tblEmployee and has 7 columns under the COLUMN_NAME, each column will have data.

Step 1. 

Create Procedure and Execute

We will be using the table tblEmployee to create a procedure out of it.

In order to create and execute a procedure, the following commands are used.

The create procedure command, created a procedure with three columns, EmployeeNumber, EmployeeFirstName and EmployeeLastName out of the seven columns in the table tblEmployee.

And the execute or exec commands execute the created procedure and return the data as we can see below.

Note aside from the two execute command shown before, it is possible to return data from store procedure also by just using the name of the store procedure simply like:

EmployeesName 

without the execute or exec preceding it. But this can only be done after the end of a batch and go ends a batch, so it has to be after the go statement.

Step 2.

Create Procedure and Return data based on Input

The procedure we created at step 1 returns all the data for the employee three columns that we specified. Supposed we don't want this to happen, and we only want the return data based on a given input then how can this be achieved?

Let see that in action.

first I will drop the procedure that I have created earlier

There are things to put into consideration while dropping the procedure, 

1.  You can't drop procedure and drop it again - (with same name)
2. You can't create procedure and create it again - (with same name)

So we need to test it to make sure if exist or not and based on that we can perform the necessary actions.

Check if procedure exists before dropping the procedure.

The code above will return employee based on the given EmployeeNumber, that was passed in as an augment of integer type.