Talking about the stored procedure in the database

1. In general, the functions implemented by stored procedures must be more complex, while the functions implemented by functions are more targeted.

2. For stored procedures, parameters (output) can be returned, while functions can only return values or table objects.

3. A stored procedure is generally executed as an independent part, and a function can be called as a part of a query statement. Because a function can return a table object, therefore, it can be located behind the FROM keyword in the query statement.

 

1. Faster execution-stored procedure statements in the database are compiled

2. Modular programming is allowed-reuse of similar methods

3. Improve System Security-prevent SQL Injection

4. Reduce network traffic-as long as the name of the stored procedure is transmitted

 

System stored procedures generally start with sp, and user-defined stored procedures generally start with usp

 

Create proc stored procedure name

@ Parameter 1 Data Type [= default value] [output],

@ Parameter 2 Data Type [= default value] [output],

...

As

SQL statement

 

Define the stored procedure:

Create proc usp_StudentByGenderAge

@ Gender nvarchar (10) [= 'male'],

@ Age int [= 30]

As

Select * from MyStudent where FGender = @ gender and FAge = @ age

 

Execute the stored procedure:

　　

Exec usp_StudentByGenderAge

　　

Exec usp_StudentByGenderAge 'femal', 50

Exec usp_StudentByGenderAge @ age = 50, @ gender = 'femal'

 

Modify the Stored Procedure

Alter proc usp_StudentByGenderAge

@ Gender nvarchar (10) [= 'male'],

@ Age int [= 30],

　　

@ RecorderCount int output

As

Select * from MyStudent where FGender = @ gender and FAge = @ age

Set @ recorderCount = (select count (*) from MyStudent where FGender = @ gender and FAge = @ age)

 

 

Call (remember the syntax here !), After the stored procedure is executed, the preceding query is completed, and the number of results obtained from the query is assigned to the @ count variable. (@ Count is passed as a parameter to usp_StudentByGenderAge. After the stored procedure is executed, return the obtained number to @ count)

Declare @ count int

Exec usp_StudentByGenderAge @ recorderCount = @ count output

Print @ count

 

Create proc usp_page

@ Page int, --- How many records are displayed on a page

@ Number int, --- the page number of data selected

As

Begin

Select * from

-- The content in the parentheses is specially arranged sequence numbers.

(

Select ROW_NUMBER () over (order by (Fid) as number

From MyStudent

) As t

Where t. number >=( @ number-1) * @ page + 1 and t. number <= @ number * @ page

End

2. ADO. NET code for implementing the paging effect:

   button1_Click( sender, EventArgs e) 　 { 　　 connStr = ; 　　 (SqlConnection conn =  SqlConnection(connStr)) 　　{ 　　　　 conn.Open(); 　　　　 　　　　 usp = ; 　　　　 (SqlCommand cmd =  SqlCommand(usp, conn)) 　　　　{ 　　　　　　 　　　　　　cmd.CommandType = CommandType.StoredProcedure;　　　　　　　 　　　　　　cmd.Parameters.AddWithValue(, textBox1.Text.Trim()); 　　　　　　 　　　　　　cmd.Parameters.AddWithValue(, textBox2.Text.Trim()); 　　　　　　 　　　　　　List<Person> p =  List<Person>(); 　　　　　　 (SqlDataReader reader = cmd.ExecuteReader()) 　　　　　　{ 　　　　　　　　 (reader.HasRows) 　　　　　　　　{ 　　　　　　　　　　 (reader.Read()) 　　　　　　　　　　{ 　　　　　　　　　　　　Person p1 =  Person(); 　　　　　　　　　　　　p1.FName = reader.GetString(); 　　　　　　　　　　　　p1.FAge = reader.GetInt32(); 　　　　　　　　　　　　p1.FGender = reader.GetString(); p1.FMath = reader.GetInt32(); 　　　　　　　　　　　　p1.FEnglish = reader.GetInt32(); 　　　　　　　　　　　　p.Add(p1); 　　　　　　　　　　} 　　　　　　　　} 　　　　　　} 　　　　　　dataGridView1.DataSource = p; 　　　　} 　　} }