Differences between stored procedures and user-defined functions

 

Stored Procedure
Stored procedures make it easier to manage databases and display information about databases and users. A stored procedure is a pre-compiled set of SQL statements and optional control flow statements. It is stored in a name and processed as a unit. Stored procedures are stored in the database and can be used by applicationsProgramExecute through one call, and allow the user to declare variables, conditional execution, and other powerful programming functions.

Stored Procedures include program streams, logic, and queries to databases. They can accept parameters, output parameters, return one or more result sets, and return values.

Stored procedures can be used for any purpose of using SQL statements. They have the following advantages:

You can execute a series of SQL statements in a single stored procedure.
You can reference other stored procedures from your stored procedures, which simplifies a series of complex statements.
The stored procedure is compiled on the server when it is created, so it runs faster than a single SQL statement.
I will not talk about the usage here.

 

User-Defined Functions
You can use the create function statement to create, use the alter function statement to modify, and use the drop function statement to remove user-defined functions. 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 remove user-defined functions. Before using a function in a Transact-SQL statement, a user who is not the owner must first grant the appropriate permissions to the function. To create or modify a table that references a user-defined function in the check constraint, default clause, or calculation column definition, you must have the references permission of the function.

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

Scalar functions
Embedded Table value functions
Multi-statement Table value functions
The scalar function returns a single data value of the type defined in the returns clause. The function body defined in the begin... end block contains the series of transact-SQL statements that return this value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

The valid statement types in the function include:

Declare statement, which can be used to define local data variables and cursors of a function.
Assign values to local objects of a function. For example, assign values to scalar and table local variables using set.

Control Flow statement.

Select statement, which contains a selection list with an expression. The expression in the list grants the value to the local variable of the function.
Insert, update, and delete statements. These statements modify the local table variables of the function.
Execute statement, which calls the extended stored procedure.

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

Call User-Defined Functions
When calling a scalar user-defined function, the name must contain at least two parts:

Select *, myuser. myscalarfunction () from mytable
You can call the table value function with a part name:

Select * From mytablefunction ()
However, when calling the SQL Server built-in function of the returned table, you must add the prefix: To the function name:

Select * From: fn_helpcollations ()
Scalar functions can be referenced anywhere in the expression of the same data type returned by functions allowed in the transact-SQL statement, including the calculation column and check constraint definitions. For example, the following statement creates a simple function that returns decimal:

Create Function cubicvolume -- input dimensions in centimeters (@ cubelength decimal (), @ cubewidth decimal (), @ cubeheight decimal () returns decimal () -- cubic centimeters. asbegin return (@ cubelength * @ cubewidth * @ cubeheight) End
You can then use this function wherever an integer expression is allowed (such as in the calculation column of a table:

Create Table bricks (brickmbr MBR 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 scalar values. The returns clause defines the scalar Data Type of the value returned by the function. The begin... end block contains one or more Transact-SQL statements that execute the function. Each return statement in this function must have a parameter to return the data type specified in the returns clause (or implicitly converted to the data type specified in returns). The return parameter value is the value returned by the function.