Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Any advantage to Using SQL Server UDF Functions?

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft SQL Server
3 Comments1 Solution105 ViewsLast Modified:
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

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

rather than

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


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?