Difference between Stored Procedure and Function

Stored Procedures and Functions are named objects, which are a block of SQL codes grouped together, that can be executed many times without rewriting the queries.

Their differences are discussed below

Stored Procedure	Function
SP may or may not return values	Function always returns a value
SP may return more than one value. It cannot return a table.	Function can return only one value or a table
Supports DML Commands such as insert/update/delete	Supports only SELECT statements
SP can call functions	Functions cannot call SP
Executed as EXEC SP_NAME ‘parameters if any’	Executed as schema_name.function_name(paremeters if any). More on schema here.
Cannot be called from Select statement	Can be called from Select Statements
Exceptions can be handled by try – catch block	Exceptions cannot be handled by try – catch block

Below is an example of function called from a stored procedure :

create procedure [dbo].[Test]

@suburb varchar(255), /*parameters to be passed at the time of calling SP*/

@city varchar(255)

as

SELECT g.Country,g.State,

dbo.crimes(c.year,@city) /*Function Crimes() is being called here and arguments 'year' and 'city' passed*/

as TotalCrime

from Fact_Crime c

inner join Dim_Geography g

on c.DWGeoKey=g.DWGeoKey1

WHERE

g.Suburb=@suburb and g.City=@city

GO

/**************Function dbo.crimes()***************/

create function [dbo].[crimes](@year int,@city varchar(50))

returns decimal(9,0)

as begin 

declare @TotalCrime int

select @TotalCrime = sum([No Of Offences]) from Fact_Crime C

where C.city=@city  and C.year=@year

return @TotalCrime /*value returned to the SP*/ 

end

EXEC dbo.test 'Manurewa','Manukau' --Executing Stored Procedure and passing arguments

This function is an user-defined scalar function, called from select statement in the stored procedure.

Happy SQLing!

Please post your comments  (+ve or -ve, even neutral 😅 ) and questions and feel free share it to with your friends .

Cos sharing is caring..! 😎

Until next blog,

Vino BI Dev