What are the differences between stored procedure and
functions in SQL Server 2000?
Answers were Sorted based on User's Feedback
Answer / bed singh
a. A FUNCTION is always returns a value using the return
statement. A PROCEDURE may return one or more values
through parameters or may not return at all.
b. Functions are normally used for computations where as
procedures are normally used for executing business logic.
c. A Function returns 1 value only. Procedure can return
multiple values (max 1024).
d. Stored procedure returns always integer value by default
zero. Whereas function returns type could be scalar or
table or table values
e. Stored procedure is precompiled execution plan where as
functions are not.
f. A function can call directly by SQL statement like
select func_name from dual while procedure cannot.
g.Stored procedure has the security and reduces the network
traffic and also we can call stored procedure in any no. of
applications at a time.
h. A Function can be used in the SQL Queries while a
procedure cannot be used in SQL queries .that cause a major
difference b/w function and procedures.
Is This Answer Correct ? | 165 Yes | 8 No |
Answer / pavan pareta
1) functions are used for computations where as procedures
can be used for performing business logic
2) functions MUST return a value, procedures need not be.
3) 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.
4) function parameters are always IN, no OUT is possible
Is This Answer Correct ? | 145 Yes | 43 No |
Answer / vampire007
1. Functions can be used inline with a select statement
while sprocs can't.
2. EXEC command can't be used inside a Function where it
can be used inside an sproc.
Is This Answer Correct ? | 75 Yes | 23 No |
Answer / vijayabhaskarreddy
there are 3 main differences between sp and function.
1 sp takes input,output parameters, function takes only
input parameters.
2 temparary variables required to store return values of
sp. in functions temparary variables will be optinale.
3 sp can not be called directly into DML statements ,
functions can be called directly into DML statements.
Is This Answer Correct ? | 64 Yes | 19 No |
Answer / pss
Function return only one value where as procedure can more
than
Is This Answer Correct ? | 58 Yes | 14 No |
Answer / ugandhar
Stored procedure have the security and reduce the network
traffic.
and also we can call stored procedure in any no.of
applications at a time.
Is This Answer Correct ? | 46 Yes | 12 No |
Answer / srinivas
Stored procedures are a set of actions already written and
stored inside the database for acheiving a particular task
where as functions are general database objects which are
used for general purpose programming
Is This Answer Correct ? | 46 Yes | 27 No |
Answer / prashant
1>Procedure can return zero or n values whereas function can
return one value which is mandatory.
2>Procedures can have input,output parameters for it whereas
functions can have only input parameters.
3>Procedure allow select as well as DML statement in it
whereas function allow only select statement in it.
4>Functions can be called from procedure whereas procedures
cannot be called from function.
5>Exception can be handled by try-catch block in a procedure
whereas try-catch block cannot be used in a function.
6>We can go for transaction management in procedure whereas
we can't go in function.
7>Procedures can not be utilized in a select statement
whereas function can be embedded in a select statement.
Is This Answer Correct ? | 22 Yes | 4 No |
Answer / bhupi
SP return a single result-set,
SP works with table variables,
SP uses EXEC stmts
Is This Answer Correct ? | 28 Yes | 12 No |
Answer / arvind kumar
there main difference:::
1 sp takes input,output parameters, function takes only
input parameters.
2 temparary variables required to store return values of
sp. in functions temparary variables will be optinale.
3 sp can not be called directly into DML statements ,
functions can be called directly into DML statements.
Is This Answer Correct ? | 13 Yes | 2 No |
Hi Friends, I have a table in which there are thousands of records and in city field there is NULL value for all records now i want to change that null value with distinct values in each record say delhi, bihar, agra, jaipur etc, what will be the query for that????? its not possible to update thousands of records one by one. is there any alternative ...? Plz help ... its urgent Thanx in advance
What is a recursive stored procedure in sql server?
What are the types of resultset?
What is the purpose of grouping data in a report?
What command is used to rename the database?
Can some one please help with a query which will take only max value of a column in a join.
What is cursor in ms sql server?
wht is normalization?can u explain me in detail?
What is the use of keyword with encryption. Create a store procedure with encryption?
What is the name of the system variable that returns the number of rows affected by a SQL statement?
Which trace flags are enabled in sql server?
Other than truncate statement, which other command can by-pass the trigger on the tables?