SQL Server stored procedure programming experience skills
Author: Suhongtu Ultra
Article Source:
Http://home.kzinfo.net/getc/200104/0406008.htm
MS SQL Server is currently increasing the most important database management system above the Windownt operating system. With the launch of MS SQL Server2000, Microsoft's database service system really implements the world of WindowsNT / 200 0 series operating system. The situation, in the Microsoft's operating system, no database system can compete with it, including the leader database system in the database field, Oracle. It is undeniable that MS SQL Server's largest shortcomings are only on 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, which prompted MS SQL Server to play its own features in its only "land" to the extreme, maximizing the various Windo WSNT series operating systems. Potential! As an MS SQL Server database system is a very important concept is that stored procedures, reasonable use of stored procedures, can effectively improve program performance; and encapsulate commercial logic in stored procedures in the database system, it can greatly improve the entire software system Maintainability, when your business logic changes, no longer need to modify and compile client applications and re-distribute them into numerous users, you only need to modify the storage of the server-side implementation of the corresponding business logic. The process can be. Reasonable writing the stored procedures you need, you can maximize the use of MS SQL Server's resources.
Let's take a look at all kinds of skills experience in writing MS SQL Server stored procedures and using stored procedures! 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. The skill of the stored procedure using the Output type parameter is a normal stored procedure to return a record set directly to the caller, but sometimes we only need some of the values of some of the parameters returned by the stored procedure. At this time, you can specify the stored procedure. Output parameters, such as: create procedure getname @uid nvarchar (1), @usernam nvarchar (10) = '' output as set @ username = 'Hongchao' Go In the above stored procedure, we pass the parameters is @uid, and Parameters @USERNAME does not need to pass it when they are toned.
In this way, the stored procedure will return to our parameter @USERNAME is 'hongchao'.
The above is easy, you need to pay attention to, when in SQL2000, if your stored procedure has only one parameter, and this parameter is an output type, you must give this parameter once when calling this stored procedure Value, otherwise the case where the call is incorrect! 2, the writing precautions in the stored procedure This is different in MS SQL Server 7.0 and MS SQL Server2000, and I don't know if it is Microsoft's omission, that is, some system keywords in different versions. Different, such as keyword Level, the same sentence: select * from users where level = 1 runs without slightest problems in the stored procedure in MS SQL Server7, but it will run in MS SQL Server 20 00 The reason is that "Level" is treated as a keyword in the MS SQL Server 2000 (weird is the same keyword in SQL7, 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 "]" to use "[" and "]" to avoid the occurrence of the transplantation process. Run error problem. 3. Precautions for using the system stored procedure SP_EXECUTESQL during the stored procedure We often use the system's stored procedure sp_execute when writing your own stored procedures. But what needs to be noted is that if you have a temporary Table operation in this stored procedure (generally a SQL statement), then this temporary table is invisible, that is, you can't The value is transmitted between the caller and the caller by temporary table. The solution method is to use the global temporary table, which is the Table starting with "##". 4. Precautions for using temporary table and cursors during storage procedures If our business logic is more complicated, in the stored procedure, some media need to be a Tower, at this time, the temporary table has played a role, but be sure to remember in use. After that, even if you delete the temporary table. The only way to traverse a recordset in the stored procedure is to use the system cursor, and it is also important to note that after using the completion, turn closer and destroy the cursor object to release the resources he use. And don't use the cursor at all, 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, in the stored procedure, the external ActiveX DLL program is somewhat special, we may need to call the external ActiveX DLL program. At this time, you need to use the system's stored procedure sp_oacreate and other relevant system stored procedures. The stored procedure starting with SP_OA can freely call the various methods and properties of the ActiveX DLL in their own stored procedures.
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 <> 0 Begin E
Xec sp_oageterrorinfo @Object, @src out, @Desc out select hr = communication (varbinary (4), @ hr), source = @ sr, 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 @Obje
CT, @src out, @Desc out select hr = convert (varbinary (4), @ HR), source = @ sr, description = @ DESC RETURN END - Gets the property value of the object via the Output parameter. Exec @hr = sp_oagetproperty @object, 'hostname', @P Roperty Out if @hr <> 0 begin exec sp_oageterrorinfo @Object, @src out, @d
ESC OUT SELECT HR = Convert (Varbinary (4), @ HR), Source = @ SR C, Description = @ Desc Return End Print @Property - Method for calling an object EXEC @hr = sp_oamethod @Object, 'Connect', NULL , 'M y_server', 'my_login', 'my_password' if @hr <> 0 Begin Exec sp_oageterrorinfo @o
Bject, @src out, @Desc out select hr = convert (varbinary (4), @ hr), source = @ sr, description = @ desc return end - destroyed the ActiveX object that has been created EXEC @hr = sp_oadestroy @Object IF @hr <> 0 begin exec sp_oageterrorinfo @Object, @src out, @Desc out select hr = convert (VA
