VB implements SQL Server 2000 stored procedure call

This article takes the SQL Server2000 stored procedure as an example, discusses the advantages of the stored procedure, and uses Visual Basic6.0 programming language to provide key code for calling the stored procedure.

　　Keywords: Database; SQL Server2000; stored procedures; applications; VB
　
　　Stored Procedure

Stored procedures are a set of pre-compiled Transact-SQL statements stored on the server. They are a method for encapsulating repetitive task operations. They support user-provided variables and have powerful programming functions. It is similar to the BAT file in the dossystem. The BAT file can contain a group of frequently executed commands that are executed through the execution of the BAT file. In the same way, you can write a lot of Transact-SQL statements to complete a task and organize them into a stored procedure. By executing this stored procedure, you can complete this task. The stored procedure is different from the BAT file, that is, the stored procedure has been pre-compiled.

1. How to Create a stored procedure

In the Transact-SQL language, you can use the CREATE PROCEDURE statement to CREATE a stored PROCEDURE. The syntax format is as follows:

Create proc [EDURE] procedure_name [; number] [{@ Parameter data_type} [VARYING] [= default] [OUTPUT] ],… N] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [For replication] AS SQL _statement [... N]

In the preceding create procedure statement, the content in square brackets "[]" is optional, and the content in curly brackets "{}" must appear and cannot be omitted ,[,... N] indicates the previous parameter style, which can be repeated. Vertical bars "|" indicates either of the two options.

The following describes the meanings of various options in the statement.

Create procedure is a keyword and can also be written as create proc.

Procedure_name is the name of the stored procedure. The name can be any identifier that complies with the naming rules. The [; number] parameter after the name indicates that a series of Stored Procedure names can be defined. The number of stored procedures is specified by number.

You can use @ parameter data_type to specify the parameter name. In the Transact-SQL language, the user-defined parameter names are preceded by the "@" symbol. These data types are various data types allowed by the Transact-SQL language, includes the data types provided by the system and user-defined data types.

When the parameter type is cursor, the keywords VARYING and OUTPUT must be used. VARYING indicates that the result set can be an output parameter and its content is dynamic. This keyword can only be used when the cursor is used as the data type. The keyword OUTPUT indicates that this is an OUTPUT parameter. You can return the result information of the stored procedure to the application.
Default is used to specify the default value of a parameter.

The RECOMPILE option indicates re-compiling the stored procedure. This option is used only when necessary. For example, you often need to change the database mode.

The ENCRYPTION option is used to encrypt the text of the stored procedure to prevent others from viewing it.

Option for replication is mainly used during the REPLICATION process. Note: This option cannot be used with RECOMPILE.

AS is a keyword, indicating that the subsequent content is the statement of the stored procedure. Parameter SQL-statement [... N] indicates that a stored procedure can contain multiple Transact-SQL statements.

2. Advantages of Stored Procedures

In systems with frequent access to databases, developers are happy to use stored procedures, which are inseparable from the following advantages of stored procedures.

(1) stored procedures can share the application logic with other applications to ensure consistent data access and manipulation.

(2) stored procedures provide a security mechanism. If a user is granted the permission to execute a stored procedure, even if the user does not have the permission to access the table or view referenced during the execution of the stored procedure, this user can also fully execute the stored procedure without being affected. Therefore, you can create a stored procedure to add, delete, and perform other operations, and control the information access permissions through programming.

(3) The execution speed of the stored procedure is fast to improve the system performance. After the first execution of the stored procedure, the execution plan is stored in the high-speed buffer storage area of the process. In future operations, you only need to call the compiled binary stored procedure from the high-speed buffer storage area for execution.

(4) using stored procedures can reduce network transmission time. If there are one thousand statements of Transact-SQL statements, which are transmitted one by one between the client and the server over the network, the transmission will take a long time. However, if you write these one thousand statements as a complex Stored Procedure command, the time required for network transmission between the client and the server will be greatly reduced.

　　SQL Server 2000 database Stored Procedure Call

As one of the most common database client development tools, VB provides powerful support for client applications to call server-side stored procedures. In particular, with the launch of VB6.0, VB client applications can conveniently use ADO objects and sets to call database stored procedures.

In the sci-tech archive management system compiled by the author, VB is used as the development platform and SQL Server2000 Database is used for data management. This technology archive management system contains massive data, frequent access to the database saves execution time and greatly improves system performance.

1. Introduction to ADO

The ADO control (also known as the ADO Data Control) is similar to the inherent Data Control of VB. Using the ADO Data control, you can use Microsoft ActiveX Data Objects (ADO) to quickly establish a connection between the control bound to the database and the Data provider.

The ADO Data control can implement the following functions:

· Connect to a local database or remote database.

· Open a specified database table, or define a set of records for queries, stored procedures, or views of tables in the database based on the Structured Query Language (SQL.

· Pass the values of data fields to the data binding controls to display or change these values in these controls.

· Add a new record or update a database based on the data displayed in the bound control.

2. Database Connection

You can use the ADO Control to connect to a database. Therefore, you must select Microsoft ADO Data Control 6.0 (OLEDB) in the project component and add the ADO Control to the form. There are two methods to connect to a database using ADO.

1) connection through the ADODC property page

