Differnce between Stored procedure and user defined functions?

Categories >> Software >> Microsoft Related >> ADO.NET
Suggest New Category

Question Posted / swapna

17 Answers
28116 Views
Microsoft, Symphony, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Differnce between Stored procedure and user defined functions?..

Answer / chandra shaker

Stored Procedure returns morethan one value where as
Function returns only one value.

Is This Answer Correct ?

9 Yes

6 No

Differnce between Stored procedure and user defined functions?..

Answer / ranjanimanuel

1.UDF can't use a nondeterministic functions such as Getdate
(),RAND() where as StoreProcedure cn use such a functions.
2.UDF must return a value or a single resultset . Store
Procedure can return a value or even multiple resultsets,
but doesn't have to
3. UDF can be used in anywhere sql statements like
where/having/Select sections where as store procedure
cannot be.
4.UDFs that return table can be treated as another rowset.
This can be used in JOINS with other table

Is This Answer Correct ?

1 Yes

0 No

Differnce between Stored procedure and user defined functions?..

Answer / gowdhami

In UDF if any error comes it will stop the execution
but in SP it will skip the step move to next line

Is This Answer Correct ?

1 Yes

0 No

Differnce between Stored procedure and user defined functions?..

Answer / sharad kulat

Store Procedure Is first tme complie and make the
Execution plan & next time proc just call plan .so its get
execute fast.In case Function compile every request.

function & Store procedure return not only single but also
number of value .

function return number of value useing return type table.

You Can use Function with select ,case Statement ,same
thing can not do through Store procedure.

Is This Answer Correct ?

3 Yes

3 No

Differnce between Stored procedure and user defined functions?..

Answer / lalit raghuvanshi

20 main differences between Stored procedures and Functions
in Sql Server
http://www.webcodeexpert.com/2013/04/difference-between-stored-procedures.html

Is This Answer Correct ?

0 Yes

0 No

Differnce between Stored procedure and user defined functions?..

Answer / santosh kumar

Sl. No. User Defined function Stored Procedure
1 Function must return a value. Stored procedure may or not return values.
2 Will allow only Select statement, it will not allow us to use DML statements. Can have select statements as well as DML statements such as insert, update, delete
etc
3 It will allow only input parameters, doesn’t support output parameters. It can have both input and output parameters.
4 It will not allow us to use try-catch blocks. For exception handling we can use try catch blocks.
5 Transactions are not allowed within functions. Can use transactions within Stored procefures.
6 We can use only table variables, it will not allow using temporary tables. Can use both table variables aswell as temporary table in it.
7 Stored procedures can’t be called from function. Stored Procedures can call functions.
8 Functions can be called from select statement. Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec
statement can be used to call/execute stored procedure.
9 UDF can be used in join clause as a result set. Procedures can’t be used in Join clause

Is This Answer Correct ?

0 Yes

0 No

Differnce between Stored procedure and user defined functions?..

Answer / asha

no difference

Is This Answer Correct ?

4 Yes

89 No