Any advantage to Using SQL Server UDF Functions?

I am fairly new to SQL Server, using Developer 14.  UP to this point I have worked with Views and SP's but no UDF functions.

I have a situation now that I think would be perfect for a function but before I go to the trouble of creating one I want to first ask if they are efficient or advantageous in any way, other than possibly reducing code in the executing SPROC.  If they are, then how to create one to replace SPROC 2 below.

I am creating a pretty simple SPROC that will add a new 'Hold' for a Tax record.  The Hold is passed to the SPROC as a numeric value, along with the RecordID of the Tax record.

The logic in SPROC1 will be:
1. First check if the hold is already on the record
2. If not on the record, add it.

At this point in my SQL learning curve I would approach this with 2 SPROCS

SPROC1 - In the main SPROC.  It will be passed HoldID and RecordID.  There is no Output Parameter.
Currently I will have it executing SPROC2 to determine whether the hold is already on the record.
If the HoldID is not already on the record I will insert a new record into the Hold File for this Tax Record.

SPROC2 - will be Executed  by SPROC1.  It will be passed HoldID and RecordID.  The Ouptu Paramter will return a non-zero number if the HoldID already exists for this record and 0 if it does not exist.

SProc2 :
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spGetMatchingInstallPayIDForPayment]    Script Date: 11/28/2017 7:36:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter PROCEDURE [dbo].[spCheckIfHoldExists] 
	-- Add the parameters for the stored procedure here
	@HoldID		    int, 
	@TaxRecordID    int, 
	@ReturnValue	int = 0	 Output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SET @ReturnValue = IsNull(
	( Select PropertyID
	From  vtblTaxAuthortity_PropID_TAID_Muni_HoldTypeID_HoldClassID
	Where @HoldID       = HoldTypeID  	  and
	      @TaxRecordID  = TaxAuthorityID),0)  
END

Open in new window


SPROC1 will check the Output Parm from SPROC2.  If it is greater than 0 is will exit without creating a new record, since it already exists.

I understand that if SPROC2 were a function instead of a SP I could would save a coding line as I could use

IF fnCheckIfHoldExists  (HOldID, RecordID) then

Open in new window


rather than

Execute  spCheckIfHoldExists  HoldID, RecordID
If ReturnID =0 
     insert .......

Open in new window



Other than saving one code line would there be any benefit to converting SPROC2 to a function?  If there is, how would it look as a function?
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
The biggest benefit of UDFs in SQL Server (in my opinion, of course!) is the ability to consume them within SELECT statements. For scalar functions (returning single value, as in your code above) can be used as a column. Table returning functions (called table valued functions, TVFs) can be used as a table in joins and in addition you can pass parameters. So you can OUTER APPLY and do amazing things.

Having said that, the drawback is you cannot get the execution plans of function execution. So while in the process of performance improvements UDFs act as a black box. It should be converted back to sp to examine further.

After learning this, I started avoiding UDFs. I suppose, usage as above, if being called from several places, is fine for a function. Not much harm.

Google for the exact syntax. It is pretty straight forward.
0
Pawan KumarDatabase ExpertCommented:
Hi Mlcktmguy,

This is a big post so is divided in below sections.

1. ADVANTAGES OF USING FUNCTIONS OVER SP.
2. UPDATE PROC1 and PROC2 for your code so that you can use them directly.
3. Working Examples of getting value from one SP to another using OUTPUT Parameters and TEMP Tables.
4. Functions code. Changed your code to include a scalar function for checking the data and modified your proc2 to get the value from the scalar function.

1.

ADVANTAGES OF USING FUNCTIONS OVER SP.
There are no such advantages but there are differences on how they can be used at places and how they work. Also note that do not use functions around columns in JOINS and in where clause as that would lead to scanning the entire table. Please read the post from my blog - https://msbiskills.com/2010/08/12/differences-between-stored-procedure-and-function-in-sql-server/
<I have given example of how we can use function for your code at the end>

Your Updated code for your PROC1

--
USE [JTSConversion]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter PROCEDURE [dbo].[spCheckIfHoldExists] 
	-- Add the parameters for the stored procedure here
	@HoldID		    int, 
	@TaxRecordID    int, 
	@ReturnValue	int = 0	 Output
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT TOP 1 @ReturnValue = ISNULL(PropertyID ,0)
	From vtblTaxAuthortity_PropID_TAID_Muni_HoldTypeID_HoldClassID
	Where HoldTypeID = @HoldID AND TaxAuthorityID = @TaxRecordID

	RETURN 
END
--

Open in new window



Your Updated code for your PROC2

--
USE [JTSConversion]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sptblHold_Insert] 
(
      @passedTaxAuthorityID            int          = 0,
      @passedPropertyID                  int          = 0,
      @passedHoldTypeID                  int          = 0,      
      @passedHoldEffectiveDate      datetime     = null,
      @passedComment                  nvarchar(Max)= null, 
      @passedDateAdded                  datetime     = null,
      @passedUserAdded                  nvarchar(50) = null
)
As

BEGIN
SET NOCOUNT ON;

  DECLARE @ReturnHoldRecID INT=0

  Execute dbo.[spCheckIfHoldExists] @passedHoldTypeID, @passedTaxAuthorityID, @ReturnHoldRecID OUTPUT 
 
  If @ReturnHoldRecID <= 0
  BEGIN
       INSERT INTO dbo.tblHolds(TaxAuthorityID,PropertyID,HoldTypeID,HoldEffectiveDate,Comment,DateAdded,UserAdded)
       VALUES (@passedTaxAuthorityID,@passedPropertyID,@passedHoldTypeID,@passedHoldEffectiveDate,@passedComment,@passedDateAdded,@passedUserAdded)
  End 

