The difference between a stored procedure and a custom function

1:

1) The stored procedure is powerful and can perform a series of database operations, including modifying tables, or you can create a stored procedure that runs 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) A stored procedure that returns an int status result or a recordset.

Custom functions, you can return table variables.

It's easy to get confused about this. Stored procedures, you can use this form to return more than N results:

CREATE PROCEDURE SP1
As
Begin
Select Name, FID_FK from Table_1
print ' 111 '
Select TestName, FID from table_2
End
CREATE PROCEDURE SP1 as begin select name, FID_FK from table_1 print ' 111 ' Select TestName, fid from table_2 end

[Table 1]

This result can only be used in two ways: INSERT into Table_3 (name, FID_FK) EXEC SP1, or exec SP1.

However, for a custom function, it must specify a data @t that is defined as a return value of type table, and insert it explicitly into the table @t in the code, or simply return the table by making an object variable that returns a table type without specifying return. That

Create function fn1 ()
Returns table
As
Return select FID, testname from table_2
Create function fn1 () returns table as return select FID, testname from table_2

[Table 2]

Or

Create function fn1 ()
Returns @v table
(FID int primary key NOT NULL,
TestName nchar (10))
As
Begin
Insert INTO @v select Fid,testname from table_2
End
Create function Fn1 () Returns @v table (FID int primary key NOT NULL, testname nchar (TEN)) as begin insert INTO @v select F Id,testname from Table_2 End
[Table 3]

In this respect, the difference between a stored procedure and a custom function is most visible: The former is a collection of functions, can return an int value, or returns a result set of a query, but only as a byproduct of a series of functions, and the latter is created to return a value. The use of custom functions is described in detail in the appendix.

4) stored procedure whose return value cannot be directly referenced, but must be used as EXEC SP1 or INSERT into table.

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.

"Appendix" finally Add.

I ft, broken csdn, the whole format will not, do not know what to do. But it's still the same.

Generally speaking, custom functions have these three uses:

1, as above [table 2], returns a In-line table value;

2, as above [table 3], returns a multi statement table value;

3, just return an arbitrary variable, such as:

Create Function Cubicvolume
(@CubeLength decimal (4,1), @CubeWidth decimal (4,1), @CubeHeight decimal (4,1))
Returns Decimal (12,3)
As
Begin
Return (@CubeLength * @CubeWidth * @CubeHeight)
End
Original address: http://blog.csdn.net/sandyzhs/article/details/3049678#

2.

What is the difference between stored procedures, functions, and Views in SQL?

Stored procedures are pre-written and compiled SQL programs
Pre-written code snippets for functions, with system functions and custom functions
A view is a pre-built query statement that is used just like a table.

Stored procedures need to be executed separately, functions can be called anywhere, and views are an intuitive way of behaving ~

Original address: http://zhidao.baidu.com/question/130756796.html

3.

The difference between a stored procedure and a custom function