MS SQL Server is currently increasingly becoming the most important thing in the Windownt operating system
The launch of MS SQL Server2000, Microsoft's database service system is really realized
0 Series Operating System One Uniform World, in Microsoft's operating system, no database system can
Head Relivery Orthodox Office Database System Oracle. It is undeniable that MS SQL Seri
In Microsoft's own operating system, this is the fatal wound of MS SQL Server. But in another
Promote MS SQL Server to play its own features on its own "land"
Various potentials in the WSNT series operating system! Very heavy in the MS SQL Server database system
The use of stored procedures can effectively improve the performance of the program; and encapsulate commercial logic in the database system.
Maintainability of the software system, when your business logic changes, no longer need to modify and compile the client
Many user hands, you only need to modify
The server-side implementation of the corresponding business logic stored procedures. reasonable
Use the MS SQL Server's various resources to maximize the MS SQL Server. Let's take a look at various edits.
Store procedures and use stored procedures skills!
The premise below we discussed is what you have had a certain MS SQL Server stored procedure.
Special indication, then apply to MS SQL Server 7.0 and MS SQL Serv
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 storage.
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 is
In this way, the stored procedure will return to our parameter @USERNAME is 'hongchao'. on
Yes, when in SQL2000, if your stored procedure has only one parameter, this parameter is O
Use this stored procedure to give this parameter an initial value, otherwise the call error will appear!
2, writing precautions during the stored procedure
This is in MS SQL Server 7.0 and MS SQL Server200
Knowing that it is Microsoft's omission, that is, some keywords are different between different versions, such as the key
Select * from users where level = 1 runs without a slightest problem among MS SQL Server7, but it is M
In the 00, the error will occur, the reason is in the MS SQL Server2000
(The strange thing is that SQL7 is also the keyword, but there is no problem), so in SQL2000,
Select * from users where [level] = 1
From the above example we can see that when you write a stored procedure, it is best possible to be possible with the system keyword.
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 system's stored procedures in many cases.
What to pay attention is that if you have a temporary in the parameter of this stored procedure (generally a SQL statement)
For use, this temporary table is invisible, that is, you can't adjust by temporary table.
The method of decision is to use the global temporary table, which is the Table started 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, at this time
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, which also wants to close 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,
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
Cheng SP_OACREATE and other related system stored procedures, are stored in SP_OA
The various methods and properties of the ActiveX DLL are 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 VARC
- Create an object (SqldMo.sqlServer). EXEC @hr = sp_oacreate 'sqldmo. SQLSER
OUT
IF @hr <> 0
Begin
Exec sp_oageterrorinfo @Object, @S
OUT
SELECT HR = Convert (Varbinary (4), @ HR
C, description = @ DESC
Return
End
- Set the properties of the object.
Exec @hr = sp_oasetproperty @object,
Izmo '
IF @hr <> 0begin
Exec sp_oageterrorinfo @Object, @S
OUT
SELECT HR = Convert (Varbinary (4), @ HR
C, description = @ DESC
Return
End
- Get the properties value of the object through the Output parameter.
Exec @hr = sp_oAgetProperty @object,
ROPERTY OUT
IF @hr <> 0
Begin
Exec sp_oageterrorinfo @Object, @S
OUT
SELECT HR = Convert (Varbinary (4), @ HRC, Description = @ DESC
Return
End
Print @property
- Method for calling objects
Exec @hr = sp_oamethod @Object, 'conn
Y_Server ',' my_login ',' my_password '
IF @hr <> 0
Begin
Exec sp_oageterrorinfo @Object, @S
OUT
SELECT HR = Convert (Varbinary (4), @ HRC, 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, @S
OUT
SELECT HR = Convert (Varbinary (4), @ HR
C, description = @ DESC
Return
End
6, use database transaction processing during storage
In many cases, we will encounter the case where multiple tables needed simultaneously during the stored procedure, and the data caused by the outside of this time is inconsistent. 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 use the return statement in the transaction, which will trigger a transaction's non-positive
Sex.
Further, once multiple processing is placed in the transaction, the system's processing speed will decrease, so it should be frequent.
In the multiple stored procedures, this will greatly improve the response speed of the system, but the premise is not to violate the data.
After reading these skills in the SQL Server stored procedures, I believe some help you or less
Summary, you can make you consciously avoid some detours when you apply the SQL Server stored procedure.
