Differnce between Stored procedure and user defined functions?
Answer Posted / 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 |
Post New Answer View All Answers
What are the data providers used in ado.net
Describe connection object in ado.net
What is the full form of ado.net?
Describe datareader object of ado.net with example.
What is ambient transaction?
What is ado and rdo?
What is the difference in an abstract class and an interface?
Which object is used to add relationship between two Datatables?
Where is adodb dll located?
describe the dataset object in ado.net.
If a table contains 20000 records . In a page at each time 100 records to be displayed what are the steps you will take to improve performance? Will you use dataset or datareader?
What is the difference between dataset and datatable?
Can datareader hold data from multiple tables?
What are the parameters that control most of connection pooling behaviors?
What is dataset and datatable in ado.net?