Is STORED PROCEDURE the best way to use in large database?

I wouldn't say that the size of the database should be the driving force to use or not to use procedures. After all a database which someone thinks large may be small to another.

The factors that affect the usage of the procedures include:

• Eliminate round-trips, with procedures you may be able to eliminate the need of transferring data back and forth from the database. This means that a single unit of work may perform better if the whole operation is done on the database side
• Security, with procedures you can prevent the user (or application) from seeing or modifying the underlying tables allowing only controlled route of access, the procedure
• Maintainability, think of the procedure like an interface. For example you can change the underlying data structure and still provide the same result to the calling side
• Dynamic queries, creating and handling dynamic queries is typically more inconvenient and difficult inside procedures
• User conversation, user conversation isn't allowed inside a transaction so this needs to be taken into account when designing the architecture
• Multiple callers, if your environment has multiple applications, it may be easier to implement the logic inside a procedure and call it from several places than to implement the logic in multiple places at calling side. This can happen for example in development environments utilizing multiple languages

As you can see, I didn't include the size of the database to the list nor did I include the speed of a single operation. Very often these are not affected whether you use procedure or not.

That's a good question, and point for heated discussions.
Personally I prefer to keep all code together (C# and database code), but in big databases there might be (perfomance) reasons to opt for stored procedures.
We had problems with SQL Server performance when the database size and number of queries was growing, but we solved this by switching to PostgreSQL and did not use stored procedures.
Here an advantage of our DAL (Data Access Layer) showed up: it was relatively easy to convert the code from SQL Server to PostgreSQL as all code was in one place.
If you are interested in database conversion to PostgreSQL, see: Convert SQL Server Database to PostgreSQL[^]

To the people downvoting this without leaving any comment, my question is: I would really like to know why, is it just because you are convinced SQL Server is the best ?
Have you ever tried PostgreSQL ? if so, what are your arguments against it ?