Monday, 10 July 2017

FUNCTION vs STORED PROCEDURE


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:

Total
3


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:

Input
100
95
80
99


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

[ALSO READ A FEW MORE...]


Posted by at

No comments:

Post a Comment