Scalar Function or Stored Procedure – to SQL Server and VBA

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
Declare
    @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 & ")")
Douglass MacLeanCEO, CTOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Well, it depends on your ExecuteScalar function. It's a custom one. Thus the question is: how does it look like?

And for your actual problem: You can call your function as you have posted.
The stored procedure must return a single row with a single column. This means, it must use especially SET NOCOUNT ON;, otherwise you have more result sets. But such a procedure makes from the T-SQL viewpoint not that much sense, here I would expect a procedure with a output parameter instead.
Douglass MacLeanCEO, CTOAuthor Commented:
Hi ste5an,
Thanks taking your valuable time to consider my issue.
Here is the Execute Scalar code.
Public Function ExecuteScalar(SQLCommand As String) As Variant
On Error GoTo HandleError
    Dim rst As New adodb.Recordset
    Dim Result As Variant
   
    With rst
        .Source = SQLCommand
        .ActiveConnection = strENCONNECT
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
        .Open
        If Not rst.BOF And Not rst.EOF Then
            Result = rst.Fields(0).Value
            ExecuteScalar = Result
        Else
            ExecuteScalar = Null
        End If
        Set rst.ActiveConnection = Nothing
        .Close
        Set rst = Nothing
    End With
   
    Exit Function
HandleError:
    MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & _
            SQLCommand, vbOKOnly + vbCritical, "Execute Scalar Procedure"
End Function


I was not quite clear, I think, on the issue. In Access VBA I can get what I want with the stored procedure. I REALLY want to use the function:
-- =============================================
-- Author:            Doug MacLean
-- Create date: 2018-12-03
-- Description:      Get then end date for a specific recurring month or quarter
-- =============================================
CREATE FUNCTION [dbo].[fnGetPeriodEndDate](
  @RecurFreq int
, @RecurYr int
, @RecurSeq int
)
RETURNS date
AS
BEGIN
      Declare @NewRecurDate date
      Declare @EndMo int
      Declare @EndDay int
      If @RecurFreq = 12 begin
            Set @EndMo = @RecurSeq
      end
      else if @RecurFreq = 4 begin
            Set @EndMo = (case
                  when @RecurSeq = 1 then 3
                  when @RecurSeq = 2 then 6
                  when @RecurSeq = 3 then 9
                  when @RecurSeq = 4 then 12
                  end)
      end
      Set @EndDay = (case
            when @EndMo in (9, 4, 11, 6) then 30
            when @EndMo = 2 then (case when @RecurYr % 4 = 0 then 29 else 28 end)
            else 31
            end)            
      Set @NewRecurDate = convert(datetime, convert(varchar, @RecurYr) + '-' + convert(varchar,@EndMo) + '-' + convert(varchar,@EndDay))
--      Return the result of the function
      RETURN  @NewRecurDate
END
GO
ste5anSenior DeveloperCommented:
First of all: I would rewrite that function.

Use speaking names, get as verb should not be used, cause it is too generic. There are newer T-SQL functions (SQL Server 2012+) , which simplify the calculation for the end of the month. And there is a possible glitch. There is no default, thus not all calling values return a valid date, NULL is possible. E.g.

CREATE FUNCTION dbo.fn_PeriodEndDate (
    @RecurrenceFrequency INT ,
    @RecurrenceYear INT ,
    @RecurrenceSequence INT
)
RETURNS DATE
AS
BEGIN
    DECLARE @EndMonth INT = CASE WHEN @RecurrenceFrequency = 4
                                      AND @RecurrenceSequence IN ( 1, 2, 3, 4 ) THEN @RecurrenceSequence * 3 -- Quarterly (?)
                                 WHEN @RecurrenceFrequency = 12 THEN @RecurrenceSequence                     -- Yearly (?)
                                 ELSE 12                                                                     -- Default.
                            END;

    RETURN EOMONTH(DATEFROMPARTS(@RecurrenceYear, @EndMonth, 1));
END;
GO

Open in new window


EDIT: There are two types of functions in SQL Server. Those who return a scalar and those who return a result set. Yours is of the first type. Here the T-SQL syntax is like using a normal function in T-SQL:

SELECT yourFunctionName(parameters);

Open in new window

The other type returning a result set is called as

SELECT * FROM yourFunctionName(parameters);

Open in new window

Your VBA function is correct.

Option Compare Database
Option Explicit

Public Sub test()

  Debug.Print ExecuteScalar("SELECT @@VERSION;", "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=(local)")
  Debug.Print ExecuteScalar("SELECT dbo.fn_PeriodEndDate(1, 2019, 1);", "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=(local)")

End Sub

Public Function ExecuteScalar(SQLCommand As String, strENCONNECT As String) As Variant
 On Error GoTo HandleError
     Dim rst As New ADODB.Recordset
     Dim Result As Variant
     
     With rst
         .Source = SQLCommand
         .ActiveConnection = strENCONNECT
         .CursorType = adOpenStatic
         .CursorLocation = adUseClient
         .LockType = adLockReadOnly
         .Open
         If Not rst.BOF And Not rst.EOF Then
             Result = rst.Fields(0).Value
             ExecuteScalar = Result
         Else
             ExecuteScalar = Null
         End If
         Set rst.ActiveConnection = Nothing
         .Close
         Set rst = Nothing
     End With
     
     Exit Function
HandleError:
     MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & SQLCommand, vbOKOnly + vbCritical, "Execute Scalar Procedure"
 End Function

Open in new window

p.s. use the [code] tags (the CODE button in the toolbar). This makes posts much more readable. You can edit your posts..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PletcherSenior DBACommented:
For efficiency reasons, don't use a local variable in a scalar function unless you absolutely must have it.  In this situation, you don't.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER  FUNCTION [dbo].[fnGetPeriodEndDate](
  @RecurFreq int
, @RecurYr int
, @RecurSeq int
)
RETURNS date
AS
BEGIN
RETURN (
    SELECT CASE WHEN EndMonth > 12 THEN NULL
        ELSE DATEADD(DAY, -1, DATEADD(MONTH, EndMonth, Jan01)) END AS NewRecurDate
    FROM (
        SELECT EndMonth = CASE @RecurFreq
            /* order is from most-used to least-used, for efficiency */
            WHEN 12 THEN @RecurSeq
            WHEN  4 THEN @RecurSeq * 3
            WHEN  1 THEN 12
            WHEN  3 THEN @RecurSeq * 4
            WHEN  2 THEN @RecurSeq * 6
            WHEN  6 THEN @RecurSeq * 2
            END,
            Jan01 = DATEFROMPARTS(@RecurYr, 01, 01) --If you don't have DATEFROMPARTS available, let me know
    ) AS calc1
)
END --FUNCTION
GO
Douglass MacLeanCEO, CTOAuthor Commented:
Thanks Scott. That's useful information and I will probably adopt your version of the function.

That said, I still have the issue that I can call the function in a SET statement in a stored procedure, but have not yet found a way to call the function from VBA.
ste5anSenior DeveloperCommented:
..

There are two types of functions in SQL Server. Those who return a scalar and those who return a result set. Yours is of the first type. Here the T-SQL syntax is like using a normal function in T-SQL:

SELECT yourFunctionName(parameters);

Open in new window

The other type returning a result set is called as

SELECT * FROM yourFunctionName(parameters);

Open in new window


You've used the second version, which is wrong for your type of function.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.