Difference between SP and function

Hi All,

It may be sound weird. I want to find the difference between SQL Stored procedure and functions. I knew a couple one is for function, parameter is must where as in SP its not, second is function would return a value whereas procedure wont.

Is there anythign else?That was it! You are on the right track :)|||>> second is function would return a value whereas procedure wont.

not necessarily. even a stored proc can return values ( see OUTPUT PArameters).

Function can return only ONE value where as a stored proc can return multiple values.
performance wise there isnt any diff.

I remember having googled about this once and did come across a couple of articles that xtensively described the differences..so I would say google and you can find more definitive answers.

hth|||I guess "parameter is must where as in SP " he ment for OUTPUT param!|||one use of a function is to use it to return a table object that can be used in another sql statement|||Hi

Thanks you all for your answers. But in the recent inteview which i attended they asked for one more difference between these two apart from those i specified earlier.|||I'm sure you could Google and find all of the information you need.

The main thing about UDFs is that they need to be deterministic -- that is, the same input parameters will always return the same result. So therefore you cannot, for example, directly use GETDATE() in your UDF. Another biggie is that you cannot use either @.@.ERROR or RAISERROR. And another biggie is that dynamic SQL cannot be executed.

I've gotten burnt by all of the above, and others. Some have workarounds, some do not.

Terri