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 world of WindowsNT / 200 0 series operating system. The situation, in the Microsoft's operating system, no database system can compete with it, including the leader database system in the database field, Oracle. 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 in its only "land" to the extreme, maximizing the various Windo WSNT series operating systems. Potential! 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!
Below we discussed, you have a must have a certain MS SQL Server stored procedure, and the following tips are not particularly indicated, while applicable to MS SQL Server 7.0 and MS SQL Server2000.1, using Output Skills of stored procedures for type parameters
The general stored procedure is directly returned to a record set to the caller, but sometimes we only need some of the values of some parameters returned by the stored procedure, at this time, you can specify the Output parameters of the stored procedure, such as:
Create Procedure GetName
@UID nvarchar (1),
@Usernam nvarchar (10) = '' Output
AS
Set @ username = 'hongchao'
Go
In the above stored procedure, the parameters we pass are @uid, and the parameter @USERNAME does not need to pass when there is. 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, writing precautions during the stored procedure
This is different in MS SQL Server 7.0 and MS SQL Server2000, and I don't know if Microsoft's omission, that is, some system of keywords are different between different versions, such as keyword Level The same sentence:
Select * from users where level = 1
There is no slightest problem in the stored procedure among MS SQL Server7, but it will have an error in MS SQL Server 20 00 because "Level" is treated as a keyword in MS SQL Server2000 (strangely SQL7 is also the keyword, 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 system stored procedures SP_EXECUTESQL during stored procedures
When we write your own stored procedures, we often use the stored procedure sp_execute of the system in many cases. 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 method is to use the global temporary table, which is the Table starting with "##".
4. Precautions for using temporary Table and cursors during storage
If our business logic is more complicated, in the stored procedure, some media need some media as a Tower, this time the temporary table played, but be sure to remember that after using it, even if you delete the temporary Table. The only way to traverse a recordset in the stored procedure is to use the system cursor, and it is also important to note that after using the completion, turn closer and destroy the cursor object to release the resources he use. 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. Call the external ActiveX DLL program during the stored procedure
In some special cases, 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, which are stored in sp_oa, which can be free in yourself The various methods and properties of the ActiveX DLL are called when the stored procedure is called. For example, the example below:
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
EXEC SP_OAGETERRORINFO @Object, @SRC out, @Desc out
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR C, Description = @ Descreturn
End
- Set the properties of the object.
Exec @hr = sp_oasetproperty @OBject, 'Hostname', 'g izmo'
IF @hr <> 0
Begin
EXEC SP_OAGETERRORINFO @Object, @SRC out, @Desc out
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR C, Description = @ Descreturn
End
- Get the properties value of the object through the Output parameter.
Exec @hr = sp_oagetproperty @Object, 'hostname', @P Roperty OUT
IF @hr <> 0
Begin
EXEC SP_OAGETERRORINFO @Object, @SRC out, @Desc out
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR C, Description = @ Descreturn
End
Print @property
- Method for calling objects
Exec @hr = sp_oamethod @Object, 'connect', null, 'm y_server', 'my_login', 'my_password'
IF @hr <> 0
Begin
Exec sp_oageterrorinfo @Object, @src out, @Desc outselect hr = convert (varbinary (4), @ HR), Source = @ SR C, Description = @ DESC
Return
End
- Destroy the ActiveX object that has been created
Exec @hr = sp_oadestroy @Object
IF @hr <> 0
Begin
EXEC SP_OAGETERRORINFO @Object, @SRC out, @Desc out
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR C, Description = @ Descreturn
End
6, use database transaction processing during storage
In many cases, we will encounter the case where multiple tables need to be operated simultaneously during the stored procedure, which is required to avoid the inconsistency of data caused by external operations during operation. At this time, it is necessary to put the operation of the plurality of tables into the transaction.
However, it should be noted that it cannot be forcibly exit using the return statement in the transaction, which will cause non-normal errors of transactions, and cannot guarantee the consistency of data.
Further, once multiple processing is placed in transactions, the processing speed of the system will decrease, so the frequently operated plurality of segmentable processing processes should be placed in multiple stored procedures, which greatly improves the system response. The speed, but the premise is not to violate the consistency of the data.
After reading these skills in the SQL Server stored procedure, I believe that some help you will more or less, and I hope that through some of the experience, you can make you feel aware of the SQL Server stored procedure. Some detours.
(All programs in Windows Advance Server2000 Chinese version MS SQL Server7.0 / 2000 Chinese version test)
