Difference between function and procedure in sql

Functions and stored procedures are set of SQL statements that can be called by name. They have many similarities like they take arguments, used to perform required tasks and have same programming style and structure. But we are more interested in differences to grab the knowledge about power they held against each other. So here are some difference between function and stored procedure in Oracle point of view with examples.

CREATE FUNCTION SYNTAX

CREATE REPLACE FUNCTION function_name RETURN datatype…

CREATE FUNCTION DBO.GetTotalUser() 
RETURNS INT
AS BEGIN 
 DECLARE @TotalUser INT
 SELECT @TotalUser=COUNT(*) 
 FROM USER_LOGIN; 
 
 RETURN @TotalUser; 
END

CREATE PROCEDURE SYNTAX

CREATE  REPLACE PROCEDURE procedure_name …

CREATE PROCEDURE dbo.GetUsersByType @Type nvarchar(30)
AS
SELECT * 
FROM USER_LOGIN
WHERE USER_TYPE = @Type
GO

Leave a Reply

Your email address will not be published. Required fields are marked *