MS SQL Server is currently increasingly becoming the most important database management system above the Windownt operating system.
http://www.acnow.net/ SB
The launch of MS SQL Server2000, Microsoft's database service system is really realized in WindowsNT / 200
http://www.acnow.net/ SB
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
http://www.acnow.net/ SB
Head Relivery Orthodox Office Database System Oracle. It is undeniable that MS SQL Server's largest shortcomings are only available
http://www.acnow.net/ SB
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.
http://www.acnow.net/ SB
Promote MS SQL Server to play their own features on their own only "land", maximizing Windo
http://www.acnow.net/ SB
Various potentials in the WSNT series operating system! A very important concept in the MS SQL Server database system is that stored procedures, together
http://www.acnow.net/ SB
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
http://www.acnow.net/ SB
Maintainability of the software system, when your business logic changes, no longer need to modify and compile client applications and re-distribute them
http://www.acnow.net/ SB
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,
http://www.acnow.net/ SB
Use the MS SQL Server's various resources to maximize the MS SQL Server. Let's take a look at all kinds of writing MS SQL ServerHttp: //www.acnow.net/ SB
Store procedures and use stored procedures skills!
http://www.acnow.net/ Euqackv
Below we discuss, you have a must have a certain MS SQL Server store process, and if you don't have the following skills
http://www.acnow.net/ SB
Special indication is applied to MS SQL Server 7.0 and MS SQL Server2000.
http://www.acnow.net/ Euqackv
1. Tips for the storage procedure of the stored procedure using the Output type
http://www.acnow.net/ Euqackv
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
http://www.acnow.net/ SB
You can specify the OUTPUT parameters of the stored procedure, such as:
http://www.acnow.net/ Euqackv
Create Procedure GetName
http://www.acnow.net/ Euqackv
@UID nvarchar (1),
http://www.acnow.net/ Euqackv
@Usernam nvarchar (10) = '' Output
http://www.acnow.net/ Euqackv
AS
http://www.acnow.net/ Euqackv
Set @ username = 'hongchao'http://www.acnow.net/ Euqackv
Go
http://www.acnow.net/ Euqackv
In the above stored procedure, the parameters we pass are @uid, and the parameter @USERNAME does not need to pass when there is.
http://www.acnow.net/ SB
In this way, the stored procedure will return to our parameter @USERNAME is 'hongchao'. The above is easy, you need to pay attention
http://www.acnow.net/ SB
Yes, when in SQL2000, if your stored procedure has only one parameter, this parameter is Output type, you must be toned
http://www.acnow.net/ SB
Use this stored procedure to give this parameter an initial value, otherwise the call error will appear!
http://www.acnow.net/ Euqackv
2, writing precautions during the stored procedure
http://www.acnow.net/ Euqackv
This is different in MS SQL Server 7.0 and MS SQL Server2000, nor
http://www.acnow.net/ SB
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:
http://www.acnow.net/ Euqackv
Select * from users where level = 1
http://www.acnow.net/ Euqackv
There is no slightest problem in the stored procedures in MS SQL Server7, but to MS SQL Server 200HTTP: //www.acnow.net/ SB
In the 00, an error will occur because "Level" is treated as a keyword in the MS SQL Server2000.
http://www.acnow.net/ SB
(Weird is that SQL7 is also the same keyword, but there is no problem), so in SQL2000, the above statement should be changed:
http://www.acnow.net/ Euqackv
Select * from users where [level] = 1
http://www.acnow.net/ Euqackv
From the above example we can see that when you write a stored procedure, it is best to use "[" and "]" to use "[" and "]" in places with the system keyword.
http://www.acnow.net/ SB
Surrounded to avoid running errors in the transplantation process.
http://www.acnow.net/ Euqackv
3. Precautions for using system stored procedures SP_EXECUTESQL during stored procedures
http://www.acnow.net/ Euqackv
When we write your own stored procedures, we often use the stored procedure sp_execute of the system in many cases. But need
http://www.acnow.net/ SB
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
http://www.acnow.net/ SB
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.解 http://www.acnow.net/ SB
The method of decision is to use the global temporary table, which is the Table started with "##".
http://www.acnow.net/ Euqackv
4. Precautions for using temporary Table and cursors during storage
http://www.acnow.net/ Euqackv
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
http://www.acnow.net/ SB
Be sure after use, even if you delete the temporary TABLE used.
http://www.acnow.net/ Euqackv
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.
http://www.acnow.net/ SB
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.
http://www.acnow.net/ SB
Especially for large concurrency, it is easy to deplete system resources.
http://www.acnow.net/ Euqackv
Use temporary Table and cursors to have advantages and disadvantages, you can use appropriate use during use!
http://www.acnow.net/ Euqackv
5. Call the external ActiveX DLL program during the stored procedure
http://www.acnow.net/ Euqackv
In some special cases, we may need to call the external ActiveX DLL program. This time you need to use the system's storage.
http://www.acnow.net/ SB
Cheng SP_OACREATE and other related system stored procedures, are stored in sp_oa, freely in their own storage
http://www.acnow.net/ SB
The various methods and properties of the ActiveX DLL are called. For example, the example below:
http://www.acnow.net/ Euqackv
Declare @Object INTHTTTP: //www.acnow.net/ Euqackv
Declare @hr int
http://www.acnow.net/ Euqackv
Declare @Property Varchar (255)
http://www.acnow.net/ Euqackv
Declare @Return Varchar (255)
http://www.acnow.net/ Euqackv
Declare @src varchar (255), @Desc varchar (255)
http://www.acnow.net/ Euqackv
- Create an object (SqldMo.sqlServer).
http://www.acnow.net/ Euqackv
EXEC @hr = sp_oacreate 'sqldmo. SQLServer ', @Object
http://www.acnow.net/ SB
OUT
http://www.acnow.net/ Euqackv
IF @hr <> 0
http://www.acnow.net/ Euqackv
Begin
http://www.acnow.net/ Euqackv
Exec sp_oageterrorinfo @Object, @src out, @Desc
http://www.acnow.net/ SB
OUT
http://www.acnow.net/ Euqackv
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR
http://www.acnow.net/ SB
C, description = @ DESC
http://www.acnow.net/ Euqackv
Return
http://www.acnow.net/ Euqackv
End
http://www.acnow.net/ Euqackv
- Set the properties of the object.
http://www.acnow.net/ Euqackv
Exec @hr = sp_oasetproperty @Object, 'hostname', 'ghttp://www.acnow.net/ SB
Izmo '
http://www.acnow.net/ Euqackv
IF @hr <> 0
http://www.acnow.net/ Euqackv
Begin
http://www.acnow.net/ Euqackv
Exec sp_oageterrorinfo @Object, @src out, @Desc
http://www.acnow.net/ SB
OUT
http://www.acnow.net/ Euqackv
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR
http://www.acnow.net/ SB
C, description = @ DESC
http://www.acnow.net/ Euqackv
Return
http://www.acnow.net/ Euqackv
End
http://www.acnow.net/ Euqackv - Get the properties value of the object through the Output parameter.
http://www.acnow.net/ Euqackv
Exec @hr = sp_oAgetProperty @object, 'Hostname', @P
http://www.acnow.net/ SB
ROPERTY OUT
http://www.acnow.net/ Euqackv
IF @hr <> 0
http://www.acnow.net/ Euqackv
Begin
http://www.acnow.net/ Euqackv
Exec sp_oageterrorinfo @Object, @src out, @Desc
http://www.acnow.net/ SB
OUT
http://www.acnow.net/ Euqackv
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR
http://www.acnow.net/ SB
c, description = @ designttp://www.acnow.net/ Euqackv
Return
http://www.acnow.net/ Euqackv
End
http://www.acnow.net/ Euqackv
Print @property
http://www.acnow.net/ Euqackv
- Method for calling objects
http://www.acnow.net/ Euqackv
Exec @hr = sp_oamethod @Object, 'connect', null, 'm
http://www.acnow.net/ SB
Y_Server ',' my_login ',' my_password '
http://www.acnow.net/ Euqackv
IF @hr <> 0
http://www.acnow.net/ Euqackv
Begin
http://www.acnow.net/ Euqackv
Exec sp_oageterrorinfo @Object, @src out, @Desc
http://www.acnow.net/ sbout
http://www.acnow.net/ Euqackv
SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR
http://www.acnow.net/ SB
C, description = @ DESC
http://www.acnow.net/ Euqackv
Return
http://www.acnow.net/ Euqackv
End
http://www.acnow.net/ Euqackv
- Destroy the ActiveX object that has been created
http://www.acnow.net/ Euqackv
Exec @hr = sp_oadestroy @Object
http://www.acnow.net/ Euqackv
IF @hr <> 0
http://www.acnow.net/ Euqackv
Begin
http://www.acnow.net/ Euqackv
Exec sp_oageterrorinfo @Object, @src out, @Desc
http://www.acnow.net/ SB
OUT
http://www.acnow.net/ EuqackVSelect HR = Convert (Varbinary (4), @ HR), Source = @ SR
http://www.acnow.net/ SB
C, description = @ DESC
http://www.acnow.net/ Euqackv
Return
http://www.acnow.net/ Euqackv
End
http://www.acnow.net/ Euqackv
6, use database transaction processing during storage
http://www.acnow.net/ Euqackv
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.
http://www.acnow.net/ SB
Data caused by external data. At this time, it is necessary to put the operation of the plurality of tables into the transaction.
http://www.acnow.net/ Euqackv
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.
http://www.acnow.net/ SB
Sex.
http://www.acnow.net/ Euqackv
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.
http://www.acnow.net/ SB
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.
http://www.acnow.net/ Euqackv
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.
http://www.acnow.net/ SB
Summary, you can make you consciously avoid some detours when you apply the SQL Server stored procedure.
http://www.acnow.net/ Euqackv
(All programs in Windows Advance Server2000 Chinese version MS SQL Server7.0 / 2000 Chinese version test)