END

Open in new window


SAMPLE EXAMPLES FOR YOU -

SOLUTION 1 - USING TEMP TABLE

I have created sample procedure ( PROC1, and PROC 2)  and shown how we can get value from one procedure to another. Detailed Explanation is written inside the proc2.

CREATE PROC1

CREATE PROC Proc1
(
	@ID INT OUTPUT
)
AS
BEGIN

	SELECT @ID = 20 

	SELECT @ID

	RETURN
END
GO

Open in new window


CREATE PROC2

CREATE PROC Proc2
(
	@Vals INT 
)
AS
BEGIN

		IF OBJECT_ID('tempdb..#tempDATA') IS NOT NULL DROP TABLE #tempDATA /*Drop the temp table if already there else create the new temp table*/
		CREATE TABLE #tempDATA ( ID INT )

		INSERT INTO #tempDATA /*Just above executing the SP use the insert into command, this will execute the sp and we will get the data in the temp table*/
		EXEC Proc1 @Vals                     /*Passing Value to the PROC 1*/

		DECLARE @RecieveVALUE AS INT
		SELECT TOP 1 @RecieveVALUE = ID FROM #tempDATA /* GET the value from the temp table to the variable*/

		SELECT @RecieveVALUE ReturnValuefromPROC1 /*Then we just need to select the data*/
END

Open in new window


EXECUTION

EXEC Proc2 0 

Open in new window


OUTPUT

/*------------------------
OUTPUT 
------------------------*/

ReturnValuefromPROC1
--------------------
20

(1 row(s) affected)

Open in new window



SOLUTION 2 - USING OUTPUT Parameters

I have created sample procedure ( PROC1, and PROC 2)  and shown how we can get value from one procedure to another using OUTPUT paramters.
Detailed Explanation is written inside the proc2.

CREATE PROC1

CREATE PROC Proc1
(
	@ID INT OUTPUT
)
AS
BEGIN

	SELECT @ID = 20 	

	RETURN
END
GO

Open in new window


CREATE PROC2

CREATE PROC Proc2
(
	@Vals INT 
)
AS
BEGIN

		DECLARE @RecieveVALUE AS INT 		

		EXECUTE [dbo].Proc1 @RecieveVALUE OUTPUT /* This is how we call the other SP and GET THE value in the local variable from other SP */

		SELECT @RecieveVALUE ReturnValuefromPROC1  
END

Open in new window


EXECUTION

EXEC Proc2 0 

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
ReturnValuefromPROC1
--------------------
20

(1 row(s) affected)

Open in new window


FUNCTIONS

You can also use a function for above checking. Below is an example for you.

CREATE FUNCTION dbo.fn_spCheckIfHoldExists
(
	@HoldID		    int, 
	@TaxRecordID    int	
)
RETURNS INT
AS
BEGIN
	DECLARE @ReturnValue AS INT = 0;

    SELECT TOP 1 @ReturnValue = ISNULL(PropertyID ,0)
	From vtblTaxAuthortity_PropID_TAID_Muni_HoldTypeID_HoldClassID
	Where HoldTypeID = @HoldID AND TaxAuthorityID = @TaxRecordID

	RETURN @ReturnValue
END
GO

Open in new window


UPDATE code for proc 2 in case you use the function for checking the value.

Your Updated code for your PROC2

--
USE [JTSConversion]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sptblHold_Insert] 
(
      @passedTaxAuthorityID            int          = 0,
      @passedPropertyID                  int          = 0,
      @passedHoldTypeID                  int          = 0,      
      @passedHoldEffectiveDate      datetime     = null,
      @passedComment                  nvarchar(Max)= null, 
      @passedDateAdded                  datetime     = null,
      @passedUserAdded                  nvarchar(50) = null
)
As

BEGIN
SET NOCOUNT ON;

  DECLARE @ReturnHoldRecID INT=0

  SELECT @ReturnHoldRecID = dbo.fn_spCheckIfHoldExists(@passedHoldTypeID,@passedTaxAuthorityID)  
 
  If @ReturnHoldRecID <= 0
  BEGIN
       INSERT INTO dbo.tblHolds(TaxAuthorityID,PropertyID,HoldTypeID,HoldEffectiveDate,Comment,DateAdded,UserAdded)
       VALUES (@passedTaxAuthorityID,@passedPropertyID,@passedHoldTypeID,@passedHoldEffectiveDate,@passedComment,@passedDateAdded,@passedUserAdded)
  End 

END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
You want to use a table-valued function (code below), because they perform better than scalar functions when being called for a lot of rows.

For single values, you can call the function directly:

IF NOT EXISTS(SELECT * FROM dbo.CheckIfHoldExists(@HoldID, @TaxRecordID))
BEGIN
    --INSERT new row into Hold table
END /*IF*/

For processing data from a table, call the function with OUTER APPLY:
SELECT HoldID, TaxRecordID, ISNULL(hold.id, 0) AS ExistingHoldId
FROM dbo.main_table mt
OUTER APPLY dbo.CheckIfHoldExists(mt.HoldID, mt.TaxRecordID) AS hold


CREATE FUNCTION dbo.CheckIfHoldExists
(
	@HoldID		    int, 
	@TaxRecordID    int    
)
RETURNS TABLE
AS
RETURN (
    Select PropertyID
	From  vtblTaxAuthortity_PropID_TAID_Muni_HoldTypeID_HoldClassID
	Where @HoldID       = HoldTypeID  	  and
	      @TaxRecordID  = TaxAuthorityID
)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.