diffrance between stored procedure & function in sql server?
Answers were Sorted based on User's Feedback
Answer / dev
Difference between function and stored proc:
========================================================
Stored Procedure :supports deffered name resoultion Example
while writing a stored procedure
that uses table named tabl1 and tabl2 etc..but actually not
exists in database is allowed
only in during creation but runtime throws errorFunction
wont support deffered name
resolution.
2. Stored procedure returns always integer value by default
zero. where as function return
type could be scalar or table or table values(SQL Server).
3. Stored Procedure is pre compiled exuction plan where as
functions are not.
4. Stored Procedure retuns more than one value at a time
while funtion returns only one
value at a time.
5. We can call the functions in sql statements (select max
(sal) from emp). where as sp is
not so
6. Function do not return the images,text whereas sp
returns all.
7. Function and sp both can return the values. But function
returns 1 value only.
procedure can return multiple values(max. 1024) we can
select the fields from function. in
the case of procdure we cannot select the fields.
8. Functions are used for computations where as procedures
can be used for performing
business logic
9. Functions MUST return a value, procedures need not be.
10. You can have DML(insert, update, delete) statements in
a function. But, you cannot call
such a function in a SQL query..eg: suppose, if u have a
function that is updating a table..
you can't call that function in any sql query.
- select myFunction(field) from sometable;
will throw error.
11. Function parameters are always IN, no OUT is possible
Is This Answer Correct ? | 5 Yes | 0 No |
Answer / pramod prakash
Function can be used in an expression but we cann't use
procedure in an expression
Is This Answer Correct ? | 3 Yes | 0 No |
How do you declare static variable?
Explain the difference between server control and html control.
What is query string?
What is ViewState and How it is managed,Its Advantages/Benefits?
How many types of validation are there?
How can we identify that the Page is Post Back?
A web service can only be written in .net? State whether true or false.
What is the difference between mvc and asp.net? : Asp.Net MVC
Explain server control extensibility with reference to asp.net 2.0 ?
What object is used to encapsulate a rowset? a) DataSet b) DataAdapter c) DataRowSet d) DataTable
Can we have a web application running without web.config file?
Does web services support data reader like pom project?