What are the differences between stored procedure and
functions in SQL Server 2000?
Answer Posted / 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 |
Post New Answer View All Answers
Does partitioning help performance?
What objects does the fn_my_permissions function reports on? : sql server security
If you lose rights to your sql server instance what are the options to connect to sql server instance? : sql server security
How can we delete Duplicate row in table?
How to attach adventureworkslt physical files to the server?
What is Lock table in SQL?
What is a matrix in ssrs?
Where cross join is used?
What are number line correlation administrators will use while working with a subquery?
What is query optimization process?
How many full-text indexes can a table have?
What are the advantages to use stored procedures?
Do you know the capabilities of cursors?
Mention the uses of stored procedures.
What is difference between stored procedure and user defined function?