Stored Procedures and functions

The difference between a stored procedure and a function:

1. In general, the function of the stored procedure implementation is a bit more complex, and the function implementation of the function is relatively strong.

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

3. The stored procedure is typically performed as a separate part, and the function can be called as part of a query statement, since the function can return a Table object, so it can be located after the FROM keyword in the query statement.

Second, the advantages of stored procedures:

1. Execute faster – Stored procedure statements stored in the database are compiled

2. Allow modular programming – reuse of similar methods

3. Improve system security – Prevent SQL injection

4. Reduce network liquidity – as long as the name of the transfer stored procedure

System stored procedures typically start with an SP, and user-defined stored procedures typically begin with USP

Third, the definition of the stored procedure syntax, "[" the contents of the expression can be optional

create proc Stored procedure name

@ Parameter 1 data type [= default] [output],

@ Parameter 2 data type [= default] [output],

...

As

SQL statements

Iv. An example of simplicity

To define a stored procedure:

Create proc Usp_studentbygenderage

@gender nvarchar (10) [= ' Male '],

@age int [= 30]

As

SELECT * from mystudent where [email protected] and [email protected]

To execute a stored procedure:

Situation one (call the default parameter):

EXEC usp_studentbygenderage

Situation (invokes the parameter you specify):

exec usp_studentbygenderage ' female ', 50

or specify the variable name exec usp_studentbygenderage @age =50, @gender = ' female '

To modify a stored procedure

ALTER PROC Usp_studentbygenderage

@gender nvarchar (10) [= ' Male '],

@age int [= 30],

--plus output indicates that the parameter is to be assigned and returned in the stored procedure.

@recorderCount int Output

As

SELECT * from mystudent where [email protected] and [email protected]

Set @recorderCount = (select count (*) from mystudent where [email protected] and [email protected])

The purpose of the--output parameter is that the caller needs to pass a variable in, and then perform the assignment for that variable in the stored procedure, and after the execution of the stored procedure is completed, the corresponding result is returned to the passed-in variable. (identical to the out principle in C #)

Call (remember the syntax here!) Because there are other parameters in front of the stored procedure, to write the @recorderCount, after the execution of the stored procedure, rather than the completion of the above query work, at the same time, the result of the query results are assigned to the @count variable. (@count is passed as a parameter to Usp_studentbygenderage, and when the stored procedure is finished, the resulting number of bars is returned to @count)

DECLARE @count int

EXEC usp_studentbygenderage @[email protected] Output

Print @count

V. Using a stored procedure to complete the paging

1. Stored Procedure Code

Create proc Usp_page

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

@number int,---The user has selected the first page of data

As

Begin

SELECT * FROM

--the contents of the parentheses are specially arranged sequence numbers.

(

Select Row_number () over (the Order by (Fid)) as number

From Mystudent

) as T

where t.number>= (@number-1) * @page +1 and T.number<[email protected]* @page

End

2, to achieve the paging effect corresponding to the ADO code:

 1 private void Button1_Click (object sender, EventArgs e) {2 string connstr = @ "Server=.\sqlexpress;database=mydb;int Egrated Security=true "; 3 using (SqlConnection conn = new SqlConnection (CONNSTR)) 4 {5//Open database connection 6 Conn. Open (); 7//object with stored procedure name as command 8 string USP = "Usp_page"; 9 using (SqlCommand cmd = new SqlCommand (USP, conn)) 10 {11///execute is a stored procedure statement (CMD).　　　　　　　　CommandType = CommandType.StoredProcedure; TextBox1.Text refers to how many records are displayed in CMD. Parameters.addwithvalue ("@page", TextBox1.Text.Trim ());//textbox.text means that the user has selected page cmd. Parameters.addwithvalue ("@number", TextBox2.Text.Trim ()); 16//Use List as the data source to implement the list<person> p = new list& Lt Person> (); using (SqlDataReader reader = cmd. ExecuteReader ()) (reader. hasrows) (reader. Read ()), p1 = new Person (), p1. FName = reader. GetString (1); P1. Fage = Reader. GetInt32 (2); P1. Fgender = reader. GetString (3); P1. Fmath = reader. GetInt32 (4); P1. Fenglish = reader. 　　GetInt32 (5); P.add (p1);}33}34}35 datagridview1.datasource = p;36}37 }38}

Stored procedures and functions