I have a little block of code that generates a date value based on three parameters. I need to use it in two places.
1. SET a temporary variable in a stored procedure to the output of the block of code
2. Assign it to a variable in a VBA function behind a MS Access form
I want to have only one version of the code. Either a SQL Server scalar function or a SQL Server stored procedure. I don’t care which.
Here is where I stand in trying to achieve the goal.
SET temporary variable in a stored procedure
@RecurFreq int = 12 -- Periods per year
, @RecurYr int = 2020 -- Year
, @RecurSeq int = 2 -- Period
Declare @NewRecurDate date
-- Using this function works OK
SET @NewRecurDate = dbo.fnGetPeriodEndDate (@RecurFreq, @RecurYr, @RecurSeq)
SELECT @NewRecurDate as FromFunction
-- Executing this stored procedure works OK
EXEC dbo.rwGetPeriodEndDate @RecurFreq, @RecurYr, @RecurSeq
-- But I have not found a way to SET the @NewRecurDate variable to the result of the stored procedure execution
If that’s not possible, then I can go the other way. How do I call SQL Server from my VBA code to execute my fnGetPeriodEndDate function?
Here’s the VBA code:
Dim NewPeriodEndDate As Date
‘This works fine VBA knows how to deal with assigning the result of a stored procedure to a variable.
NewPeriodEndDate = ExecuteScalar("dbo.rwGetPeriodEndDate @RecurFreq = " & Me.grpRecurFreqSel & ", @RecurYr = " & Me.RecurYearSel & ", @RecurSeq = " & RecurSeq)
‘How do I get this to work? I cannot figure out how to use a function. It seems that Access doesn't recognize a SQL Server function as a legitimate recordset source.
NewPeriodEndDate = ExecuteScalar("Select * from dbo.fnGetPeriodEndDate (" & Me.grpRecurFreqSel & ", " & Me.RecurYearSel & ", " & RecurSeq & ")")