Differences between functions and stored procedures in a database

read a lot of explanations on the Internet version, feel this write the best. differences between functions and stored procedures in a database

There is no difference in nature. Just a function like this: You can only return a variable's limit. and stored procedures can return multiple. Functions can be embedded in SQL and can be invoked in select, and stored procedures do not work. The essence of execution is the same.

Function restrictions are more, such as the inability to use temporary tables, only table variables. There are some functions that are not available and so on. and stored procedures have fewer restrictions.

Since I am now basically a DBA, I usually look at some books on the database. But I've always had a bad grasp of the difference between stored procedures and functions. I've always thought that stored procedures can be implemented as well as functions. Recently, just as the university teacher gave us a sql-server course, my doubts on this question finally slowly untie. By the way, I saw some information on the internet this evening and found the following analysis more reasonable:

1. Generally speaking, the function of the stored procedure realizes to be more complex, and function's realization function pertinence is relatively strong.

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

3. A stored procedure is typically executed as a separate part, and a function can be invoked as part of a query statement, because the function can return a Table object, so it can be located in the query statement after the FROM keyword.

4. When the stored procedure and function are executed, SQL Manager will go to procedure cache to fetch the corresponding query statement, if the procedure cache does not have the corresponding query statements, SQL Manager will compile stored procedures and functions.

The procedure cache saves the execution plan (execution plans) and executes the execution scheme in the procedure cache after it is compiled, after which SQL Server will follow each execution The actual situation of plan is to consider whether to save the execution in the cache, the standard one is the frequency with which it may be used, and then the cost of generating the plan, which is the time to compile. Plan saved in the cache will not have to be compiled the next time it executes.

Specific differences between stored procedures and user-defined functions

First look at the definition:

Stored Procedures

Stored procedures make it much easier to manage the database and to display information about the database and its users. Stored procedures are precompiled collections of SQL statements and optional control flow statements, stored as a name and processed as a unit. Stored procedures are stored in a database that can be executed by an application through a single call, and allows the user to declare variables, conditional execution, and other powerful programming capabilities.

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, which has the following advantages:

You can execute a series of SQL statements in a single stored procedure.

You can reference other stored procedures from within your own stored procedures, which simplifies a series of complex statements.

Stored procedures are compiled on the server when they are created, so it executes faster than a single SQL statement.

User-defined Functions

A function is a subroutine consisting of one or more Transact-SQL statements that can be used to encapsulate code for reuse. Microsoft? SQL Server? 2000 does not limit users to built-in functions defined as part of Transact-SQL languages, but rather allows users to create their own user-defined functions.

User-defined functions can be created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and drop function statements. Each fully valid user-defined function name (database_name.owner_name.function_name) must be unique.

You must be granted the CREATE FUNCTION permission to create, modify, or drop user-defined functions. A user who is not the owner must give the user the appropriate permissions for the function before using it in a Transact-SQL statement. To create or change a table that references a user-defined function in a CHECK constraint, DEFAULT clause, or computed column definition, you must also have the REFERENCES permission for the function.

In a function, the Transact-SQL error of the next statement that causes the deletion of a statement and continues in a pattern such as a trigger or stored procedure. In a function, the above error causes the execution of the function to stop. The next action causes the statement that wakes to invoke the function to stop.

Types of user-defined functions

SQL Server 2000 supports three types of user-defined functions:

Scalar functions

Inline table-valued functions

Multi-statement table-valued function

A user-defined function takes 0 or more input parameters and returns a scalar value or table. A function can have up to 1024 input parameters. When a parameter of a function has a default value, it must be called to specify the default defaults keyword in order to get the default value. This behavior differs from the parameter that contains the default value in the stored procedure, and omitting the function in those stored procedures also means omitting the default value. The user-defined function does not support output parameters.

Scalar functions return a single data value for the type defined in the RETURNS clause. You can use all scalar data types, including bigint and sql_variant. Timestamp data types, user-defined data types, and non-scalar types (such as table or cursor) are not supported. At the BEGIN ... The function body defined in the end block contains the Transact-SQL statement family that returns the value. The return type can be any data type other than text, ntext, image, cursor, and timestamp.

Table-valued function returns a table. For inline table-valued functions, there is no function body; The table is the result set of a single SELECT statement. For a multiple-statement table-valued function, in the BEGIN ... The function body defined in the end block contains Transact-SQL statements that generate rows and insert rows into the table that will be returned. For more information about inline table-valued functions, see inline user-defined functions. For more information about table-valued functions, see returning a user-defined function that returns a table data type.

