Organize the second training materials (SQL Server Stored Procedure and ADO. NET access stored procedure) of the Network Studio after the summer vacation (I)

I suddenly couldn't open my SQL Server 2008 database yesterday. And the day before yesterday also used it to write T-SQL statements. So I am very depressed. After I go to the Internet for a query, I can't solve the problem, so I simply re-installed one side. So the content I sorted out yesterday cannot be updated to my blog in time. I can only continue to sort it out today, then update.

This is mainly about the knowledge of accessing the stored procedure through ADO. NET. So at the beginning, I will give a series of lectures on the stored procedure of SQL Server and review it.

For programmers in the previous section, stored procedures are an important tool that allows developers to process data without the solution architecture. At the same time, because the stored procedure has the encapsulation concept, when the logic rules change, the backend program can be modified without affecting the front-end program design.

Stored Procedures include all the advantages of the view. The view only has the select command, while the Stored Procedure contains almost all T-SQL commands.
Stored Procedures can greatly improve the efficiency of T-SQL command execution.
T-SQL statement execution on the SQL Server Client:

During each execution, you must analyze whether the statement is correct, optimize and compile the statement into an execution plan, and check whether the same execution plan exists in SQL Server.

When a common T-SQL command is repeatedly executed, the same analysis, optimization, and compilation will always take a lot of CPU time.

Instead, the stored procedure is used for analysis, optimization, and compilation only once, and then directly executed according to the execution plan in the SQL Server cache.

Advantage: reduces network bandwidth usage: Because the storage process is executed on the SqlServer client, you do not need to communicate back and forth with the front-end. In contrast, this reduces the bandwidth usage of packets transmitted over the network, for network program execution, the use of stored procedures can reduce the occupation of network bandwidth.

SQL Server Stored Procedure declaration method:

Create proc stored procedure name

As

T-SQL command

SQL Server Stored Procedures include non-parameter stored procedures and parameter stored procedures

1) stored procedures without Parameters

Call Stored Procedure

Exec stored procedure name;

This method is similar to the view, but the view cannot use the order by command. Stored procedures do not have such restrictions.

The example code 1-1 is as follows:

1 -- create a stored procedure without parameters 2 create proc select_activity3 as4 -- the SQL command executed by the Stored Procedure 5 select * from T_Activity;

SQL calls the Stored Procedure

1 -- call the select_activity Stored Procedure 2 exec select_activity

Stored Procedures with Parameters

There are three main types of parameters:

1) input parameter 2) output parameter 3) Return Value

The input parameter must be input before calling this stored procedure. This parameter is only used in the program and will not be returned to the user.

Basic declaration method:

Create proc stored procedure name

@ Input parameter name 1 data type,

@ Input parameter name 2 Data Type

As

T-SQL command

If multiple parameters exist, they must be separated by commas. If the parameter is a string or a date, it must be enclosed in single quotation marks, but not a number.

Note:

Do not write a stored procedure too long, because the stored procedure is executed on the database. If it is too long, the execution efficiency will be affected;

Example 1-2 code:

1 -- create a stored procedure with parameters 2 create proc select_activity1 3 4 -- modify the Stored Procedure Statement 5 -- alter proc select_activity1 6 7 -- declare parameters. Multiple parameters include ", "separator, the parameter is declared after the parameter data type 8 @ id int, 9 @ activityName nvarchar (50) 10 as11 -- select * from T_Activity where id = @ id and activityName = @ activityName; 13 14 -- call the stored procedure with parameters 15 -- if the parameter is a string or date, it must be enclosed in single quotation marks, but not a number. 16 exec select_activity1 5, 'biwu zhaobao' 17 18 -- or 19 exec select_activity120 @ id = 1, 21 @ activityName = 'biwu zhaobaoxin ';

Stored Procedures can contain some simple programming statements

For example, if, begin, end, and other statements

Sample Code 1-3 is as follows:

1 -- when setting a parameter, if you need to assign a value to the set parameter, add "=" after the declared parameter and the set initial value, the initial value set here is null 2 create proc sele_activity 3 @ id int = null, 4 @ activityInfoId int = null 5 as 6 7 -- use the if statement to determine whether the input parameter value is null. if the input parameter value is null, the system returns the frontend error message "Enter Parameters ", and use return to immediately end the stored procedure 8 if @ id is null or @ activityInfoId is null 9 begin10 raiserror ('Enter the parameter ', 16, 1) 11 return12 end13 -- the executed SQL command 14 select * from T_Activity where id = @ id or activityInfoId = @ activityInfoId; 15 16 -- stored procedure without parameter assignment is used, return Error 17 -- error message: "Message 50000, level 16, status 1, process sele_activity, line 7th please input parameter" 18 exec sele_activity; 19 20 -- The call returns the correct result 21 exec sele_activity22 @ id = 2, 23 @ activityInfoId = 8;

Stored Procedure of output parameters

The output parameter is the opposite of the input parameter. The input parameter is only active in the as, and the output parameter is returned to the front-end

Declaration process of output parameters:

Createproc stored procedure name

@ Input parameter name Data Type

@ Output parameter name data type output

As

T-SQL command

When declaring the output parameter variable, it must be declared as output. After the stored procedure is executed, the output parameter is returned to the front-end

Sample Code 1-4 is as follows:

1 -- create the stored procedure of select_MathResult 2 create proc select_MathResult 3 -- declare the output parameter @ activityName and parameter type 4 @ activityName nvarchar (50), 5 -- declare the output parameter, and use output to indicate that this parameter is the output parameter 6 @ result int output 7 8 as 9 -- use a T-SQL statement to return the number of rows of data with the same activityName 10 select @ result = count (*) from T_Activity where activityName = @ activityName11 12 -- when calling a stored procedure, declare the parameters that accept the output parameter @ answer and the type of the parameter 13 declare @ answer int14 exec select_MathResult 'competitive ', @ answer output15 16 select 'result is ', @ answer

This completes the SQL Server Stored Procedure review. In the next article, we will introduce how to use ADO. NET to access the stored procedure. Article: http://www.cnblogs.com/qq731109249/archive/2012/10/07/2714238.html