General discussion

Locked

Difference between User-Defined functin and stored procedures in MSSQL

By klng_brian ·
What is the difference between user-defined function and stored procedures in SQL server 2000?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Difference between Stored Procedure and Functions

by avnish In reply to Difference between User-D ...

1. Stored Procedure :supports deffered name resoultion Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws errorFunction wont support deffered name resolution.
2. Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values(SQL Server).
3. Stored Procedure is pre compiled exuction plan where as functions are not.
4. Stored Procedure retuns more than one value at a time while funtion returns only one value at a time.
5. We can call the functions in sql statements (select max(sal) from emp). where as sp is not so
6. Function do not return the images,text whereas sp returns all.
7. Function and sp both can return the values. But function returns 1 value only.
procedure can return multiple values(max. 1024) we can select the fields from function. in the case of procdure we cannot select the fields.
8. Functions are used for computations where as procedures can be used for performing business logic
9. Functions MUST return a value, procedures need not be.
10. You can have DML(insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query..eg: suppose, if u have a function that is updating a table.. you can't call that function in any sql query.
- select myFunction(field) from sometable;
will throw error.
11. Function parameters are always IN, no OUT is possible

Collapse -

Stored procedure Vs function

by maqsoodshaik In reply to Difference between User-D ...

Functions are designed to send their output to a query or T-SQL statement. For example, User Defined Functions (UDFs) can run an executable file from SQL SELECT or an action query, while Stored Procedures (SPROC) use EXECUTE or EXEC to run.

Stored procedures are designed to return its output to the application. A UDF returns table variables, while a SPROC can't return a table variable although it can create a table. Another significant difference between them is that UDFs can't change the server environment or your operating system environment, while a SPROC can. Operationally, when T-SQL encounters an error the function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code (provided you've included error handling support). You'll also find that although a SPROC can be used in an XML FOR clause, a UDF cannot be.

If you have an operation such as a query with a FROM clause that requires a rowset be drawn from a table or set of tables, then a function will be your appropriate choice. However, when you want to use that same rowset in your application the better choice would be a stored procedure.

Collapse -

Diff between SP and UDF

by ka.rajesh In reply to Difference between User-D ...

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
Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries
Inline function - can contain a single SELECT statement.
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
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Of course there will be Syntax differences and here is a sample of that
Stored procedure

Code: SQL

CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
RETURN

User defined functions

Code: SQL

CREATE FUNCTION dbo.Function1
(
/*
@parameter1 datatype = default value,
@parameter2 datatype
*/
)
RETURNS /* datatype */
AS
BEGIN
/* sql statement ... */
RETURN /* value */
END

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums