Talking about the stored procedure in the database and talking about the stored procedure in the database
I. Differences between stored procedures and functions:
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.
Ii. Advantages of stored procedures:
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
3. Define the Stored Procedure syntax. The content in "[" indicates the option.
Create proc stored procedure name
@ Parameter 1 Data Type [= default value] [output],
@ Parameter 2 Data Type [= default value] [output],
...
As
SQL statement
4. A simple example
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:
Situation One (call the default parameter ):
Exec usp_StudentByGenderAge
Situation Two (call the specified parameter ):
Exec usp_StudentByGenderAge 'femal', 50
Or specify the variable name exec usp_StudentByGenderAge @ age = 50, @ gender = 'femal'
Modify the Stored Procedure
Alter proc usp_StudentByGenderAge
@ Gender nvarchar (10) [= 'male'],
@ Age int [= 30],
-- Add output to indicate that this parameter must be assigned and returned during the stored procedure.
@ 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)
-- The purpose of the output parameter is that the caller needs to pass a variable in and assign a value to the variable in the stored procedure. After the stored procedure is completed, return the corresponding execution result to the passed variable. (Same as the out principle in C)
Call (remember the syntax here !) Because there are other parameters before the stored procedure, write @ recorderCount. After the stored procedure is executed, the preceding query is completed, at the same time, 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
5. Use stored procedures to complete Paging
1. Stored Procedure Code
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:
1 private void button#click (object sender, EventArgs e) {2 string connStr = @ "server =. \ sqlexpress; database = MyDB; integrated security = true "; 3 using (SqlConnection conn = new SqlConnection (connStr) 4 {5 // open the database connection 6 conn. open (); 7 // use the stored procedure name as the object for Command processing 8 string usp = "usp_page"; 9 using (SqlCommand cmd = new SqlCommand (usp, conn )) 10 {11 // The Stored Procedure statement 12 cmd is executed. commandType = CommandType. storedProcedure; // textBox1.Text indicates how many records are displayed 13 cmd. parameters. addWithValue ("@ page", textBox1.Text. trim (); 14 // textBox. text refers to the page 15 cmd selected by the user. parameters. addWithValue ("@ number", textBox2.Text. trim (); 16 // use list as the data source to implement 17 lists <Person> p = new List <Person> (); 18 using (SqlDataReader reader = cmd. executeReader () 19 {20 if (reader. hasRows) 21 {22 while (reader. read () 24 {25 Person p1 = new Person (); 26 p1.FName = reader. getString (1); 27 p1.FAge = reader. getInt32 (2); 28 p1.FGender = reader. getString (3); 29 p1.FMath = reader. getInt32 (4); 30 p1.FEnglish = reader. getInt32 (5); 31 p. add (p1); 32} 33} 34} 35 dataGridView1.DataSource = p; 36} 37} 38}