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.
/****** Object: StoredProcedure [dbo].[spGetMatchingInstallPayIDForPayment] Script Date: 11/28/2017 7:36:23 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
Alter PROCEDURE [dbo].[spCheckIfHoldExists]
-- Add the parameters for the stored procedure here
@ReturnValue int = 0 Output
-- 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
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
Execute spCheckIfHoldExists HoldID, RecordID
If ReturnID =0
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?