What are the differences between stored procedure and
functions in SQL Server 2000?

Answers were Sorted based on User's Feedback



What are the differences between stored procedure and functions in SQL Server 2000?..

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

Is This Answer Correct ?    7 Yes 0 No

What are the differences between stored procedure and functions in SQL Server 2000?..

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.

Is This Answer Correct ?    2 Yes 0 No

What are the differences between stored procedure and functions in SQL Server 2000?..

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

Is This Answer Correct ?    2 Yes 0 No

What are the differences between stored procedure and functions in SQL Server 2000?..

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.

Is This Answer Correct ?    1 Yes 0 No

What are the differences between stored procedure and functions in SQL Server 2000?..

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

Is This Answer Correct ?    1 Yes 0 No

What are the differences between stored procedure and functions in SQL Server 2000?..

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.

Is This Answer Correct ?    0 Yes 0 No

What are the differences between stored procedure and functions in SQL Server 2000?..

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.

Is This Answer Correct ?    0 Yes 0 No

What are the differences between stored procedure and functions in SQL Server 2000?..

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

Is This Answer Correct ?    0 Yes 0 No

What are the differences between stored procedure and functions in SQL Server 2000?..

Answer / sandeep

Function retun value while storeprocedure nor return value

Is This Answer Correct ?    0 Yes 0 No

What are the differences between stored procedure and functions in SQL Server 2000?..

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

Is This Answer Correct ?    0 Yes 0 No

Post New Answer

More SQL Server Interview Questions

how to rename the table

3 Answers  


how do u find least salary in a table

4 Answers  


What is the use of MOVE keyword in restore command

3 Answers   IBM,


If a user does not have permission to a table, but has permission to a view created on it, will he be able to view the data in table?

0 Answers  


Where are sql server usernames and passwords stored in the sql server?

0 Answers  






Is the primary key column of a table an index in ms sql server?

0 Answers  


What are system databases into sql server (2005/2008) : sql server database administration

0 Answers  


How do you delete duplicate records in sql server?

0 Answers  


What is tempdb database? : SQL Server Architecture

0 Answers  


What is sql collation?

0 Answers  


What is sparse columns of sql server 2008?

0 Answers  


What is the difference between functions and stored procedures?

0 Answers   Accenture,


Categories