CODEWITHSUNDEEP
sqlsql-serverdatesql-function
Tereen Prasanga
Tereen Prasanga

Reputation: 27

In this sql functionI i got some errors in sql server

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

Upvotes: 0

Views: 48

Answers (1)

Peter B
Peter B

Reputation: 24202

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:

  1. The inner SELECT will return either a single row, or no rows.
  2. 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

Upvotes: 1

Related Questions