The difference between a stored procedure and a custom function

The difference between a stored procedure and a custom function

Difference between Procedure and User Defined Function

First look at the concept of stored procedures and custom functions

First, what is a stored procedure?

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.

Ii. What is a user-defined function?

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. Functions that return updatable data tables

If a 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. Functions that return non-updatable data tables

If a user-defined function contains more than one SELECT statement, or contains a SELECT statement that is not updatable, the table formatting results returned by the function are not updatable.

3. Functions that return scalar values

A user-defined function can return a scalar value.

Differences between stored procedures (user-defined stored procedures) and custom functions (user-defined functions):

1. Stored procedures, powerful, can perform a series of database operations, including modifying tables, or you can create stored procedures that run automatically when SQL Server starts.

Custom functions, user-defined functions cannot be used to perform a set of actions that modify the state of a global database.

2. Stored procedures, you can use the non-deterministic function.

Custom functions that do not allow the use of undefined functions in the body of a user-defined function.

3. Stored procedures, you can return the recordset.

Custom functions, you can return table variables.

4. A stored procedure whose return value cannot be directly referenced.

A custom function whose return value can be directly referenced.

5. Stored procedure, executed with EXECUTE statement.

A custom function that is called in a query statement.

The difference between a stored procedure and a custom function