what is difference between function and stored procedure in sql server 2005 with example?
44441
The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values. Functions can have only input parameters for it whereas Procedures can have input or output parameters. Functions can be called from Procedure whereas Procedures cannot be called from a Function.
310528
Aggregate functions operate on many records and produce a summary, works with GROUP BY whereas non-aggregate functions operate on each record independently. There are so many built-in functions in SQL to do various calculations on data.
1412923
Stored procedure always returns an integer value of zero by default. Whereas function return types could be scalar or table or table values. ... A Function can be used in the SQL queries while a procedure cannot be used in SQL queries. That causes a major difference between function and procedures.
1116670
['Scalar Functions (Returns A Single Value)', 'Inline Table Valued Functions (Contains a single TSQL statement and returns a Table Set)', 'Multi-Statement Table Valued Functions (Contains multiple TSQL statements and returns Table Set)']
1024185
A view is essentially a saved SQL statement. Therefore, I would say that in general, a stored procedure will be likely to be faster than a view IF the SQL statement for each is the same, and IF the SQL statement can benefit from optimizations. Otherwise, in general, they would be similar in performance.
753660
The main difference between a function expression and a function declaration is the function name, which can be omitted in function expressions to create anonymous functions. A function expression can be used as an IIFE (Immediately Invoked Function Expression) which runs as soon as it is defined.
9591
Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters . ... Functions can be called from Procedure whereas Procedures cannot be called from Function.
439692
Procedure allows select as well as DML statement in it whereas function allows only select statement in it. Functions can be called from procedure whereas procedures cannot be called from function. ... UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
211660
Go is essentially is SSMS code rather than a regular T-SQL code. It used to define end of a batch of SQL code in SSMS. I won't say there is any perticular reason for using GO. But if you have a create procedure statement and insert statements together in a deployment script then you need to separate them using GO.
1108614
Functions can be used in select or update or delete statement. Trigger gets automatically executed when insert update or delete is fired on a table. It is a type of stored procedure invoked automatically for a predefined DML operation . cannot accept parameters.
284749
Sub-Procedures are similar to functions but there are few differences. Sub-procedures DONOT Return a value while functions may or may not return a value. Sub-procedures Can be called without call keyword. Sub-procedures are always enclosed within Sub and End Sub statements.
1350383
Action can contains Object Repository, Data table, Active screen etc. whereas function do not have these features. Action is internal to QTP whereas Function is just lines of code with some/none parameters and a single return value. Action can/can not be resuable whereas functions are always reusable.
913050
['Description. In SQL Server (Transact-SQL), the SUBSTRING functions allows you to extract a substring from a string.', 'Syntax. The syntax for the SUBSTRING function in SQL Server (Transact-SQL) is: SUBSTRING( string, start_position, length ) ... ', 'Note. The first position in string is 1. ... ', 'Applies To. ... ', 'Example.']
847257
The BEGIN and END blocks are mandatory in a function while the stored procedure do not require them if it is just one line. In a function, it is mandatory to use the RETURNS and RETURN arguments, whereas in a stored procedure is not necessary.
440090
A procedure is compiled once and can be called multiple times without being compiled. A function returns a value and control to calling function or code. A procedure returns the control but not any value to calling function or code.
219296
SQL query and Stored procedure do the same thing. But the difference is that, a query should be compiled every time the query is executed, while the stored procedure is in compiled form when executed first time. If we use stored procedure we can avoid recompilation of the query.
750987
query and stored procedure do the same thing but the difference is that a query should be compiled everytime the query is executed,while the stored procedure is in compiled form when executed first time.