Avatar of mlcktmguy
mlcktmguy
Flag for United States of America

asked on 

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?
Microsoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon