Stored Procedure
A stored procedure is a set of Transact-SQL statements that has been compiled into a single execution plan and stored in the database. We can create applications that execute the stored procedures and process the results.
Stored procedures return data in four ways:
1. Output parameters, which can return either data or a cursor variable.
2. Return codes, which are always an integer value.
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.
User-Defined Functions
Functions are subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse. A user-defined function takes zero or more input parameters and returns either a scalar value or a table.
Difference Between Stored procedure and User-Defined Function
1. A Function return rowsets, tables, VARCHARs, INTs, and other data types. While a Stored procedure can only return an INT value.
2. Function always return a single value to the caller, while
Stored procedures do not.
3. When Transact-SQL encounters an error the function stops, while Transact-SQL will ignore an error in a Stored procedure and proceed to the next statement(provided Err handling).
4. A Stored procedure can be used in an XML FOR clause, a Function can't be.
5. Result of the function can be used within a SQL staement. A result set or retun value of the stored procedurecannot be used within a SQL statement.
A stored procedure is a set of Transact-SQL statements that has been compiled into a single execution plan and stored in the database. We can create applications that execute the stored procedures and process the results.
Stored procedures return data in four ways:
1. Output parameters, which can return either data or a cursor variable.
2. Return codes, which are always an integer value.
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.
User-Defined Functions
Functions are subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse. A user-defined function takes zero or more input parameters and returns either a scalar value or a table.
Difference Between Stored procedure and User-Defined Function
1. A Function return rowsets, tables, VARCHARs, INTs, and other data types. While a Stored procedure can only return an INT value.
2. Function always return a single value to the caller, while
Stored procedures do not.
3. When Transact-SQL encounters an error the function stops, while Transact-SQL will ignore an error in a Stored procedure and proceed to the next statement(provided Err handling).
4. A Stored procedure can be used in an XML FOR clause, a Function can't be.
5. Result of the function can be used within a SQL staement. A result set or retun value of the stored procedurecannot be used within a SQL statement.
No comments:
Post a Comment