Difference between Stored Procedure and Function in SQL Server
- 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.
Scalar Functions
The function takes one input value, and returns a single data value,
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Quantity)
FROM Production.ProductInventory p
WHERE p.ProductID = @ProductID
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;
Table-Valued Functions
The function takes one input value, and returns Table,
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
how to call a sqlfunction in storedprocedure
--exec fun_proc 1,2
CREATE PROC fun_proc
@p_val1 int,
@p_val2 int
AS
BEGIN
Declare @sum int
select @sum = dbo.sums(@p_val1,@p_val2)--call function to get the value
print @sum
END
--Function
create function sums
(
@val int,
@val2 int
)
returns int
as
begin
return(@val + @val2)
end
select dbo.sums(1,2)
