Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SanjaySutar
Starting Member
18 Posts |
 Posted - 2008-04-09 : 07:00:35
|
| What is the difference between stored procedure and functions ?thanx in advance |
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-04-09 : 07:36:03
|
| 1. Functions return only a scalar(single) values. Note that this single value could be a table variable.2. You cannot use stored procedure in a select statement likeselect <spname > but this is possible with UDF.3. You can not return a table type variable with stored procedure but this is possible with user defined functions in sql server.4. Stored procedure may or may not return a value but a function has to return a value.5. UDFs can accept a smaller number of parameters than stored procedures. UDFs can have up to 1024 parameters, whereas stored procedures support up to 2100 parameters. This is a relatively minor limitation because most routines require a much smaller number of parameters. 6. UDFs cannot call stored procedures (except extended procedures), whereas stored procedures can call other procedures7. You can not insert record to physical table from within UDF. You need to use Stored proc instead.Prakash.PThe secret to creativity is knowing how to hide your sources! |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-09 : 07:41:48
|
1. Functions can return resultset as well as a scalar value.2. SELECT * FROM OPENROWSET(LinkedServer, 'EXEC Mysp')3. Not with SQL Server 2005 but with SQL Server 2008 you can.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
kiruthika
Yak Posting Veteran
67 Posts |
Posted - 2008-04-09 : 07:45:56
|
| Hi, Function can be executed using the select clause while sp's can not be. Function dosen't return output parameters while sp's return output parameter If there is an error in the UDF its stop executing.But in sp's it just ignores the error and moves to the next statement. UDF must return value where as sp's can not.kiruthikahttp://www.ictned.eu |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-09 : 07:47:58
|
SP's can return values, both RETURN values and parameter values with the OUTPUT/OUT optional keyword.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-04-09 : 07:59:07
|
OP has tendancy not even to reply back, wow, what a great virtue!  Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-04-09 : 09:21:39
|
| http://hemantgirisgoswami.blogspot.com/2007/02/difference-between-stored-procedure-and.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
sankalp
Starting Member
1 Post |
Posted - 2010-06-28 : 00:02:46
|
| - funcation always return a value but SP may or may not return a value n SP return 1024 value- We can call a function inside the stored procedure but cannot call SP inside the function.- We can not perform DDL,DML statement inside the function but we can do it in SP |
|
|
|
|
|
|
what's the difference ???