Why we should give preference to stored procedure?

A stored procedure is a group of SQL statements that have been created and stored in a database. A procedured can accept input parameters. When the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

Procedure Cache is the place where Execution Plan of Stored Procedure is stored. An execution plan states the efficient way in which the query(s) is executed. So whenever a normal query is executed its Execution Plan is created but when a Stored Procedure is executed, Execution plan is created and stored in Procedure Cache. Whenever the same procedure is executed and its execution plan exists in Procedure cache then it uses that execution plan rather than creating a new plan.