What
are the differences between User Defined Function and Stored Procedure
[Function
vs Stored Procedure]?
|
User Defined Function
|
Stored Procedure
|
||||||||
|
Use
|
SQL Server User Defined Functions
are routines that accept parameters, perform an action, such as a complex
calculation, and return the result of that action as a value. The return value
can either be a single scalar value or a result set.
NOTE:
There are four types of User Defined Function in SQL Server.
·
Scalar-Valued Function – The function
takes one OR many input values, but returns a single data value.
·
Table-Valued Functions – The function
takes one OR many input parameters and must return the columns of a table.
·
Aggregate Functions –Perform a calculation
on a set of values and return a single value. Except for COUNT, aggregate
functions ignore null values. Aggregate functions are frequently used with
the GROUP BY clause of the SELECT statement.
·
System Functions – Perform operations
on and return information about values, objects, and settings in SQL Server.
Example:
AVG() , MAX(), MIN(), SUM()..
|
SQL Server Stored Procedure is
a set of Structured Query Language (SQL) statements with an assigned name
that's stored in the database in compiled form so that it can be shared by a
number of programs.
NOTE:
Types of Stored Procedures in SQL Server are,
·
User-Defined Stored Procedure – It allows
us to create bundles of SQL statements that are stored in a single unit on
the server for future use.
Example:
[dbo].[USP_PT_ErrorLog_Record]
·
System Stored Procedures -Many administrative
activities in SQL Server are performed through a special kind of procedure
known as a system stored procedure. Its name starts with “sp_”
Example:
sp_helptext USP_PT_ErrorLog_Record
·
Extended Stored
Procedures– It will be removed in future version of SQL
Servers.
|
|||||||
|
Storage Location
|
Scalar-Valued Functions are created under Scalar-Valued Functions section in the Functions
section of current database Programmability section.
Table-Valued Functions are created under Scalar-Valued Functions section in the Functions
section of current database Programmability section.
|
SQL Server Stored Procedures are
created under Stored Procedure section of current database Programmability
section.
|
|||||||
|
Syntax
|
EXAMPLE:
Scalar Function:
-- Create Scalar-Valued Function
CREATE FUNCTION [dbo].[GetTotal]
(
@Input1 INT,
@Input2 INT
)
RETURNS INT
AS
BEGIN
--return the total
RETURN @Input1 + @Input2
END
GO
-- Calle the Scalar-Valued Function
SELECT dbo.[GetTotal](1,2) AS Total
RESULT:
Table-Valued Function:
-- Create Table-Valued Function
CREATE FUNCTION [dbo].[GetCommaInput_TableFormat]
(
@InputString VARCHAR(MAX)
)
RETURNS @Table TABLE (
Input INT
)
AS
BEGIN
--return
INSERT @Table
SELECT Members.Member.value('.','INT') AS RecordID FROM
(
SELECT
CAST ('<T><D>'
+ REPLACE(@InputString, ',', '</D></T><T><D>')
+ '</D></T>' AS
XML) AS CommasValue
) AS CommasValue
CROSS
APPLY CommasValue.nodes('/T/D') Members(Member)
RETURN
END
GO
-- Call the Table-Valued Function
SELECT *
FROM dbo.[GetCommaInput_TableFormat]
('100,95,80,99') AS OutputTable
RESULT:
|
EXAMPLE:
-- Creating the Stored Procedure
CREATE PROCEDURE [dbo].[USP_PT_ErrorLog_Record]
-- Parameters
for the stored procedure
@UserID INT,
@SystemMessageID INT
= 99,
@ErrorLogLineNumber INT,
@ErrorLogMessage VARCHAR(1000) = null,
@ErrorLogFunction VARCHAR(50) = null
AS
SET NOCOUNT ON;
BEGIN TRY
INSERT INTO [dbo].[ErrorLog]
([ErrorLogDate]
,[UserID]
,[SystemMessageID]
,[ErrorLogLineNumber]
,[ErrorLogMessage]
,[ErrorLogFunction]
)
VALUES
(GETDATE()
,@UserID
,@SystemMessageID
,@ErrorLogLineNumber
,@ErrorLogMessage
,@ErrorLogFunction
)
END TRY
BEGIN CATCH
IF @@ERROR>0 -- @@TRANCOUNT if
Begin commit tran used inside the try catch.
BEGIN
--DB Error
SELECT
@ErrorLogLineNumber = ERROR_LINE(),
@ErrorLogMessage = CAST(ERROR_NUMBER() AS VARCHAR(15)) + '-' + ERROR_MESSAGE(),
@ErrorLogFunction = ERROR_PROCEDURE()
INSERT INTO [dbo].[ErrorLog]
([ErrorLogDate]
,[UserID]
,[SystemMessageID]
,[ErrorLogLineNumber]
,[ErrorLogMessage]
,[ErrorLogFunction]
)
VALUES
(GETDATE()
,@UserID
,@SystemMessageID
,@ErrorLogLineNumber
,@ErrorLogMessage
,@ErrorLogFunction
)
END
END CATCH
SET NOCOUNT ON;
GO
-- Execution of Stored Procedure
EXEC [USP_PT_ErrorLog_Record] 1, '1', 23, 'This is Error due to invalid data', 'getAction'
-- Retrieve the data from ErrorLog table
SELECT * FROM [dbo].[ErrorLog]
WITH (NOLOCK)
|
|||||||
|
Behaviors
|
Function must have RETURN statement.
|
Stored Procedure may or may not have RETURN statement.
|
|||||||
|
Function must RETURN a Value.
|
Stored Procedure may or may not RETURN Values.
|
||||||||
|
Function doesn’t allow us to use the following commands inside with it.
·
INSERT, UPDATE and DELETE
·
PRINT and OUTPUT
INTO
·
BEGIN TRANS, COMMIT
TRANS, ROLLBACK TRANS,
·
TRY…CATCH, @ERROR or RAISERROR
·
SET options ON/OFF (NOCOUNT, INDENTITY ...)
|
Stored Procedure allows us to use all the commands inside with it.
|
||||||||
|
The following Service Broker
statements cannot be included in the definition of a Transact-SQL user-defined
function:
·
BEGIN DIALOG
CONVERSATION
·
END CONVERSATION
·
GET CONVERSATION
GROUP
·
MOVE
CONVERSATION
·
RECEIVE
·
SEND
|
Stored Procedure allows us to use all the Service Broker
statements inside with it.
|
||||||||
|
User-Defined Functions does
not support the dynamic SQL or temp tables but allows the Table variables.
|
Stored Procedure supports all kind of dynamic SQL, Temp tables or Table variables.
|
||||||||
|
Stored Procedures can’t
be called from Function.
|
Stored Procedure can
call the Functions.
Example:
SELECT Pay.Amount, [dbo].[UDF_PT_ConvertAmountToWord](Pay.Amount,0,1,1)
FROM Pay WITH (NOLOCK)
|
||||||||
|
User-Defined Functions can
be called from SELECT statement.
Example:
SELECT *
FROM dbo.[GetCommaInput_TableFormat]
('100,95,80,99') AS OutputTable
|
Stored Procedure can’t
be called from SELECT/WHERE/HAVING etc statements.
EXECUTE/EXEC - Statement can be used to
call/execute the Stored Procedure.
Example:
EXEC [USP_PT_ErrorLog_Record] 1, '1', 23, 'This is Error due to invalid data', 'getAction'
|
||||||||
|
User can be used in join
clause as a result set of User-Defined Function.
Example:
SELECT * FROM [dbo].[ErrorLog]
WITH (NOLOCK)
INNER JOIN (
SELECT * FROM dbo.[GetCommaInput_TableFormat] ('1,2')
)
OutputTable ON OutputTable.Input = [ErrorLog].ErrorLogID
|
Stored Procedures can’t
be used in Join as a result set.
|
||||||||
|
User-Defined Function will
allow only INPUT parameters, doesn’t support OUTPUT parameters.
Example:
CREATE FUNCTION [dbo].[GetCommaInput_TableFormat]
(
@InputString VARCHAR(MAX)
)
RETURNS @Table TABLE (
Input INT
)
AS
BEGIN
INSERT INTO @Table VALUES(1)
RETURN
END
|
Stored Procedures can
have both INPUT and OUTPUT parameters.
Example:
CREATE PROCEDURE [dbo].[USP_PT_Payee_Match_Name]
-- Parameters for the stored procedure
@QName [varchar](500),
@OutQName [varchar](500) OUTPUT
AS
BEGIN
.....
.....
SELECT @OutQName
= LTRIM(RTRIM(@QName))
END
|
