UtterAccess.com
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
> Local Variables In Scalar Udf?, Any Versions    
 
   
BruceM
post Oct 24 2017, 03:15 PM
Post#1


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


Is it possible to have local variables in a user-defined scalar function? The documentation I have seen suggests it is possible, but so far no success. All SQL Server will tell me is that I have a syntax error "near" something or other, along with some jargon.

All I'm trying to do is concatenate three values, any of which may be null, into a finished result. Posted below is the result of several hours of work, interspersed with attempts to research the errors. I know it's wrong, but the solution eludes me.

CODE
ALTER FUNCTION [dbo].[udfFullDocName]
(
    @Pfx nvarchar (15), @BaseName nvarchar (20), @Sfx nvarchar (15)
)
RETURNS nvarchar(50)
AS
BEGIN

    DECLARE @strPfx nvarchar(15)
    DECLARE @strBase nvarchar(20)
    DECLARE @strSfx nvarchar(15)

    SET @strPfx = IsNull(@Pfx,'') + ' '

    SET @strBase = IsNull(@BaseName,'')

    SET @strSfx = IsNull(@Sfx,'')

    IIf(Left(@strSfx,1) = '-',SET @strSfx = @strSfx,SET @strSfx = ' ' + @strSfx)

    RETURN @strPfx + @strBase + @strSfx

END

I get the following error messages. I can only assume Line 24 is the IIf line, since without it the alteration seems to be successful. I have tried CASE also, but the only effect is different error messages.

Msg 102, Level 15, State 1, Procedure udfFullDocName, Line 24 [Batch Start Line 7]
Incorrect syntax near 'IIf'.
Msg 102, Level 15, State 1, Procedure udfFullDocName, Line 24 [Batch Start Line 7]
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure udfFullDocName, Line 24 [Batch Start Line 7]
Incorrect syntax near ')'.

I suspect that using Left(@strSfx,1) as a condition may be the problem. My hours of research have not shown any examples of using the Left function in this way.
Go to the top of the page
 
MadPiet
post Oct 24 2017, 03:26 PM
Post#2



Posts: 2,252
Joined: 27-February 09



Why not use CONCAT?
https://docs.microsoft.com/en-us/SQL/t-SQL/...at-transact-SQL

CODE
SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;
SELECT CONCAT ('Happy ', NULL, 'Birthday');
Go to the top of the page
 
GroverParkGeorge
post Oct 24 2017, 04:05 PM
Post#3


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Iif() is only in Access. Try a Case instead.

--------------------
Go to the top of the page
 
MadPiet
post Oct 24 2017, 05:47 PM
Post#4



Posts: 2,252
Joined: 27-February 09



QUOTE
Is it possible to have local variables in a user-defined scalar function?


Just wondering... the rest of your question has nothing to do with this...? You can declare local variables inside your function in T-SQL the same way you can a function in VBA. Here's a really simple example. I'm using a local variable to hold the value to accrue a total, and then returning it...

CODE
ALTER FUNCTION FunctionWithVariables
(
    -- Add the parameters for the function here
    @InputValue INT
)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @DummyVariable INT = 0; -- this is a local variable.
    DECLARE @Counter TINYINT = 1; -- so is this.

    -- Add the T-SQL statements to compute the return value here
    WHILE @Counter <= @InputValue
    BEGIN
        SET @DummyVariable += @Counter;    -- cheap running sum.
        SET @Counter += 1;
    END

    -- Return the result of the function
    RETURN @DummyVariable; -- pass this back out of the function.

END

PRINT dbo.FunctionWithVariables(5);
Go to the top of the page
 
BruceM
post Oct 25 2017, 02:27 PM
Post#5


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


My computer died this morning. Fortunately there was another one around that had Access installed, but getting it set up has taken a lot of my time today.

George, IIf has been available in SQL Server since the 2012 version. I would be glad to use Case if it works, but somehow I'm not handling the conditional test properly either way.

