What is the difference between user-defined function and stored procedures in SQL server 2000?
-
Dear all,
I would like to know,
What is the difference between user-defined function and stored procedures in SQL server 2000?
I just know one of the difference about them is :EXEC "strore procedures" and SELECT from "user-defined function", otherwise I don't know their difference.Could anyone clairfy it for me, please ?Lots of thanks -
The difference lies in the concept.User defined function can be used in a query like the example below shows.I got the example from SQL BOL
*************************
User-defined functions take zero or more input parameters, and return a single value. Some user-defined functions return a single, scalar data value, such as an int, char, or decimal value.
For example, this statement creates a simple function that returns a decimal:
CREATE FUNCTION CubicVolume-- Input dimensions in centimeters. (@CubeLength decimal(4,1), @CubeWidth decimal(4,1), @CubeHeight decimal(4,1) )RETURNS decimal(12,3) -- Cubic Centimeters.ASBEGIN RETURN ( @CubeLength * @CubeWidth * @CubeHeight )ENDThis function can then be used anywhere an integer expression is allowed, such as in a computed column for a 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) ) )****************************the best reference I can give you is the SQL BOL, look up user-defined functions, overview, that should answer you -
Check out the presentation by Andy Novick at http://www.novicksoftware.com/Presentations/UserDefinedFunctions/User-Defined-Functions-presentation.htm
He also wrote a very good book about UDFs.
-
That's a _very_ broad question!
You might want to read up both terms in BOL as a starter and come back with question that will certainly arise from reading BOL.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Stored Procs (in my opinion) are good for updating large sets of data or maintaining Business Logic while Saving / Deleting Records. I find them very inefficient for returning data only because the Syntax does not follow the standard for "Selecting" data. Even if I was using Stored Procs for selecting data, I'd still be using UDFs for the Hard to do functions so that my T-SQL code was "encapsulated" for easy re-use. So there it is, UDFs provide re-use.
Using User Defined Functions are much more flexible for retrieving data that is normally hard to get without numerous Statements. UDFs can be used for Inline, Scaler-Valued, or Table-Valued Functions. For example, lets take the age old classic "Employees" table that has a "ManagerID" Column which actually points to another "EmployeeID" within the same table. Now, you boss wants you to print a report of everyone assigned to him regardless of what Department they are in. Also, it could be that there are other supervisors under you boss too...now what? Well, good luck with a Stored Proc, however, UDF's make this easy (the example code for this Function is in MSDN):
- Select * From Employees
- Where EmployeeID IN(Select EmployeeID From dbo.udf_GetEmployeeList(@ManagerID))
We can even make it more exact:
- Select * From Employees
- Where EmployeeID IN(Select EmployeeID From dbo.udf_GetEmployeeList(@ManagerID))
- And LastName >= 'B%'
Now, let's say you develop a Function for "Proper-Case". This function, when passed in a Varchar value, will return that value as a Proper-Cased String.
- Select FirstName, dbo.udf_ProperCase(FirstName) From Employees
- or
- Update Employees Set FirstName = dbo.udf_ProperCase(FirstName)
There you have it nice and simple, now complicated Stored Proc "EXEC" commands.
-
It's worth noting however, that using UDFs limit your application's ability to scale. If you will never have more than a couple hundred rows in your tables, it's probably OK to use UDFs, but if you think about the way that UDFs actually work is that they are executed for EVERY ROW returned. This is bad for any medium-large databases. A faster way is to return a set-based query and either join to it (derived table) or use in a subquery. Both documented in SQL Server Books Online.
-
It's been mentioned quite a few times, that UDF's are executed for each and every row. Here's the proof of concept:
USE northwind
GO
CREATE VIEW foolview
AS
SELECT
GETDATE() AS Jetzt
GO
CREATE FUNCTION fool_me()
RETURNS DATETIME
AS
BEGIN
RETURN (
SELECT
Jetzt
FROM
foolview
 
END
GO
CREATE function you_dont_fool_me(@Jetzt datetime)
RETURNS DATETIME
AS
BEGIN
RETURN @Jetzt
END
GO
DECLARE @Jetzt datetime
SET @Jetzt = GETDATE()
--Test 1 viele Zeilen
SELECT DISTINCT
dbo.fool_me()
FROM
[Order Details] AS od
INNER JOIN
Orders AS o
ON
o.OrderId = od.OrderID
--Test2 eine Zeile
SELECT DISTINCT
dbo.you_dont_fool_me(@Jetzt)
FROM
[Order Details] AS od
INNER JOIN
Orders AS o
ON
o.OrderId = od.OrderID
GO
DROP FUNCTION fool_me
DROP FUNCTION you_dont_fool_me
DROP VIEW foolview
------------------------------------------------------
2004-08-31 09:23:39.337
2004-08-31 09:23:39.347
2004-08-31 09:23:39.267
2004-08-31 09:23:39.277
2004-08-31 09:23:39.307
2004-08-31 09:23:39.357
2004-08-31 09:23:39.297
2004-08-31 09:23:39.307
2004-08-31 09:23:39.327
2004-08-31 09:23:39.277
2004-08-31 09:23:39.347
2004-08-31 09:23:39.357
2004-08-31 09:23:39.317
2004-08-31 09:23:39.327
2004-08-31 09:23:39.287
2004-08-31 09:23:39.297
2004-08-31 09:23:39.367
(17 row(s) affected)
------------------------------------------------------
2004-08-31 09:23:39.237
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Did I ever mention that I really hate this autoreplacement with this annoying smilies
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes there is the alot of difference in the stored procedure and the USER DEFINED PROCEDURE ,it will be better if u check this link
- Manish
-
So the major drawback is that they are executed for every row? Why wouldn't I want a Scaler Function to execute for every row
? I don't normally use Functions in data that I retrieve from the database anyway. I use them to "clean" or perform some function while data is being updated / inserted, so that when it is retrieved the next time, there are no worries.However, can someone verify for me that a Table-Valued Function executes for every single Row? I can't see how that's even possible
. MAYBE if I was Joining on it or something, but what about the "IN" Clause in a Nested SQL Statement?I'm not trying to argue, I just need some Links or something to read that states this please
-
tymberwyld, You are correct and the rest are also correct just you are both talking about different things. Table-value functions are executed once and the join is performed once, but SCALAR functions which for some unknown reason to me seem to be Very popular are really executed on a row by row basis and even if sometimes you come up with an ugly looking code for a select list you would find an striking performance difference if you try to use an scalar UDF
HTH
* Noel -
Great! Thanks for the Reply! Personally, I always try everything in my power (sometimes hours and hours of work) to get everything I need from a Select Statement. Always my last resorts are Functions / Stored Procs.
-
Always my last resorts are .../ Stored Procs
Interesting typo
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply