Issues that should be paid attention to when using stored procedures (original)

There are many benefits to using stored procedures. Program In terms of security and performance, there is no need to say more here. everything has two sides, and it is impossible to be good everywhere. Then there will be no things to be reversed. the disadvantage of using stored procedures is that programming is troublesome. The conversion of data types during debugging and writing stored procedures can be painstaking. during the storage process, we often encounter a large number of parameters and different data types. the side effect is that a large storage process is too big to start. I feel like this in the process of using it. So I want to follow the rules in the future. I will try to solve the problem here, in case I will check it later. because I am using it recently, I am afraid I will forget it later. It is better to have a good memory than a bad pen. Let's keep it here for what my family often says: the first step in using a stored procedure is to write a stored procedure. preparations: 1. determine the parameters to be output and input. after designing the interface (such as datggrid), you can determine the output items. if there are too many parameters, you can't remember them by yourself, so you can take a stupid approach. according to the order of fields in the DataGrid, write the sequence number and field name in the database from start to end (written on paper, so that you can easily take the control, or print it out), for example: 1, lydwh
2, yqbh
3, flh ....... In this way, you can know the number of fields in the table as parameters and determine the order of fields. for example, during the update of the DataGrid, you can compare the list on the paper to check whether the assignment is correct or not. In addition, you can prevent the field from being dropped and go to debug.2 during running, determine the Data Type of each parameter. in this step, copy the design view of the database table as in 1. when writing a stored procedure, you do not need to look at the design view of the table. Switching is more error-prone. the purpose of writing these two lists on paper is to mark them with pens and mark them with different marks during verification. after completing these two steps, you can write the stored procedure. after writing the stored procedure, is it correct if there is no syntax error? (Is that true? If you have different opinions, that would be fine ). during the check, you can use ultraedit and other text editing tools to query rows and compare whether the number of parameters is the same. when naming a parameter, you can use the same name as the field in the database table, so that the parameters can be compared with each other without errors. after @ is added, the parameter names are different. You can add the same identifier to the output parameter, which facilitates batch replacement. Step 2: Call the stored procedure. here are some tips: when adding stored procedure parameters, you can copy the SQL Server Stored Procedure parameter table and write it directly in the program for necessary modifications (side effects, stored Procedure errors will be included in the program ). you can change the parameter name by batch replacement. when you compile a stored procedure callCodeFor more information, see the two tables in 1 and 2. Step 3: When writing a stored procedure, when there are many parameters, it is easy to get impatient. At this time, you must calm down and do it slowly. there are still some tips when writing input and output parameters. Don't keep thinking about losing yourself. After writing the stored procedure, you can run the program. Generally, an error occurs. the data type is inconsistent, and the parameter type does not match. do not be too busy at this time. perform the following steps: 1. First, check whether the value assignment parameter table is in the same sequence and name as the fields to be output in the program. 2. debug. at this time, check whether the name of each parameter matches the expected data type. Is there any exception that occurs during the parameter value assignment process. in this way, you can compare and modify. The above is just a summary of some minor problems encountered in the stored procedure, and may be added later. in data type conversion, do not convert datetime when assigning values, and convert the value of money type to decimal to assign values. int type does not need to be converted.