Functions vs. Store Procedures

Question

Post reply

Functions vs. Store Procedures

kim03

SSC Veteran

Points: 243
More actions
October 27, 2004 at 12:12 pm

#59789

What is the different between functions and store procedures?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

A little bit vague this question, methinks.

You might want to read to read both topics in BOL and come back more concrete questions.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

kim03 SSC Veteran Points: 243 More actions · Answer 2

Thanks Frank,

This is an interview questions. How would you answer this in the circumstances mention here?

john-132685 SSCertifiable Points: 6134 More actions · Answer 3

Um, OK, then how's this for an answer:

one starts with an 'F'?

or

One is listed in sysobjects with type 'FN' and the other has a 'P'...

Is this multiple choice?

TriggerMe SSChampion Points: 11752 More actions · Answer 4

Kim03,

Surely you can come up with better DBA Interview questions than that! How bout sussing out the candidate if he or she can develop Functions & Stored Procedures.

Kindest Regards,

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Okay, if it's for an interview, I would say the difference is that you should avoid UDF's whereever you can, while you should use stored procedures whereever you can. Exceptions are UDF's that return a table, which are very similar to views. Other UDF's do a row-by-row processing, and therefore are performance killers on larger tables.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

vidya_pande SSCertifiable Points: 5275 More actions · Answer 6

Following are the key differnces between Stored procedure and UDF.

1.User defined Function returns only one value; It may be the one value of table data type if ur using table valued function.But stored procedure can return more than one values if you use OUTPUT paramers with procedure.

2.You can use UDF(scalar/table valued) in the select statement as a record source. You can not use stored procedure result set as a record source in select statement directly.

3.UDF can return only one result set at a time.While stored procedure can return more than one result sets.

kim03 SSC Veteran Points: 243 More actions · Answer 7

Thank you all for the inputs. I thought that was a bit strange for an interview brain teaser.

Pleasant day to all of you guys!

Kim

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 8

Worth a read for a view from the other side of the fence.......

http://weblogs.asp.net/fbouma/archive/2003/06/16/8731.aspx

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

http://weblogs.asp.net/fbouma/archive/2003/05/14/7008.aspx

Frank: How u doing? Have you read these?

Steve

We need men who can dream of things that never were.

vidya_pande SSCertifiable Points: 5275 More actions · Answer 9

Few More additions to above mentioned differences-

4. DRI permissions are applicable to UDF and not to SP

5. Schema binding can be done with UDF not with SP.

6. You can make call to another SP through SP. Call to SP(other than extened stored procedure) is not allowed in UDF.

7. Non Deterministics functions are allowed in SP not in UDF.

8. UPDATE, INSERT, and DELETE statements modifying database tables other than table variables that are local to the function is not allowed in UDF, It is allowed in SP