That link points back to this page so not sure which tutorial you are currently following.
In terms of what is a stored procedure, it is simply a prepared SQL statement that can be executed multiple times. For example, imagine that you have a table storing contact information (ID, Name, Age, Address etc.). If you want to get a record by ID you could write a straightforward SELECT statement such as: SELECT Name, Age, Address From ContactsTable WHERE ID = @IDParameter. Or you could create a stored procedure such as:
CREATE PROCEDURE GetContactByID
SELECT Name, Age, Address From ContactsTable WHERE ID=@ID
and then simply execute the stored procedure passing the parameter value using Exec GetContactByID 1.
Stored procedures are useful when your queries are quite complex such as using cursors or temporary tables. For basic stuff, simple SQL is sufficient. There is also a potential performance gain with stored procedures as they can be cached, but from what I understand, SQL Server has been caching the execution plans of standard queries for some time now so this may be an invalid point.