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

What are the difficulties faced in cube development? : sql server analysis services, ssas

0 Answers  

What is difference in performance between insert top (n) into table and using top with insert?

0 Answers  

What happens if null values are involved in bitwise operations?

0 Answers  

What are the differences between triggers and stored procedures?

0 Answers  

Explain what is raid and what are different types of raid levels?

0 Answers  

you are provided with the single table having say 4 col ie fname lname age city , now the all records with displying of only fname and lname is required but in this format say my name is abhay khanna it will come like this abhay-khanna rahul-roy gaurav-singh the above format is required

3 Answers  

Are there any preferred steps that need to be taken care of before starting the installation of sql server 2000?

0 Answers  

What is hot add cpu in sql server 2008?

0 Answers  

What is the difference between osql and query analyzer?

0 Answers  

What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?

1 Answers  

Should you normalize audio?

0 Answers  

How to create an multi-statement table-valued function?

0 Answers