Source: http://www.erpsalon.org/
MS SQL Server is currently increasing the most important database management system above the Windownt operating system. With the launch of MS SQL Server2000, Microsoft's database service system really implements the situation in the WindowsNT / 2000 series operating system. In Microsoft's operating system, there is no database system to compete with it, including the leader database system of the leader Oracle in the database field. It is undeniable that MS SQL Server's largest shortcomings are only on Microsoft's own operating system, this is the fatal wound of MS SQL Server. But on the other hand, it has become the best accelerator, which prompted MS SQL Server to play its own features on its own "land" to the extreme, maximizing the various potentials of the WindowsNT series operating system. ! As an MS SQL Server database system is a very important concept is that stored procedures, reasonable use of stored procedures, can effectively improve program performance; and encapsulate commercial logic in stored procedures in the database system, it can greatly improve the entire software system Maintainability, when your business logic changes, no longer need to modify and compile client applications and re-distribute them into numerous users, you only need to modify the storage of the server-side implementation of the corresponding business logic. The process can be. Reasonable writing the stored procedures you need, you can maximize the use of MS SQL Server's resources.
Let's take a look at all kinds of skills experience in writing MS SQL Server stored procedures and using stored procedures! The premise below we discussed is that you have a certain MS SQL Server stored procedure, and the various techniques below are not specifically indicated, and they apply to MS SQL Server7.0 and MS SQL Server2000. 1. The skill of the stored procedure using the Output type parameter is a normal stored procedure to return a record set directly to the caller, but sometimes we only need some of the values of some of the parameters returned by the stored procedure. At this time, you can specify the stored procedure. Output parameters, such as: create procedure getname @uid nvarchar (1), @usernam nvarchar (10) = '' output as set @ username = 'Hongchao' Go In the above stored procedure, we pass the parameters is @uid, and Parameters @USERNAME does not need to pass it when they are toned.
In this way, the stored procedure will return to our parameter @USERNAME is 'hongchao'.
The above is easy, you need to pay attention to, when in SQL2000, if your stored procedure has only one parameter, and this parameter is an output type, you must give this parameter once when calling this stored procedure Value, otherwise the case where the call is incorrect! 2, the writing precautions in the stored procedure This is different in MS SQL Server 7.0 and MS SQL Server2000, and I don't know if it is Microsoft's omission, that is some system keywords in different versions. Different, such as keyword Level, the same sentence: Select * from users where level = 1 runs without slightest problems in the memory process in MS SQL Server7, but it will run in MS SQL Server2000. The reason is that "Level" is treated as a keyword in the MS SQL Server2000 (weird is also the keywords in SQL7, but there is no problem), so in SQL2000, the above statement should be changed to: SELECT * From users where [level] = 1
From the above example we can see that when you write a stored procedure, it is best to use "[" and "]" to use "[" and "]" to use "[" and "]" to avoid the occurrence of the transplantation process. Run error problem. 3. Precautions for using the system stored procedure SP_EXECUTESQL during the stored procedure We often use the system's stored procedure sp_execute when writing your own stored procedures. But what needs to be noted is that if you have a temporary Table operation in this stored procedure (generally a SQL statement), then this temporary table is invisible, that is, you can't The value is transmitted between the caller and the caller by temporary table. The solution is to use the global temporary Table, which is Table starting with "##". 4. Precautions for using temporary table and cursors during storage procedures If our business logic is more complicated, in the stored procedure, some media need to be a Tower, at this time, the temporary table has played a role, but be sure to remember in use. After that, even if you delete the temporary table. The only way to traverse a recordset in the stored procedure is to use the system cursor. It is also important to note that the resource he uses in time and destroy the cursor object in time after using the completion. And don't use the cursor at all, because he will take up more system resources, especially for large concurrency, it is easy to deplete system resources.
Use temporary Table and cursors to have advantages and disadvantages, you can use appropriate use during use! 5, in the stored procedure, the external ActiveX DLL program is somewhat special, we may need to call the external ActiveX DLL program. At this time, you need to use the system's stored procedure sp_oacreate and other relevant system stored procedures. The stored procedure starting with SP_OA can freely call the various methods and properties of the ActiveX DLL in their own stored procedures.
For example, the following example: declare @Object int declare @hr int declare @Property varchar (255) Declare @RETURN VARCHAR (255) Declare @src varchar (255), @Desc varchar (255) - Create an object (SqldMo.sqlserver ). EXEC @hr = sp_oacreate 'sqldmo. SQLServer ', @Object Out if @hr <> 0 Begin EX
EC sp_oageterrorinfo @Object, @src out, @Desc out select hr = convert (varbinary (4), @ HR), source = @ src, description = @ DESC RETURN END - Sets the properties of the object. EXEC @hr = sp_oasetproperty @Object, 'hostname', 'gizmo' if @hr <> 0 begin exec sp_oageterrorinfo @Object,
@Src out, @Desc out select hr = convert (varbinary (4), @ hr), source = @ src, description = @ DESC RETURN END - Gets the property value of the object via the Output parameter. Exec @hr = sp_oagetproperty @object, 'hostname', @Property Out if @hr <> 0 begin exec sp_oageterrorinfo @Object, @src out, @Desc ou
T select HR = Convert (Varbinary (4), @ HR), Source = @ src, description = @ desc Return End Print @Property - Method for calling objects EXEC @hr = sp_oamethod @Object, 'connect', null, ' MY_SERVER ',' my_login ',' my_password 'if @hr <> 0 begin exec sp_oageterrorinfo @Object,
@Src out, @Desc out select hr = convert (varbinary (4), @ hr), source = @ src, description = @ desc return end - destroy the created ActiveX object exec @hr = sp_oadestroy @Object if @hr <> 0 begin exec sp_oageterrorinfo @Object, @src out, @Desc out select hr = convert (Varbinary "
