Answer / aashish

In many instances you can accomplish the same task using either a stored procedure or a function. Both functions and stored procedures can be custom defined and part of any application. Functions, on the other hand, are designed to send their output to a query or T-SQL statement. For example, User Defined Functions (UDFs) can run an executable file from SQL SELECT or an action query, while Stored Procedures (SPROC) use EXECUTE or EXEC to run. Both are instantiated using CREATE FUNCTION.

To decide between using one of the two, keep in mind the fundamental difference between them: stored procedures are designed to return its output to the application. A UDF returns table variables, while a SPROC can't return a table variable although it can create a table. Another significant difference between them is that UDFs can't change the server environment or your operating system environment, while a SPROC can. Operationally, when T-SQL encounters an error the function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code (provided you've included error handling support). You

Answer / nazeema

Stored procedures can change server environment and our
operating system environment.
Function can not change server environment and our
operating system environment.

Stored procedures are stored in parsed and compiled format
in the database.
Functions are compiled and executed at run time.

Stored procedure return more than one value or Dataset.
Fuction can return a single value only.

error handling can be done in Stored procedure.
it not possible in function.

Answer / jeetu

Function can be used inside the queries but Store procedure never used inside the queries For Example Given below:

select avg(salary) from employee;// allowed here

avg(): is function

Select sp(salary) from employee // Not allowed here

sp(): is stored Procedure

Thanks

Answer / appu

1.we can't call stored procedure inside query instead we can call function inside query.
2.we can't use stored procedure in select statement where as we can use function in select statement.

Answer / sanjeev bhardwaj

Function:

Function must have a return type.
Function output can be set to the DMLstatement.
User Defined Functions (UDFs) can run an executable file
from SQL SELECT or an action query

Stored Procedure:

Stored Procedure may or may not have a return type.
Stored Procedure output can not st to any DML Statement.
Stored Procedure can run uder Execute command

Answer / sathishraj

*The DML statements INSERT, UPDATE, and DELETE cannot be
used on base tables
*SQL functions that return non-deterministic values are not
allowed to be called from inside User Defined Functions.
GETDATE is an example of a non-deterministic function.

Answer / jitendra

>Procedure may return none or more values.
->Function must always return one value either a scalar value or a table.
>Procedure allow modular programming.
>Function can be used in a select statement where as procedure can't.
>procedure can input and output parameter but Function takes only input parameter.

Answer / satish kr verma

Some of the differeneces are mentioned below

Function -
1) Have to return a single value to the calling program
2) Can call functions in sql statements
3) Cannot return images

Procedures -
1) Do not return any value except assigning values to OUT
variables
2) Cannot call procedures in sql statements
3) Can return images

Answer / sandeep

Function retun value while storeprocedure nor return value

Answer / dilip

HI FOR more specification for difference between sp and UDF
visit
http://sqldebate.blogspot.in/2012/02/diffrence-between-stored-procedure-and.html

How do I install sql server?

0 Answers  

What is function of CUBE ?

0 Answers   HCL,

what is the sql equivaent of the dataset relation object ?

0 Answers  

what is victor class

0 Answers  

How do you delete a data source?

0 Answers  

What is a materialized view?

3 Answers  

What is a trace frag? Where do we use it?

0 Answers  

Explain an automatic checkpoint

0 Answers  

How to get last system shutdown time in Sql server when restarted system?

1 Answers  

How to view the error log for any specific instance? : sql server database administration

0 Answers  

Which data types generate inaccurate results if used with an = or <> comparison in a where clause of a sql statement?

0 Answers  

What is the difference between push and pull subscription? : sql server replication

0 Answers