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

Source: Internet
Author: User

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!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.