SQL Server functions and stored procedures offer similar functionality. Both allow you to create bundles of SQL statements that are stored on the server for future use. This offers you a tremendous efficiency benefit, as you can save programming time by:
· Reusing code from one program to another, cutting down on program development time
· Hiding the SQL details, allowing database developers to worry about SQL and application developers to deal only in higher-level languages
· Centralize maintenance, allowing you to make business logic changes in a single place that automatically affect all dependent applications
At first glance, functions and stored procedures seem identical. However, there are several subtle, yet important differences between the two:
· Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
· Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures, rather than permission to access the underlying tables. Stored procedures provide the ability to restrict user actions at a much more granular level than standard SQL Server permissions. For example, if you have an inventory table that cashiers must update each time an item is sold (to decrement the inventory for that item by 1 unit), you can grant cashiers permission to use a decrement item stored procedure, rather than allowing them to make arbitrary changes to the inventory table.
· Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.
Difference points vise
Stored procedures:
· Have to use EXEC or EXECUTE.· Return output parameter.
· You can create table but won't return Table Variables.
· You cannot join stored procedure.
· Can be used to change server configuration.
· Can be used with XML FOR Clause.
· Can have transaction within stored procedure.
· Procedures can be used for performing business logic.
· Stored Procedures takes input, output parameters.
· Stored Procedures cannot be called directly into DML statements.
· Procedure may return one or more values through parameters or may not return at all.
· Procedure can return multiple values (max 1024).
· Stored procedure returns always integer value by default zero.
· Stored procedure is precompiled execution plan.
· Procedure cannot be used in SQL queries.
Functions:
· Can be used with Select statement.· Not returning output parameter but returns Table variables.
· We can join UDF (user defined function).
· Cannot be used to change server configuration.
· Cannot be used with XML FOR clause.
· Cannot have transaction within function.
· Functions are used for computations.
· Function takes only input parameters.
· Functions can be called directly into DML statements.
· A FUNCTION is always returns a value using the return statement.
· A Function returns 1 value only.
· Whereas function returns type could be scalar or table or table values.
· A function can call directly by SQL statement like select func_name from dual.
· A Function can be used in the SQL Queries.
Syntax for creating Store Procedure
--SYNTAX DEMONSTRATION OF CREATING USER DEFINE STORED PROCEDURE
CREATE PROCEDURE <PROCEDURE_NAME>
(
-- PARAMETER OF STORED PROCEDURE
)
AS
BEGIN
--PROCEDURE BODY
--WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND HERE
END
GO
-- Execution Stored Procedure
EXEC <Procedure Name>
Syntax for creating Function
--Transact-SQL Function Syntax
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
--function_body
RETURN scalar_expression
END
For more details on SQL Function see below link
Anonymous User
29-Jun-2019Thanks for sharing.