X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> User Defined Function Using Table Fields As Parameters?, Any Versions    
post Oct 23 2017, 03:04 PM

UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine

SQL Server 2016 (it is not among the drop-down choices when starting a topic).

I have the following VBA procedure that takes the basic name of a document, together with any prefix or suffix, and combines it into a single string. Any of the arguments (they are field values, in practice) could be null. I don't think the reason matters.

Anyhow, I have managed to put together a SQL Server user-defined function to concatenate the three field values, but it isn't quite as simple as that, as the code shows (I hope).

I expect I could do this with nested IIf statements or similar logic, but I wonder if SQL Server UDFs offer the option of a sort of code block structure more like the VBA If...Then...Else structure rather than the linear IIf structure, or if otherwise this could be handled by creating the equivalent of strName as the udf version of a variable, then do the same with strPfx and strSfx, and finally combine the three.

Also, I wonder if a udf is the appropriate choice. I haven't quite sorted out what stored procedures do, but maybe one of those would be better here?

Public Function FullDocName( _
    ByVal Base As Variant, _
    ByVal Prefix As Variant, _
    ByVal Suffix As Variant) _
  As String
' Assemble name with prefix and/or suffix, if any.
'    Examples:
'  Prefix     Base     Suffix     Return Value
'  R22        C1234               R22 C1234
'  Z11                            Z11
'             AB987               AB987
'             L22      6SC        L22 6SC
'             M1631    -11        M1631-11

  Dim strName As String
  Dim strPfx  As String
  Dim strSfx  As String

  strName = _
    Nz(Base, "")
  If Not IsNull(Prefix) Then
      strPfx = _
        Prefix & " "
  End If
  If Not IsNull(Suffix) Then
      If Left$(Suffix, 1) = "-" Then
          strSfx = Suffix
          strSfx = _
            " " & Suffix
      End If
  End If

  FullDocName = _
    Trim$(strPfx & strName & strSfx)

End Function

The following gives the sense of the udf. I haven't figured out if there is a way simply to view it:
ALTER FUNCTION [dbo].[udfFullDocName]
    @Pfx nvarchar (15), @BaseName nvarchar (20), @Sfx nvarchar (15)
RETURNS nvarchar(50)
    RETURN IsNull(@Pfx,'') + ' ' + IsNull(@BaseName,'') + ' ' + IsNull(@Sfx,'')

I will just mention that I have figured out how to invoke the function via a SELECT:

SELECT dbo.udfFullDocName(T.Prefix,T.BaseName,T.Suffix)
FROM dbo.tblMain AS T

I am writing this at the end of a long day, so will not be getting back to this until tomorrow. I mention this because my reply may be delayed.
Go to the top of the page
post Oct 25 2017, 09:47 AM

Posts: 788
Joined: 25-April 14

I use these function all the time in my query and they work.
Yours looks OK. What is it producing, (or not producing)?

tho you no longer need use $ in functions
Trim$( is Trim(
Left$( is Left(
Go to the top of the page
post Oct 25 2017, 02:19 PM

UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine

The function works fine in VBA. I'm trying to do the same processing in a SQL Server UDF.

Yeah, I have some of those $ prefixes left over. I know they could go, but they do no harm, so I tend not to get around to it unless I need to rework the code.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 12:07 AM