MadPiet, it isn't simple concatenation. Rather, some special handling is needed depending on the first character of the suffix.
The main thing is that I want to figure this out in a simple function so I know how to apply the same principles to more complex requirements. I seemed to do OK building the function and assigning local variables. Where it seemed to fail was the logic "If the suffix starts with a hyphen, leave it alone. If it starts with anything else, add a space in front of it." I can't find a way to apply that logical test and have the result reflected in the variable @strSfx. That was the intent of the IIf expression (or Case, or whatever does the job).
Go to the top of the page
 
cheekybuddha
post Oct 25 2017, 07:35 PM
Post#6


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


IIf() is not a statement on its own - you need to assign it to something (both in Access and SQLServer!)

Do you really want @strPfx to be a space if @strPfx is null?

If you don't wish to use CONCAT() as Piet suggests then try:
CODE
' ...
    SET @strPfx = COASLESCE(@Pfx + ' ', '')

    SET @strBase = COALESCE(@BaseName, '')

    SET @strSfx = CAOALESCE(@Sfx, '')

    IF LEFT(@strSfx, 1) != '-'
      SET @strSfx = ' ' + @strSfx

    RETURN @strPfx + @strBase + @strSfx
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 25 2017, 07:43 PM
Post#7


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


Using CONCAT():
CODE
ALTER FUNCTION [dbo].[udfFullDocName]
(
    @Pfx nvarchar (15), @BaseName nvarchar (20), @Sfx nvarchar (15)
)
RETURNS nvarchar(50)
AS
BEGIN

    RETURN CONCAT(@Pfx + ' ',  @BaseName, IIF(LEFT(@Sfx, 1) = '-', '', ' ') + @Sfx)

END


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Oct 26 2017, 06:21 AM
Post#8


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Just goes to show I need to up my knowledge. I have been converting Iif() expressions to Case statements all along.

--------------------
Go to the top of the page
 
BruceM
post Oct 26 2017, 07:36 AM
Post#9


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


QUOTE
If you don't wish to use CONCAT() as Piet suggests

I would like to do that, but one of the items being concatenated is conditional. I'm not trying to be stubborn, just trying to learn what to do.

Now I know about COALESCE, which looks to be a convenient function. I can imagine lots of uses for it.
QUOTE
Do you really want @strPfx to be a space if @strPfx is null?

No, I do not. My original VBA function used Trim. As I understand, SQL Server has TRIM in the 2017 version, but other than that there is LTRIM and RTRIM. I just hadn't gotten around to worrying about it yet, but as shown, COALESCE (or ISNULL since testing a single condition) would be better.
QUOTE
IIf() is not a statement on its own - you need to assign it to something

I was trying to do that. I think I misused SET, but I am blundering around at this point, discovering some things by accident, but missing others.

It seems IF...ELSE in SQL Server is a little different than in VBA in that the THEN is assumed rather than explicitly stated. I expect over time I will sort out when to use IF...ELSE, CASE, and so forth, but for now I have a better sense of how to proceed with the problem at hand. Thank you for the suggestions and help. Once I have this simple situation sorted out I will have a basis for more complex situations.

Now to install SSMS on my temporary computer so I can give another try to getting this to work.
Go to the top of the page
 
cheekybuddha
post Oct 26 2017, 08:05 AM
Post#10


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


>> I would like to do that, but one of the items being concatenated is conditional. <<

Did you notice the CONCAT() example too? It uses IIF() for the conditional!

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Oct 26 2017, 08:28 AM
Post#11


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


Sorry, I should have been clearer. I noticed CONCAT and IIF in your example. The first IIF example earlier in the thread, by MadPiet, did not use the conditional test, most likely because I did not explain well enough. My "I would like to do that, but..." comment was in reference to the first CONCAT suggestion.
Go to the top of the page
 
cheekybuddha
post Oct 26 2017, 08:50 AM
Post#12


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 02:06 PM