MS SQL Server is currently increasingly becoming the most important database management system above the Windownt operating system.
The launch of MS SQL Server2000, Microsoft's database service system is really realized in WindowsNT / 200
0 Series Operating System The situation in the world, on the Microsoft's operating system, no database system can compete with it, including the database of the database
Head Relivery Orthodox Office Database System Oracle. It is undeniable that MS SQL Server's largest shortcomings are only available
In 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.
Promote MS SQL Server only
"land
"The above has played your own function to the extreme, and the maximum use of Windo.
Various potentials in the WSNT series operating system! A very important concept in the MS SQL Server database system is that stored procedures, together
The use of stored procedures can effectively improve the performance of the program; and the commercial logic is encapsulated during the stored procedure in the database system, it can greatly improve the entire
Maintainability of the software system, when your business logic changes, no longer need to modify and compile client applications and re-distribute them
In many users, you only need to modify the stored procedures that implement the server-side implementation of the corresponding business logic. Reasonable writing the stored procedures you need,
Use the MS SQL Server's various resources to maximize the MS SQL Server. Let's take a look at all kinds of MS SQL Server
Store procedures and use stored procedures skills!
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. Tips for the storage procedure of the stored procedure using the Output type
The general stored procedure is directly returned to a recordset to the caller, but sometimes we only need some of the values of some of the parameters returned by the stored procedure, 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
Yes, when you are in SQL2000, if your stored procedure has only one parameter, and this parameter is Output type, you must give this parameter an initial value when calling this stored procedure, otherwise the call error will occur !
2, writing precautions during the stored procedure
This is different in MS SQL Server 7.0 and MS SQL Server2000, nor
Know that it is Microsoft's omission, that is, some system of keywords are different between different versions, such as keywords Level, the same sentence:
Select * from users where level = 1
There is no problem in the stored procedure in MS SQL Server7, but to MS SQL Server 20
In the 00, the error will occur, the reason is in the MS SQL Server2000
"Level"
"Wen as a keyword
(Weird is that SQL7 is also the same keyword, but there is no problem), so in SQL2000, the above statement should be changed:
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 it with the system keyword "[
"with
"]
"Putting him
Surrounded to avoid running errors in the transplantation process.
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 need
What should be noted is that if you have a temporary Table operation in this stored procedure (generally a SQL statement), the end is called
User, this temporary table is invisible, that is, you can't pass the temporary table to pass the value between the caller and the caller. solution
The method of decision is to use the global temporary table, that is
"##
"The beginning of the table.
4. Precautions for using temporary Table and cursors during storage
If our business logic is more complicated, in the storage process, some media need some media as a Tower, this time the temporary table plays, but please
Be sure after use, even if you delete the temporary TABLE used.
The only way to traverse a recordset in the stored procedure is to use the system cursor. It is also important to pay attention to the time after use.
Closed and destroy the cursor object to release the resources he use. And don't be in the case, don't use the cursor at will 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. This time you need to use the system's storage.
Cheng SP_OACREATE and other related system stored procedures, are stored in sp_oa, freely in their own storage
The various methods and properties of the ActiveX DLL are called. 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 <> 0begin
Exec sp_oageterrorinfo @Object, @src out, @Desc
OUT
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR
C, description = @ DESC
Return
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, @Descout
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR
C, description = @ DESC
Return
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, @Descout
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR
C, description = @ DESC
Return
End
Print @property
- Method for calling objects
Exec @hr = sp_oamethod @Object, 'connect', null, 'm
Y_Server ',' my_login ',' my_password '
IF @hr <> 0begin
Exec sp_oageterrorinfo @Object, @src out, @Desc
OUT
SELECT 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, @Descout
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR
C, description = @ DESC
Return
End
6, use database transaction processing during storage
In many cases, we will encounter the case where you need to operate multiple tables in the stored procedure. At this time, you need to avoid it during operation.
Data caused by external data. At this time, it is necessary to put the operation of the plurality of tables into the transaction.
But it should be noted that the RETURN statement cannot be used to exit using the return statement in the transaction, which will cause a transaction's abnormal error, and cannot guarantee the consistency of the data.
Sex.
Further, once multiple processing is placed in transactions, the processing speed of the system will decrease, so a plurality of segmented processing processes that frequently operate should be placed.
In the multiple stored procedures, this will greatly improve the response speed of the system, 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 to pass some of the above experience.
Summary, you can make you consciously avoid some detours when you apply the SQL Server stored procedure.
(All programs in Windows Advance Server2000 Chinese version MS SQL Server7.0 / 2000 Chinese version test)
