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:

 
 

  
  
-- Create a stored procedure without Parameters
  
  
Create proc select_activity
  
  
As
  
  
-- The SQL command executed by the Stored Procedure
  
  
Select * from T_Activity;
 
 

SQL calls the Stored Procedure

 
 

  
  
-- Call the select_activity Stored Procedure
  
  
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 is as follows:

 
 

  
  
-- Create a stored procedure with Parameters
  
  
Create proc select_activity1
  
  
-- Modify stored procedure statements
  
  
-- Alter proc select_activity1
  
  
-- Declare a parameter. Multiple parameters are separated by commas (,). The parameter is followed by the Data Type of this parameter.
  
  
@ Id int,
  
  
@ ActivityName nvarchar (50)
  
  
As
  
  
-- The SQL command executed by the Stored Procedure
  
  
Select * from T_Activity where id = @ id and activityName = @ activityName;
  
  
-- Call a stored procedure with Parameters
  
  
-- If the parameter is a string or date, it must be enclosed in single quotation marks, and the number is not used.
  
  
Exec select_activity1 5, 'biwu zhaobao'
  
  
-- Or
  
  
Exec select_activity1
  
  
@ Id = 1,
  
  
@ ActivityName = 'competitive tactics new ';
 
 

Stored Procedures can contain some simple programming statements

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

Sample Code 1-3 is as follows:

 
 

  
  
-- When setting a parameter, if you need to assign a value to the set parameter, add "=" next to the declared parameter and the set initial value. The set initial value here is null.
  
  
Create proc sele_activity
  
  
@ Id int = null,
  
  
@ ActivityInfoId int = null
  
  
As
  
  
-- Use the if statement to determine whether the input parameter value is null. if it is null, the system returns the frontend error message "enter a parameter" and uses return to immediately end the stored procedure.
  
  
If @ id isnullor @ activityInfoId isnull
  
  
Begin
  
  
Raiserror ('Enter the parameter)
  
  
Return
  
  
End
  
  
-- Executed SQL command
  
  
Select * from T_Activity where id = @ id or activityInfoId = @ activityInfoId;
  
  
-- An error is returned if no value is assigned to the stored procedure.
  
  
-- Error message: "message 50000, level 16, status 1, process sele_activity, 7th rows please input parameters"
  
  
Exec sele_activity;
  
  
-- The call returns the correct result.
  
  
Exec sele_activity
  
  
@ Id = 2,
  
  
@ 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:

 
 

  
  
-- Create the stored procedure of select_MathResult
  
  
Create proc select_MathResult
  
  
-- Declare the output parameter @ activityName and parameter type
  
  
@ ActivityName nvarchar (50 ),
  
  
-- Declare the output parameter and use output to indicate that the parameter is an output parameter.
  
  
@ Result upload utput
  
  
As
  
  
-- Use a T-SQL statement to return rows of data with the same activityName
  
  
Select @ result = count (*) from T_Activity where activityName = @ activityName
  
  
-- Call the stored procedure. when calling the stored procedure, declare the parameter @ answer for receiving the output parameter and the parameter type.
  
  
Declare @ answer int
  
  
Exec select_MathResult 'competitive match ', @ answer output
  
  
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://yisuowushinian.blog.51cto.com/4241271/1016527

Original works can be reprinted. During reprinting, you must mark the original source, author information, and this statement in hyperlink form. Otherwise, legal liability will be held.

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131228/1FJ2E60-0.png "/>

This article is from the blog of "Hadar column", please be sure to keep this source http://yisuowushinian.blog.51cto.com/4241271/1016524