In this sql functionI i got some errors in sql server

Question

I want to return if there is a record in my table then return it and if that table haven't a record,then return 'America/Chicago'.

Error: Msg 102, Level 15, State 1, Procedure ufn_GetGCMoodleTimeZone, Line 18 [Batch Start Line 7] Incorrect syntax near '@MoodleTimeZone'. Msg 102, Level 15, State 1, Procedure ufn_GetGCMoodleTimeZone, Line 22 [Batch Start Line 7] Incorrect syntax near 'END'.

ALTER FUNCTION [dbo].[ufn_GetGCMoodleTimeZone]
 ( @HSATimeZone VARCHAR(250) )RETURNS varchar(250)
AS
  BEGIN
    DECLARE @MoodleTimeZone varchar(250) 
    SELECT @MoodleTimeZone = GCMoodleTimeZoneMap.MoodleTimeZone
    FROM GCMoodleTimeZoneMap
    WHERE GCMoodleTimeZoneMap.HSATimeZone = @HSATimeZone
    IF @MoodleTimeZone='NULL'
    BEGIN
            @MoodleTimeZone='America/Chicago'
    END 
    RETURN @MoodleTimeZone

  END

@MoodleTimeZone='America/Chicago' --> SET @MoodleTimeZone='America/Chicago'. Also, @MoodleTimeZone='NULL' should be @MoodleTimeZone IS NULL'. You could easily rewrite this to a table valued function to increase performance (and readability). — HoneyBadger
– HoneyBadger, Commented Jun 19, 2020 at 8:25

Peter B · Accepted Answer · 2020-06-19 09:07:28Z

1

The comment about using SET and how to check for NULL are correct, so I will not repeat that. Still what you want can be written shorter and more efficiently as follows:

ALTER FUNCTION [dbo].[ufn_GetGCMoodleTimeZone]
 ( @HSATimeZone VARCHAR(250) )RETURNS varchar(250)
AS
BEGIN
  RETURN ISNULL(
      (SELECT TOP 1 MoodleTimeZone
       FROM   GCMoodleTimeZoneMap
       WHERE  HSATimeZone = @HSATimeZone
      ), 'America/Chicago'
    )
END

Explanation:

The inner SELECT will return either a single row, or no rows.
The ISNULL() will process that result, and in the "no rows"-case it will substitute the default value 'America/Chicago'.

Here is a working DB Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f4c9e946475818e538ed8f63160d6971

answered Jun 19, 2020 at 9:07

Peter B

24.7k5 gold badges42 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

In this sql functionI i got some errors in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related