Summary of calling the Stored Procedure (TRANSACT-SQL) method of ms SQL Server database in easy language

Author: liigo

Date: 2010/8/25

Original: http://blog.csdn.net/liigo/archive/2010/08/24/5834450.aspx

Reprinted please indicate the source: http://blog.csdn.net/liigo

 

Microsoft SQL Server database stored procedures, according to their input and output data, can be divided into the following general situations or their combination: no input, one or more input parameters, no output, return a value directly. One or more values are returned through the output parameter, and A recordset is returned ). In either case, no matter how complicated the input and output parameters are stored, you can call them correctly in easy language, pass in the parameters accurately, and obtain the correct output data. The following section describes how to call the stored procedure of the ms SQL Server database in easy language in multiple cases: use the database operation support library (edatabase. FNE ). Some people have said that the stored procedure in the database cannot be called in easy language, or the method of calling the stored procedure can be referred to in this article.

 

1. Call the "no input/output data" Stored Procedure

 

This is the simplest case. It is OK to execute a simple SQL statement. The following code is provided:

Database Connection 1. Execute SQL ("Exec dbproc ")

"Database connection 1" is an instance of the "database connection" Control Supported by database operations in the database. "EXEC" indicates that the stored procedure is called, "dbproc" is the name of the called stored procedure. You can call this method even if the stored procedure has a return value and does not want to receive the return value.

 

2. Call the stored procedure "one or more input parameters"

 

An input parameter (5 of which is the parameter value, which is separated by spaces after the stored procedure name ):

Database Connection 1. Execute SQL ("Exec dbproc_p1 5 ")

Two input parameters (values 3 and 6 are separated by commas ):

Database Connection 1. Execute SQL ("Exec dbproc_p2 3, 6 ")

 

 

3. Call the stored procedure of "recordset"

 

The last SQL statement of the stored procedure is a SELECT statement. Generally, A recordset is returned to the caller. In easy language, you can use the "record set" Control in the database to receive the record set through database operations. The specific code is as follows:

The core code is the line with pale yellow background highlighted (record set 1. after this line of code is successfully executed, record set 1 contains the recordset content returned by the stored procedure. You can use a simple loop statement to traverse all records. In actual use, check whether the "record set 1. Open" call is successful, which is omitted for simplicity.

In the more complex cases below, the same code is also used, and only the lines with pale yellow background and bright background have changed.

 

4. Call the stored procedure "recordset with one or more input parameters" 

 

The code is roughly the same as the previous one. It only adjusts the Writing Method of the SQL statement that calls the stored procedure in the middle line. It is involved in the previous step and does not need to be repeated:

Record set 1. Open ("Exec dbproc_p1 5", # SQL statement,) <br/> record set 1. Open ("Exec dbproc_p2 3, 6", # SQL statement ,)

 

5. Call the stored procedure of "directly returning a value"

 

This type of return value is equivalent to the return value of a simple language subroutine. It is returned by using the return command inside the stored procedure code. To get this return value, it is a little troublesome and you can view the Code:

Record set 1. Open ("declare @ r int; Exec @ r = dbproc_r; select @ r", # SQL statement ,)

Here, liigo uses three SQL statements (specifically, the transact-SQL statement), separated by semicolons. The first "declare @ r int" defines a variable of the int type @ R, and the second "Exec @ r = dbproc_r" calls the stored procedure and assigns its return value to the variable @ r, article 3: "select @ r" generates a record set with only one record and one field. After the code is successfully executed, read the first field in record 1 to obtain the returned value of the stored procedure. For the complete code, see the preceding figure. In actual application, you should pay attention to the matching of the return value type of stored procedures. It doesn't matter if you change @ r to @ ABC, but the @ symbol must be retained (the prefix of the variable or parameter ).

What if the stored procedure still has input parameters? Directly append the parameter value to the name of the stored procedure, as shown in record set 1. open ("declare @ r int; Exec @ r = xproc_r_p2 3, 6; select @ r", # SQL statement ,).

 

6. Call the stored procedure of "return value through output parameter"

 

In a stored procedure, an output parameter is both an input parameter and an output parameter, and its value can be rewritten within the stored procedure. It is probably equivalent to the "Reference" parameter of the easy-language subroutine. The call code is as follows:

Record set 1. Open ("declare @ P int; Exec dbproc_o @ P output; select @ P", # SQL statement ,)

The value of the output parameter is still obtained using a variable @ P (note that the type must be matched), and then a record set of a single record and single field is generated, after successful execution, you can use record set 1 to read the field value. In the SQL statement "Exec xproc_o @ P output" that calls the stored procedure, output indicates that this parameter can receive the return value.

 

7. Call the stored procedure "both return values and output parameters exist"

 

This is a comprehensive use of the previous two cases. You can use multiple variables to solve the problem. The code is like: record set 1. open ("declare @ r int; declare @ P1 int; declare @ P2 int; Exec @ r = xproc_r_p3 100, @ P1 output, @ P2 output; select @ r, @ P1, @ P2 ", # SQL statement ,).

 

8. Thinking: Call the stored procedure of "both output parameters (or return values) and record set"

 

I (liigo) has no solution for the moment.

 

Appendix: Stored Procedure Code for testing:

 

Create proc dbproc <br/> as <br/> begin <br/> insert into atable (F1, F3) values (0, 0) <br/> select * From atable <br/> end <br/> go <br/> Create proc dbproc_p1 <br/> @ maxf1 int <br/> as <br/>/> select * From atable where F1 <= @ maxf1 <br/> go <br/> Create proc dbproc_p2 <br/> @ minf1 int, <br/> @ maxf1 int <br/> as <br/> select * From atable where F1 between @ minf1 and @ maxf1 <br/> go <br/> Create proc dbproc_r <br/> as <br/> return 123 <br/> go <br/> Create proc dbproc_o <br/> @ op int output <br/> as <br/> set @ op = 123 <br/> go

 

The full text is complete.