SQL Server의 함수와 저장 프로 시저

SQL Server의 함수와 저장 프로 시저

---

나는 꽤 오랫동안 함수와 저장 프로 시저를 배워 왔지만 왜 언제 함수 나 저장 프로 시저를 사용해야하는지 모르겠습니다. 그들은 나에게 똑같이 보입니다. 아마도 내가 그것에 대해 좀 초보자이기 때문입니다.

누군가가 이유를 말해 줄 수 있습니까?

---

함수는 계산 된 값이며 SQL Server에 대한 영구적 인 환경 변경을 수행 할 수 없습니다 (즉, INSERT 또는 UPDATE 문이 허용되지 않음).

함수가 스칼라 값을 리턴하는 경우 SQL 문에서 인라인으로 사용되거나 결과 세트를 리턴하는 경우 결합 될 수 있습니다.

답변을 요약 한 댓글에서 주목할만한 점입니다. @Sean K Anderson에게 감사드립니다.

함수는 값을 반환해야하며 매개 변수 (인수)로 수신하는 데이터를 변경할 수 없다는 점에서 컴퓨터 과학 정의를 따릅니다. 함수는 아무것도 변경할 수 없으며 최소한 하나의 매개 변수가 있어야하며 값을 반환해야합니다. 저장된 procs는 매개 변수를 가질 필요가 없으며 데이터베이스 개체를 변경할 수 있으며 값을 반환 할 필요가 없습니다.

---

SP와 UDF의 차이점은 다음과 같습니다.

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

---

함수와 저장 프로시 저는 별도의 용도로 사용됩니다. 최상의 비유는 아니지만 함수는 모든 프로그래밍 언어에서 사용하는 다른 함수로 문자 그대로 볼 수 있지만 저장된 procs는 개별 프로그램이나 배치 스크립트와 비슷합니다.

