Differenze tra Stored Procedure e user function

martedì 25 luglio 2006 - 23.47

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

us01739 Profilo | Expert

641 messaggi | Data Invio: mar 25 lug 2006 - 23:47

Ciao a tutti,
ho un dubio che mi attanaglia, e secondo me qui trovo sicuramente la risposta...
Con SQL Server, oltre al fatto non marginale che una funzione posso richiamarla in una select nella clausola from oppure per effettuare "calcoli on line" (perdonate l'inesattezza ma è per rendere l'idea), che differenza c'è tra Stored Procedure e User Function? E' possibile fare paragoni a subroutine e function in vb?

Grazie 1000
---------------------------------------------------
Roberto Gelmini - Microsoft Certified Professional
www.robertogelmini.com
---------------------------------------------------

alx_81 Profilo | Guru

8.814 messaggi | Data Invio: mer 26 lug 2006 - 00:41

Ciao!
Cercando nella rete ho trovato, tempo fa, queste definizioni..
Le effettive differenze scritte sono solo per sql 2000..
Ci sono nuove funzionalità in sql server 2005 per le udf e le sp..
e ti invito a guardare i books on line..

UDF http://msdn2.microsoft.com/en-us/library/ms191007.aspx
SP http://msdn2.microsoft.com/en-us/library/ms191436.aspx

Stored procedure
--------------------------
A stored procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

User-defined function
-----------------------------
A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

In SQL Server 2000
User defined functions have 3 main categories

1. Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries
2. Inline function - can contain a single SELECT statement.
3. Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.

Differences between Stored procedure and User defined functions

1. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
2. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
3. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
4. Of course there will be Syntax differences
Alx81 =)

http://blogs.dotnethell.it/suxstellino

Torna su

Stanze Forum

Elenco Threads

Partecipa anche tu! Registrati!

Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !