functions are used for computations where as procedures
can be used for performing business logic
2) functions MUST return a value, procedures need not be.
3) you can have DML(insert, update, delete) statements in a
function. But, you cannot call such a function in a SQL
query..eg: suppose, if u have a function that is updating a
table.. you can't call that function in any sql query.-
select myFunction(field) from sometable; will throw error.
4) function parameters are always IN, no OUT is possible
- Procedure may return none or more values.Function must always return one value either a scalar value or a table.
- Procedure have input,output parameters.Functions have only input parameters.
- Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
- Functions can be called from procedure.Procedures cannot be called from function.
- Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
- Transaction management possible in procedure but not in function.
exec ufn_GetMaxValue 1,2,3,4
CREATE FUNCTION [dbo].[ufn_GetMaxValue]
( @pInt1 INT, @pInt2 INT, @pInt3 INT, @pInt4 INT )
RETURNS INT
AS
BEGIN
DECLARE @IntTable TABLE ( [IntValue] INT )
DECLARE @MaxValue INT
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt1 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt2 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt3 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt4 )
SELECT @MaxValue = MAX( [IntValue] )
FROM @IntTable
RETURN @MaxValue
END
GO
