Common Stored Procedures in SQL Server

sp_executesql (link)
Executes a T-SQL statement or batch. This can contain embedded parameters
Difference between exec and sp_executesql 

sp_getapplock (link)
Locking tables or stored procedure at the same time

MSSQL Tips - Prevent Multiple users from running same SQL Server Stored Procedure at the same time. Also look at Transactions. 

There are many other database engine stored procedures you can use