You can use a function anywhere that you can use a scalar value or a table. Functions can be used in constraints, computed columns, joins, WHERE clauses, or even in other functions. Functions are an incredibly powerful part of SQL Server. Also you can use stored procedures when want to allow modular programming, faster execution, reduce traffic and also you can use Stored procedures in security mechanism