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 in SQL2000, if your stored procedure has only one parameter, this parameter is Output type, you must be toned
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 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 problem in the stored procedure in MS SQL Server7, but to MS SQL Server 20
In the 00, an error will occur because "Level" is treated as a keyword in the MS SQL Server2000.
(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 "[" and "]" to use "[" and "]" in places 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 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), 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, 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, 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 example below:
Declare @Object Int
Declare @hr intDeclare @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, @Descout
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 the consistency of the data.
7 Try to declare the variable of the table type, not to create a temporary table.
Declare Table @MYTB (ID INT, Name Nvarchar (10))
This type is a table type, can be used as a temporary table (sometimes you can't meet your functional requirements), but the system overhead is smaller than the temporary table.
