It is also said that the stored procedure should not be used in the project to replace the SQL statement.

I will not say much about the storage process. I think you have all understood it.
Of course, stored procedures also have many disadvantages:
1. When there are more and more stored procedures, it is troublesome to find the stored procedure you want to modify in many stored procedures.
2. If an embedded SQL statement is used, you can modify the database operation statement when modifying the code.

In view of these two so-called shortcomings, I have put forward some of my views:
1. if there are too many stored procedures to find out, you should review whether your naming conventions are satisfactory or not, you should have consistent naming rules for stored procedures. Of course, this is not only required for stored procedures, but also for other parts. A good name for a stored procedure should preferably include the operation name (insert/update/get/list, etc.) and the Object Name (Table Name) to be operated. In this way, even if you have more stored procedures, you can quickly find the one you want to change, you can also use the object search function of SQL 2000 to find the names of all stored procedures related to the table by table name at one time. Similarly, you can use list to query, you can also find the stored procedure of all list functions.
2. I don't agree with the second point of view. In the past examples, I found that separating SQL statements from Code brings far more benefits than harm, this is more in line with the layering principle. If we embed SQL statements into the Code, when you only need to obtain the value of one more field or make some modifications to the SQL statement itself, you must compile and upload the DLL. If you are using a stored procedure, you can directly change the stored procedure. Besides, you can separate the two and write the stored procedure as a DBA, describe the instructions and usage rules and hand them over to the students responsible for writing the Dal layer. The students at the Dal layer close their eyes and can complete their work without understanding the SQL statements. Therefore, from this perspective, it is a good separation of work, and you don't have to write the Dal layer. You are also a master of SQL stored procedures.
3. Prevent injection attacks. If you use embedded SQL statements instead of stored procedures, you must perform more processing on the input user data to prevent injection attacks, for example, processing SQL sensitive characters
4. More importantly, if you want to insert binary content into a table, will you use SQL statements?
5. Embedded SQL statements, especially collage SQL statements, have always been a relatively problematic link. During the writing of stored procedures, they have been checked and stored, such as missing symbols, a small error that indicates that the number of insert fields is inconsistent with the number of parameters will be corrected immediately.
6. Everyone knows that the stored procedure is pre-compiled.
7. If you are a master, you can analyze and optimize the stored procedure to improve the performance. (I remember reading the technical support of MS to explain the analysis and optimization of the stored procedure, which is very enlightening)

Most commonly, in practice, to reduce the size of the dataset and improve the performance, we usually only select the fields currently needed. However, with the development, You can need other fields, if an embedded SQL statement is used, modify the SQL statement, compile the statement, and write the expression bound to the field. However, if you use a stored procedure, you only need to bind the expression, add the field name to the stored procedure.
Another example is that if you use string to splice SQL insert statements, it is likely to be like this.
String strsql = "insert into table (ID, username, password, address) value (" + id. tostring () + "," + username...
In this way, when you add more fields, the system will make a mistake.
If you use stored procedures
Sqlparameter mypara = new sqlparameter ("@ field5", field5 );
Add this input parameter to the stored procedure, and modify the SQL statement. The SQL statement will also help you check the syntax during the modification process.
The latter is obviously less likely to make mistakes when the former uses so many + signs and double quotation marks.

Finally, the above points only reflect my personal points of view. However, it is not what the book seems to be, but a few projects that I have made and learned while doing.