On the ADODC properties page, select generate to go to the Data Link Properties dialog box. Then, select the connection properties page in the dialog box, select or enter important information such as the server name and database, and test the connection, after the connection is successful, click OK to return to the properties page dialog box to obtain the connection string, as shown in the following example:

Provider = SQLOLEDB.1; Persist Security Info = False; User ID = sa; Initial Catalog = Science_File; Data Source = Data_Server

Sa indicates the user name, Science_File indicates the database name, And Data_Server indicates the database name.

Use the following statements to connect to the specified database:

Dim odbcstr as String, adocon As New ADODB. Connection Odbcstr = "Provider = SQLOLEDB.1; Persist Security Info = False; User ID = sa; Initial Catalog = Science_File; Data Source = Data_Server" Adocon. Open odbcstr 'connect to the database

2) use the connection statement directly.

The database connection statement is as follows:

Dim ado as ADODC Ado. connectionString = "Provider = SQLOLEDB.1; Password =" & User_Pwd & "; Persist Security Info = True; User ID =" & User_Name & "; Initial Catalog =" & Data_Name &"; data Source = "& server_name

User-Pwd indicates the User password, User_Name indicates the User name, Data_Name indicates the database name, And server_name indicates the server name.

After successfully connecting to the database, you can call the Stored Procedure for operations.

3. Call stored procedures

Assume that a stored procedure named doc_ProcName has an input parameter and an output parameter.

1) directly PASS Parameters to call the Stored Procedure

Directly passing parameters is implemented through the following steps:

(1) Open the Connection to the data source through the Connection object of ADODB;

(2) Specify the Connection object to which the Command object belongs through ActiveConnection;

(3) set the source of the Command object through the CommandText attribute, that is, the stored procedure to be called;

(4) The CommandType attribute is used to determine the source type of the Command object. If the source type is stored in the Stored Procedure CommandType, It is adw.storedproc;

(5) pass Parameters to the called stored procedure through the Parameters set of the Command object. The Parameters (0) object is the return value of the stored procedure. If the return value is 0, the stored procedure is successfully executed;

(6) execute the stored procedure specified in the CommandText attribute through the Eexecute method.

Take the Stored Procedure doc_ProcName as an example. The key code is as follows:

Dim strS As String 'defines a variable The Dim adoconn As New ADODB. Connection 'Connection object indicates that the Connection to the data source is opened. The Dim adocomm As New ADODB. Command 'COMMAND object defines the specified Command to be executed on the data source. Dim ReturnValue As Integer 'calls the return value of the stored procedure Adoconn. ConnectionString = Adodc1.ConnectionString 'adodc1 is the ADO control in the form and has successfully connected to the database. Adoconn. Open Set adocomm. ActiveConnection = adoconn 'indicates the Connection object to which the specified Command object belongs. Adocomm. CommandText = "doc_ProcName" 'sets the Command object source. Adocomm. CommandType = adw.storedproc 'notifies the provider of the CommandText attribute, which may include the source type of the Command object. Setting this attribute optimizes the command execution. Adocomm. Parameters (1) = "1" Adocomm. Parameters (2) = "OutputParameters" 'outputparameters can be any string or number Adocomm. Execute ReturnValue = adocomm. Parameters (0) 'returned value of the stored procedure. If 0 is returned, the execution is successful. StrS = adocomm. Parameters (2) 'assigns the output parameter value of the stored procedure to the variable strS

2) append the parameter method to call the Stored Procedure

The CreateParameter method is used to specify the attribute to create a new Parameter object. The syntax is as follows:

Set parameter = command. CreateParameter (Name, Type, Direction, Size, Value)

· Name (optional) is a string that represents the Parameter object Name.
· Type (optional) long integer value, which specifies the Data Type of the Parameter object.
· Direction (optional) long integer value, which specifies the Parameter object type.
· Size (optional) long integer value. It specifies the maximum length of the parameter value (in characters or bytes ).
· Value (optional) specifies the Parameter object Value.

This method is different from the above method in that when the append parameter method transmits parameters to the stored procedure, this method first uses the CreateParameter method to create parameters for the stored procedure, then, Append the created Parameters to the Parameters set through the Append method.

Take the Stored Procedure doc_ProcName as an example. The key code is as follows:

The Dim mRst As ADODB. Recordset 'recordset object represents a complete set of records from basic tables or command execution results. The Dim prm As ADODB. Parameter 'parameter object represents a Parameter or a Parameter associated with a Command object based on a parameterized query or stored procedure. Adoconn. ConnectionString = Adodc1.ConnectionString Adoconn. Open Set adocomm. ActiveConnection = adoconn Adocomm. CommandText = "doc_ProcName" Adocomm. CommandType = adw.storedproc Set prm = adocomm. CreateParameter ("parameter1", adTinyInt, adParamInput, "1 ") Adocomm. Parameters. Append prm Set prm = adocomm. CreateParameter ("parameter2", adInteger, adParamOutput) Adocomm. Parameters. Append prm Set mRst = adocomm. Execute ReturnValue = adocomm. Parameters (0)

The undefined variables and uncommented statements in the above Code are the same as those described above.

　　Conclusion

Calling server-side stored procedures in applications not only significantly improves the performance of the entire application, but also enhances the protection of database data. VB provides a convenient and effective method for client applications to call stored procedures.