Stored Procedure-[Niuke news publishing system], News Publishing System

Stored Procedure-[Niuke news publishing system], News Publishing System

1. What is a stored procedure? Definition:

Stored Procedures (Stored Procedure) are used in large databases to store commonly used or complex tasks in advance with SQL statements and a specified name, after a compilation, you do not need to compile the program again. You can specify the name of the stored procedure and provide parameters for execution.

Category:

System stored procedure: starting with sp _

Extended Stored Procedure: starting with xp _

Custom stored procedure:

Recommended naming Syntax: [proc] [MainTableName] By [FieldName (optional)] [Action]

The prefix must have proc, the object accessed by the stored procedure, the optional field name is the condition clause, and the final verb is the execution task. Example: procClientRateSelect

Note: It is not recommended to name sp _ as the prefix, because in this way, SQL Server will first find the system storage process, resulting in slow operation.

Ii. Differences between General SQL statements and stored procedures

1. improve performance. The stored procedure only needs to be compiled once. Generally, the SQL statement can be compiled once, improving the database execution speed.

2. encapsulation feature, which can encapsulate Complex Database Operations (add, delete, modify, and query) with stored procedures and combine them with transactions provided by the database.

3. Reusable, reducing the workload of database developers.

4. High security. Only the specified user has the right to use stored procedures.

Iii. Stored Procedure instances

1. SQLHelper calls the Stored Procedure

<span style="font-size:18px;"> public DataTable test(string procName)        {            DataTable dt = new DataTable();            cmd = new SqlCommand(procName, GetConn());            cmd.CommandType  = CommandType.StoredProcedure;            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))            {                dt.Load(sdr);            }            return dt;        }</span>

It is mainly to assign the procName value in the command to the stored procedure name, and assign cmd. CommandType to CommandType. StoredProcedure.

2. Writing stored procedures:

<Span style = "font-size: 18px; "> SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ========================== ============== -- Author: wang Mei -- Create date: February 6, 2015 23:11:35 -- Description: retrieve the latest 10 News (category, news title) -- ===================================================== ====== create procedure procNewsSelectNewNews -- Add the parameters for the stored procedure hereASBEGIN --- select the top 10 select top 10 n. id, n. title, n. createTime, c. [name] from news n ---- category Table id = news table id, inner join category c on n. caId = c. id order by n. createTime desc --- sort ENDGOexec procNewsSelectNewNews in descending order </span>

Iv. Postscript

The stored procedure is not frequently used, and its senses are not very three-dimensional. If anything is wrong, I hope to correct it. Please contact us!