Differences in stored procedures for custom functions

Source: Internet
Author: User
Tags scalar

Stored procedures:

Stored procedures can make it much easier to manage the database and to display information about the database and its users. A stored procedure is a precompiled collection of SQL statements and optional control-flow statements, stored as a single name and processed as a unit. Stored procedures are stored in the database and can be executed by the application through a call, and allow the user to declare variables, conditional execution, and other powerful programming features.
Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values.

You can use stored procedures for any purpose that uses SQL statements, and it has the following advantages:

1. You can execute a series of SQL statements in a single stored procedure.
2. You can reference other stored procedures from within your own stored procedure, which simplifies a series of complex statements.

3. The stored procedure is compiled on the server when it is created, so it executes faster than a single SQL statement.

User-defined functions:

Microsoft SQL Server 2000 allows you to create user-defined functions. As with any function, a user-defined function is a routine that can return a value. Depending on the type of value returned, each user-defined function can be divided into the following three categories:

1. Returns a function that updates the data table: if the user-defined function contains a single SELECT statement and the statement can be updated, the table formatting results returned by the function can also be updated.

2. Returns a function that does not update the data table: If a user-defined function contains more than one SELECT statement, or contains a SELECT statement that is not updatable, the result of the table format returned by the function is not updatable.

3. A function that returns a scalar value: A user-defined function can return a scalar value.

Stored Procedures

1. Powerful, less restrictive
2. Cannot directly reference the return value
3. Returning a Recordset with a SELECT statement

Custom functions

1. Many limitations, many statements can not be used, a lot of functions can not be implemented
2. Return values can be referenced directly
3. Returning a recordset with a table variable

Differences in stored procedures for custom functions

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.