SQL Server Stored Procedure programming experience and skills

The premise we will discuss below is that you have some experience writing ms SQL Server Stored Procedures.
Specifically, it applies to both ms SQL Server7.0 and ms SQL Server2000.

1. Tips for using output-type parameters

Generally, a stored procedure directly returns a record set to the caller, but sometimes we only need some parameter values returned by the stored procedure.
You can specify the output parameter of the stored procedure, for example:

Create procedure getname

@ Uid nvarchar (1 ),

@ Usernam nvarchar (10) = ''output

As

Set @ username = 'hongchunao'

Go

In the above stored procedure, the parameter we passed is @ uid, while the parameter @ username does not need to be passed in when it is called.
In this way, the value of @ username returned by the stored procedure is 'hongchun '. All of the above are relatively easy to use.
Yes. In SQL2000, if your stored procedure has only one parameter and this parameter is of the output type, you must
When using this stored procedure, give this parameter an initial value. Otherwise, a call error may occur!

2. Notes for writing during storage

This is different in ms SQL Server7.0 and ms SQL Server2000.
Knowing whether Microsoft is missing, that is, some system keywords are different in different versions, such as the keyword level, the same sentence:

Select * from users where level = 1

In the stored procedures of ms SQL Server7, there is no problem at all, but in the ms SQL server20
00, a running error occurs because the "level" in ms SQL Server2000 is treated as a keyword.
(The strange thing is that SQL 7 is also a 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 "]" where possible and system keywords
To avoid running errors during transplantation.

3. Notes for using the system stored procedure sp_executesql in Stored Procedures

When writing our own stored procedures, we often use the system's stored procedure sp_execute in many cases. However
Note that if you perform temporary table operations in the parameters of the stored procedure (generally an SQL statement ),
The temporary table is invisible, that is, you cannot use the temporary table to pass values between the caller and the called. Solution
The method is to use a global temporary table, that is, the table starting.

4. Precautions for using temporary tables and cursors during storage

If our business logic is complex, we need some media as a medium turntable in the storage process. At this time, the temporary table metadata plays a role, but please
Remember to delete the temporary table after use.

In the stored procedure, the only way to traverse a record set in sequence is to use the system cursor. Also note that
Close and destroy the cursor object to release the resources it uses. Do not use the cursor unless you have to, because it occupies a large amount of system resources,
Especially in the case of high concurrency, it is easy to cause the system to run out of resources and crash.

The use of temporary tables and cursors have their own advantages and disadvantages. You must make proper use of them during use!

5. Call the external ActiveX dll program in the Stored Procedure

In some special cases, we may need to call the external ActiveX dll program. At this time, we need to use the stored
Process sp_oacreate and other related system stored procedures are stored procedures starting with sp_oa, and can be stored freely in your own
Call various methods and Properties of ActiveX dll during the process. For 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 <> 0

Begin

Exec sp_oageterrorinfo @ object, @ SRC out, @ DESC
Out

Select hR = convert (varbinary (4), @ HR), source = @ SR
C, description = @ DESC

Return

End

-- Set the attributes of an 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 = @ DESC

Return

End

-- Get the object property value 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 = @ DESC

Return

End

Print @ Property

-- Call the object Method

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
Out

Select hR = convert (varbinary (4), @ HR), source = @ SR
C, description = @ DESC

Return

End

-- Destroy the created ActiveX Object

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 = @ DESC

Return

End

6. Use Database Transaction Processing in the Stored Procedure

In many cases, we may encounter the need to operate multiple tables simultaneously in the stored procedure. In this case, we need to avoid
Data inconsistency. At this time, you need to put the operations that operate on multiple tables into the transaction for processing.

However, you must note that you cannot use the return statement to forcibly exit the transaction. This will lead to an abnormal transaction error and cannot guarantee data consistency.
.

In addition, once multiple processes are put into the transaction, the processing speed of the system will be reduced. Therefore, we should put multiple sharded processing processes with frequent operations
In multiple stored procedures, this will greatly improve the system response speed, but the premise is not against data consistency.

After reading the skills in writing the SQL server stored procedure above, I believe it will be helpful to you more or less and hope to pass the above experience.
To sum up, you can consciously avoid some detours when using SQL Server Stored Procedures.