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 Comments3 Solutions105 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]
/****** Object:  StoredProcedure [dbo].[spGetMatchingInstallPayIDForPayment]    Script Date: 11/28/2017 7:36:23 PM ******/

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

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

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?
Join our community to see this answer!
Unlock 3 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros