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 ONGOSET QUOTED_IDENTIFIER ONGOAlter PROCEDURE [dbo].[spCheckIfHoldExists] -- Add the parameters for the stored procedure here @HoldID int, @TaxRecordID int, @ReturnValue int = 0 OutputASBEGIN -- 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