Stored Procedure vs User Define Function

[RicoCali]

For the life of me I can not figure out what the difference is between a stored-procedure and a User Define Function.

Can someone explain to me what the difference is. PLEASE DON'T EXPLAIN WHAT THEY ARE. I already know what they are. I just don't know the difference.

 

[vongrunt]

The most important: SP can modify "external" data (permanent tables), UDF cannot.

UDF can be called from multi-record statement (SELECT/INSERT/UPDATE/DELETE), though this implicitely turns query logic into row-based and performance may suffer. SP calls are limited to one per statement.

 

[NoCoolHandle]

I am not sure if von managed to express one of the major benifits of udf's clearly. (although i think his second sentence points this way)

They can be used in a query as a column source, joined with tables, and used just like tables or views (with or without parameters). The data inside can be real or contrived from other datasources.

You can't use a stored proc to give you a row in a where clause, you can a udf.

You can't join a storedprocs resultset to any other (without doing some fancy footwork - insert into temp object etc..)

And yes.. They allow us to extend tsql by creating our own functions. These can be ovedone and some of that functionality should be saved for the client....
Depending of then number of rows being returned....

My 1c

 

[emcevoy]

hi

I am wondering if any of you have any samples of using a User defined function (UFD) in a stored procedure to pass the function a datetime.

I am using crystal reports which cannot see UFD's but can pass a stored proc a variable.

If anyone has any samples I would be extremely greatfull!

thanks

Eileen McEvoy
Authorized Crystal Engineer
Crystal Reports Consultant and Trainer

http://www.crystalconsulting.ca

emcevoy@crystalconsulting.ca