Differences between SQL functions and stored procedures

Essentially, there is no difference. Only functions are as follows: only one variable can be returned. The stored procedure can return multiple objects. Functions can be embedded in SQL and called in select, but stored procedures cannot. The essence of execution is the same. There are many function restrictions. For example, you can only use table variables instead of temporary tables. some functions are unavailable. stored Procedures have fewer restrictions. in general, the functions implemented by stored procedures need to be more complex, and the functions implemented by functions are more targeted. 2. For stored procedures, parameters can be returned, while functions can only return values or table objects. 3. A stored procedure is generally executed as an independent part, and a function can be called as a part of a query statement. Because a function can return a table object, therefore, it can be located behind the from keyword in the query statement. 4. when stored procedures and functions are executed, SQL manager will go to procedure cache to obtain the corresponding query statement. If no query statement exists in procedure cache, SQL manager compiles stored procedures and functions. The execution plan is saved in procedure cache. After compilation, execute the execution plan in procedure cache, then, SQL Server will consider whether to save the plan in the cache based on the actual situation of each execution plan. The criterion is the possible frequency of this execution plan; the second is the cost of generating this plan, that is, the Compilation Time. The Plan saved in the cache does not need to be compiled during the next execution. The specific differences between stored procedures and user-defined functions can be first defined: stored procedures can make it easier to manage databases and display information about databases and their 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 a database and can be executed by an application through a single call. They also allow users to declare variables, conditional executions, 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. You can use stored procedures for any purpose of using SQL statements. It has 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. A user-defined function is a subroutine composed of one or more statements. It can be used to encapsulate code for reuse. Microsoft? SQL Server? 2000 does not restrict users to built-in functions defined as part of the transact-SQL language, but allows users to create their own 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. In functions, differentiated processing leads to the deletion of statements and the subsequent statements in modes such as triggers or stored procedures are in a Transact-SQL error. In the function, the above error will cause the function to stop being executed. Next, this operation stops the wake-up statement that calls the function. The User-Defined Function Type SQL Server 2000 supports three user-defined functions: scalar functions embedded table valued functions multi-statement table valued functions user-defined functions use zero or more input parameters and return scalar values or tables. A function can have up to 1024 input parameters. When a function parameter has a default value, you must specify the default keyword when calling the function to obtain the default value. This behavior is different from parameters that contain default values in stored procedures. Omitting this function in these stored procedures also means omitting the default value. User-defined functions do not support output parameters. The scalar function returns a single data value of the type defined in the returns clause. All scalar data types can be used, including bigint and SQL _variant. The timestamp data type, user-defined data type, and non-standard data type (such as table or cursor) are not supported ). 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 table value function returns table. For nested table valued functions, there is no function subject; the table is the result set of a single SELECT statement. For multi-statement table-valued functions, the body of the function defined in the begin... end block contains TRANSACT-SQL statements that generate rows and insert rows into the table that will be returned. For more information about embedded Table value functions, see embedded user-defined functions. For more information about table value functions, see user-defined functions that return table data types. The statements in the begin... end block cannot have any side effects. Function side effects refer to any permanent changes to the state of resources with a function out-of-function scope (such as changes to database tables. The only change that can be made by a statement in a function is a change to a local object (such as a local cursor or local variable) on the function. Operations that cannot be performed in the function include modifying the database table, performing operations on partial cursors not on the function, sending an email, and attempting to modify the directory, and generate the result set returned to the user. The valid statement types in the function include: declare statement, which can be used to define local data variables and cursors of the function. Assign values to local objects of a function. For example, assign values to scalar and table local variables using set. A cursor operation that references a local cursor declared, opened, closed, and released in a function. The fetch statement cannot be used to return data to the client. Only the fetch statement can be used to assign values to local variables through the into clause. 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 actual number of executions of the function specified in the query may vary among the execution plans generated by the optimizer. In this example, the subquery in the where clause wakes up the called function. The number of subqueries and their function executions varies depending on the access path selected by the optimizer. Built-in functions that return different data for 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 @ brief getutcdate @ idle @ timeticks newid @ io_busy @ total_errors Rand @ max_connections @ total_read textptr @ pack_received @@ the total_write architecture binding function create function supports the schemabinding clause, the latter can bind a function to the architecture of any objects referenced by it (such as tables, views, and other user-defined functions. An attempt to execute alter or drop on any object referenced by the schema binding function fails. Schemabinding can be specified in create function only if the following conditions are met: all views and user-defined functions referenced by this function must be bound to the architecture. All objects referenced by this function must be in the same database as the function. The object must be referenced by a name consisting of one or two parts. You must have the references permission on all objects referenced in the function (tables, views, and user-defined functions. You can use alter function to delete schema bindings. The alter function statement will redefine the function without the specified function with schemabinding. When a scalar user-defined function is called, the user-defined function must be named select *, myuser. myscalarfunction () from mytable can call the table value function using the name of a part: Select * From mytablefunction (). However, when calling the SQL Server built-in function of the returned table, you must Prefix:: add to function name: Select * From: fn_helpcollations () You can reference a scalar function at any location where the same data type expressions returned by functions allowed in the transact-SQL statement are located, includes 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 (4, 1) returns decimal (12, 3) -- cubic centimeters. asbegin return (@ cubelength * @ cubewidth * @ cubeheight) end can be used in any place where integer expressions are allowed (such as in the calculation column of the 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. 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.