함수에는 일반적으로 출력과 선택적으로 입력이 있습니다. 그런 다음 출력을 다른 함수 (DATEDIFF, LEN 등의 SQL Server 기본 제공)에 대한 입력으로 사용하거나 SQL 쿼리에 대한 조건 자로 사용할 수 있습니다 (예 : SELECT a, b, dbo.MyFunction(c) FROM table또는 SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

저장된 procs는 트랜잭션에서 SQL 쿼리를 함께 바인딩하고 외부 세계와 인터페이스하는 데 사용됩니다. ADO.NET 등과 같은 프레임 워크는 함수를 직접 호출 할 수 없지만 저장된 proc을 직접 호출 할 수 있습니다.

하지만 함수에는 숨겨진 위험이 있습니다. 오용 될 수 있으며 다소 불쾌한 성능 문제를 일으킬 수 있습니다. 다음 쿼리를 고려하십시오.

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

MyFunction은 다음과 같이 선언됩니다.

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

여기서 일어나는 일은 MyTable 테이블의 모든 행에 대해 MyFunction 함수가 호출된다는 것입니다. MyTable에 1000 개의 행이있는 경우 데이터베이스에 대한 또 다른 1000 개의 임시 쿼리입니다. 마찬가지로 함수가 열 사양에 지정 될 때 호출되면 SELECT에서 반환 된 각 행에 대해 함수가 호출됩니다.

따라서 함수 작성에주의해야합니다. 함수의 테이블에서 SELECT를 수행하는 경우 상위 저장된 proc의 JOIN 또는 다른 SQL 구문 (예 : CASE ... WHEN ... ELSE ... 종료).

---

저장 프로 시저와 사용자 정의 함수의 차이점 :

• 저장 프로시 저는 Select 문에서 사용할 수 없습니다.
• 저장 프로시 저는 지연된 이름 확인을 지원합니다.
• 저장 프로시 저는 일반적으로 비즈니스 논리를 수행하는 데 사용됩니다.
• 저장 프로시 저는 모든 데이터 유형을 반환 할 수 있습니다.
• 저장 프로시 저는 사용자 정의 함수보다 많은 수의 입력 매개 변수를 허용 할 수 있습니다. 저장 프로 시저에는 최대 21,000 개의 입력 매개 변수가있을 수 있습니다.
• 저장 프로시 저는 동적 SQL을 실행할 수 있습니다.
• 저장 프로시 저는 오류 처리를 지원합니다.
• 비 결정적 함수는 저장 프로 시저에서 사용할 수 있습니다.

---

• 사용자 정의 함수는 Select 문에서 사용할 수 있습니다.
• 사용자 정의 함수는 지연된 이름 확인을 지원하지 않습니다.
• 사용자 정의 함수는 일반적으로 계산에 사용됩니다.
• 사용자 정의 함수는 값을 반환해야합니다.
• 사용자 정의 함수는 이미지를 반환 할 수 없습니다.
• 사용자 정의 함수는 저장 프로 시저보다 적은 수의 입력 매개 변수를 허용합니다. UDF는 최대 1,023 개의 입력 매개 변수를 가질 수 있습니다.
• 사용자 정의 함수에서는 임시 테이블을 사용할 수 없습니다.
• 사용자 정의 함수는 동적 SQL을 실행할 수 없습니다.
• 사용자 정의 함수는 오류 처리를 지원하지 않습니다. RAISEERROROR @@ERROR는 UDF에서 허용되지 않습니다.
• 비 결정적 함수는 UDF에서 사용할 수 없습니다. 예를 들어, GETDATE()UDF에서 사용할 수 없습니다.

---

다른 SQL 문에서 사용할 값을 계산하고 반환하려면 사용자 정의 함수를 작성하십시오. 대신 복잡한 SQL 문 집합을 그룹화하는 경우 저장 프로 시저를 작성하십시오. 결국, 이것들은 매우 다른 두 가지 사용 사례입니다!

---

              STORE PROCEDURE                 FUNCTION (USER DEFINED FUNCTION)    
 * Procedure can return 0, single or   | * Function can return only single value   
   multiple values.                    |
                                       |
 * Procedure can have input, output    | * Function  can have only input 
   parameters.                         |   parameters.         
                                       |
 * Procedure cannot be called from     | * Functions can be called from 
   function.                           |   procedure.
                                       |
 * Procedure allows select as well as  | * Function allows only select statement 
   DML statement in it.                |   in it.
                                       |
 * Exception can be handled by         | * Try-catch block cannot be used in a 
   try-catch block in a procedure.     |   function.
                                       |
 * We can go for transaction management| * We can't go for transaction 
   in procedure.                       |   management in function.
                                       |
 * Procedure cannot be utilized in a   | * Function can be embedded in a select 
   select statement                    |   statement.
                                       |
 * Procedure can affect the state      | * Function can not affect the state 
   of database means it can perform    |   of database means it can not    
   CRUD operation on database.         |   perform CRUD operation on 
                                       |   database. 
                                       |
 * Procedure can use temporary tables. | * Function can not use 
                                       |   temporary tables. 
                                       |
 * Procedure can alter the server      | * Function can not alter the  
   environment parameters.             |   environment parameters.
                                       |   
 * Procedure can use when we want      | * Function can use when we want
   instead is to group a possibly-     |   to compute and return a value
   complex set of SQL statements.      |   for use in other SQL 
                                       |   statements.

---

기본적인 차이점

함수는 값을 반환해야하지만 저장 프로 시저에서는 선택 사항입니다 (프로시 저는 0 또는 n 값을 반환 할 수 있음).

함수는 입력 매개 변수 만 가질 수있는 반면 프로시 저는 입력 / 출력 매개 변수를 가질 수 있습니다.

함수는 하나의 입력 매개 변수를 사용하며 필수이지만 저장 프로시 저는 o에서 n 개의 입력 매개 변수를 사용할 수 있습니다.

Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.

We can go for Transaction Management in Procedure whereas we can't go in Function.

source

---

a User Defined Function is an important tool available to a sql server programmer. You can use it inline in a SQL statement like so

SELECT a, lookupValue(b), c FROM customers 

where lookupValue will be an UDF. This kind of functionality is not possible when using a stored procedure. At the same time you cannot do certain things inside a UDF. The basic thing to remember here is that UDF's:

• cannot create permanent changes
• cannot change data

a stored procedure can do those things.

For me the inline usage of a UDF is the most important usage of a UDF.

---

Stored Procedures are used as scripts. They run series of commands for you and you can schedule them to run at certain times.

Functions are used as methods. You pass it something and it returns a result. Should be small and fast - does it on the fly.

---

Stored procedure:

• Is like a miniature program in SQL Server.
• Can be as simple as a select statement, or as complex as a long script that adds, deletes, updates, and/or reads data from multiple tables in a database.
• (Can implement loops and cursors, which both allow you to work with smaller results or row by row operations on data.)
• Should be called using EXEC or EXECUTE statement.
• Returns table variables, but we can't use OUT parameter.
• Supports transactions.

Function:

• Can not be used to update, delete, or add records to the database.
• Simply returns a single value or a table value.

• Can only be used to select records. However, it can be called very easily from within standard SQL, such as:

  SELECT dbo.functionname('Parameter1')
  

  or

  SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
  

• For simple reusable select operations, functions can simplify code. Just be wary of using JOIN clauses in your functions. If your function has a JOIN clause and you call it from another select statement that returns multiple results, that function call will JOIN those tables together for each line returned in the result set. So though they can be helpful in simplifying some logic, they can also be a performance bottleneck if they're not used properly.

• Returns the values using OUT parameter.
• Does not support transactions.

---

To decide on when to use what the following points might help-

1. Stored procedures can't return a table variable where as function can do that.

2. You can use stored procedures to alter the server environment parameters where as using functions you can't.

cheers

---

SQL Server functions, like cursors, are meant to be used as your last weapon! They do have performance issues and therefore using a table-valued function should be avoided as much as possible. Talking about performance is talking about a table with more than 1,000,000 records hosted on a server on a middle-class hardware; otherwise you don't need to worry about the performance hit caused by the functions.

1. Never use a function to return a result-set to an external code (like ADO.Net)
2. Use views/stored procs combination as much as possible. you can recover from future grow-performance issues using the suggestions DTA (Database Tuning Adviser) would give you (like indexed views and statistics) --sometimes!

for further reference see: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

---

Start with functions that return a single value. The nice thing is you can put frequently used code into a function and return them as a column in a result set.

Then, you might use a function for a parameterized list of cities. dbo.GetCitiesIn("NY") That returns a table that can be used as a join.

It's a way of organizing code. Knowing when something is reusable and when it is a waste of time is something only gained through trial and error and experience.

Also, functions are a good idea in SQL Server. They are faster and can be quite powerful. Inline and direct selects. Careful not to overuse.

---

• It is mandatory for Function to return a value while it is not for stored procedure.
• Select statements only accepted in UDF while DML statements not required.
• Stored procedure accepts any statements as well as DML statements.
• UDF only allows inputs and not outputs.
• Stored procedure allows for both inputs and outputs.
• Catch blocks cannot be used in UDF but can be used in stored procedure.
• No transactions allowed in functions in UDF but in stored procedure they are allowed.
• Only table variables can be used in UDF and not temporary tables.
• Stored procedure allows for both table variables and temporary tables.
• UDF does not allow stored procedures to be called from functions while stored procedures allow calling of functions.
• UDF is used in join clause while stored procedures cannot be used in join clause.
• Stored procedure will always allow for return to zero. UDF, on the contrary, has values that must come - back to a predetermined point.

---

Here's a practical reason to prefer functions over stored procedures. If you have a stored procedure that needs the results of another stored procedure, you have to use an insert-exec statement. This means that you have to create a temp table and use an exec statement to insert the results of the stored procedure into the temp table. It's messy. One problem with this is that insert-execs cannot be nested.

If you're stuck with stored procedures that call other stored procedures, you may run into this. If the nested stored procedure simply returns a dataset, it can be replaced with a table-valued function and you'll no longer get this error.

(this is yet another reason we should keep business logic out of the database)

---

• Functions can be used in a select statement where as procedures cannot.

• Stored procedure takes both input and output parameters but Functions takes only input parameters.

• Functions cannot return values of type text, ntext, image & timestamps where as procedures can.

• Functions can be used as user defined datatypes in create table but procedures cannot.

***Eg:-create table <tablename>(name varchar(10),salary getsal(name))

Here getsal is a user defined function which returns a salary type, when table is created no storage is allotted for salary type, and getsal function is also not executed, But when we are fetching some values from this table, getsal function get’s executed and the return Type is returned as the result set.

---

I realize this is a very old question, but I don't see one crucial aspect mentioned in any of the answers: inlining into query plan.

Functions can be...

1. Scalar:

   CREATE FUNCTION ... RETURNS scalar_type AS BEGIN ... END

2. Multi-statement table-valued:

   CREATE FUNCTION ... RETURNS @r TABLE(...) AS BEGIN ... END

3. Inline table-valued:

   CREATE FUNCTION ... RETURNS TABLE AS RETURN SELECT ...

The third kind (inline table-valued) are treated by the query optimizer essentially as (parametrized) views, which means that referencing the function from your query is similar to copy-pasting the function's SQL body (without actually copy-pasting), leading to the following benefits:

• The query planner can optimize the inline function's execution just as it would any other sub-query (e.g. eliminate unused columns, push predicates down, pick different JOIN strategies etc.).
• Combining several inline function doesn't require materializing the result from the first one before feeding it to the next.

The above can lead to potentially significant performance savings, especially when combining multiple levels of functions.

---

NOTE: Looks like SQL Server 2019 will introduce some form of scalar function inlining as well.

---

User Defined Function.

1. Function must return a value.
2. Will allow only Select statements, it will not allow us to use DML statements.
3. It will allow only input parameters, doesn't support output parameters.
4. It will not allow us to use try-catch blocks.
5. Transactions are not allowed within functions.
6. We can use only table variables, it will not allow using temporary tables.
7. Stored Procedures can't be called from a function.
8. Functions can be called from a select statement.
9. A UDF can be used in join clause as a result set.

Stored Procedure

1. Stored Procedure may or not return values.
2. Can have select statements as well as DML statements such as insert, update, delete and so on
3. It can have both input and output parameters.
4. For exception handling we can use try catch blocks.
5. Can use transactions within Stored Procedures.
6. Can use both table variables as well as temporary table in it.
7. Stored Procedures can call functions.
8. Procedures can't be called from Select/Where/Having and so on statements. Execute/Exec statement can be used to call/execute Stored Procedure.
9. Procedures can't be used in Join clause

---

In SQL Server, functions and stored procedure are two different types of entities.

Function: In SQL Server database, the functions are used to perform some actions and the action returns a result immediately. Functions are two types:

1. System defined

2. User defined

Stored Procedures: In SQL Server, the stored procedures are stored in server and it can be return zero, single and multiple values. Stored Procedures are two types:

1. System Stored Procedures
2. User Defined Procedures

참고URL : https://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server