BEGIN ... The statement in the end block cannot have any side effects. function side effects are any permanent changes to the state of a resource that has a scope outside the function, such as a modification of a database table. The only change that a statement in a function can make is a change to a local object (such as a local cursor or a local variable) on a function. Actions that cannot be performed in a function include modifying a database table, manipulating a local cursor that is not on a function, sending an e-mail message, attempting to modify a directory, and generating a result set that is returned to a user.

Valid statement types in a function include:

DECLARE statement, which can be used to define data variables and cursors that are local to the function.

Assign a value to a function local object, such as using SET to assign values to scalar and table local variables.

A cursor operation that references a local cursor declared, opened, closed, and disposed in a function. You are not allowed to return data to the client using a FETCH statement. Only the FETCH statement is allowed to assign a value to a local variable with an into clause.

Control flow statements.

SELECT statement, which contains a selection list with an expression that assigns a value to a function's local variable.

INSERT, UPDATE, and DELETE statements, which modify the local table variables of the function.

EXECUTE statement that calls the extended stored procedure.

The number of actual executions of a function specified in a query may differ between execution plans generated by the optimizer. Example is a function invoked by a subquery in a WHERE clause. The number of executions of subqueries and their functions varies depending on the access path chosen by the optimizer.

Built-in functions that return different data to each call are not allowed in user-defined functions. The following built-in functions are not allowed in user-defined functions:

@ @CONNECTIONS @ @PACK_SENT GETDATE

@ @CPU_BUSY @ @PACKET_ERRORS getutcdate

@ @IDLE @ @TIMETICKS NEWID

@ @IO_BUSY @ @TOTAL_ERRORS RAND

@ @MAX_CONNECTIONS @ @TOTAL_READ textptr

@ @PACK_RECEIVED @ @TOTAL_WRITE

Schema-bound functions

The CREATE function supports the SCHEMABINDING clause, which binds a function to the schema of any object that it references, such as tables, views, and other user-defined functions. Attempting to execute ALTER or DROP on any object referenced by a schema-bound function will fail.

The following conditions must be met to specify Schemabinding in the CREATE FUNCTION:

All views and user-defined functions referenced by the function must be bound to the schema.

All objects referenced by the function must be in the same database as the function. You must use a partial or two-part name to refer to the object.

You must have REFERENCES permissions on all objects referenced in the function (tables, views, and user-defined functions).

You can use the ALTER FUNCTION to remove schema bindings. The ALTER function statement will redefine functions by specifying functions without the WITH schemabinding.

Calling user-defined functions

When you call a scalar user-defined function, you must provide a name that consists of at least two parts:

SELECT *, Myuser.myscalarfunction ()

From MyTable

You can invoke a table-valued function using a partially formed name:

SELECT *

From Mytablefunction ()

However, when you call a SQL Server built-in function that returns a table, you must prefix:: Add to the function name:

SELECT * FROM:: Fn_helpcollations ()

Scalar functions, including computed columns and CHECK constraint definitions, can be referenced anywhere in the same data type expression that the function allowed in Transact-SQL statements is returned. For example, the following statement creates a simple function that returns decimal:

CREATE FUNCTION Cubicvolume

--Input dimensions in centimeters

(@CubeLength decimal (4,1), @CubeWidth decimal (4,1),

@CubeHeight decimal (4,1))

RETURNS Decimal (12,3)--Cubic centimeters.

As

BEGIN

Return (@CubeLength * @CubeWidth * @CubeHeight)

End

You can then use this function anywhere that allows an integral expression, such as in a computed column of a table:

CREATE TABLE Bricks

(

brickpartnmbr int PRIMARY KEY,

Brickcolor nchar (20),

Brickheight Decimal (4,1),

Bricklength Decimal (4,1),

Brickwidth Decimal (4,1),

Brickvolume as

(

Dbo. Cubicvolume (Brickheight,

Bricklength, Brickwidth)

)

)

Dbo. Cubicvolume is an example of a user-defined function that returns a scalar value. The RETURNS clause defines the scalar data type of the value returned by the function. BEGIN ... The end block contains one or more Transact-SQL statements that execute the function. Each return statement in the function must have a parameter that returns a data value that has the data type specified in the RETURNS clause (or a data type that can be implicitly converted to a specified type in RETURNS). The value of the return parameter is the value returned by the function.