Create Function SQL-Server Incorrect syntax

Question

I am new to SQL Sg server and am trying to run code using the create function to convert a price fro USD to decimal. I'm getting the following flag

"Incorrect Syntax: 'Create Function' must be the only statement in the batch.

Can anyone point out what I'm dong wrong? Thank you!

CREATE FUNCTION week3.ConvertToEuro ( @priceUSD decimal(5,2))
returns decimal (5,2)

begin
        declare @PriceEuro decimal (5,2)
        set @PriceEuro =(@priceUSD * .89)
        return @PriceEuro

        end 

SELECT TOP (1000) [ProductName]
      ,[price]
      ,week3.ConvertToEuro(price) as PriceEuro
  FROM [AA5100_edmistonml].[Week3].[productprice]

Unless you're using SQL Server 2019 (and making use of inline scalar functionality), I would recommend an inline table-value function. Scalar function can be slow. — Thom A
– Thom A ♦, Commented Apr 5, 2019 at 11:25
Exchange rate is likely to change, so better to make it a parameter (or query from up-to-date table). — HoneyBadger
– HoneyBadger, Commented Apr 5, 2019 at 11:27
@Larnu. . . Side note SQL Server 2019 is not yet released. — Yogesh Sharma
– Yogesh Sharma, Commented Apr 5, 2019 at 11:29
@YogeshSharma it's now in CTP 2.4, and (the preview) has been publically available for some time. Of course, that's why I stated "unless you're using 2019"; with the high likelihood they are not (especically as 2.4 is now only available as an evaluation version, and not developement/express). — Thom A
– Thom A ♦, Commented Apr 5, 2019 at 11:33

Yogesh Sharma · Accepted Answer · 2019-04-05 11:23:42Z

2

Both statements are different you either run separate or put GO :

CREATE FUNCTION week3.ConvertToEuro ( @priceUSD decimal(5,2))
returns decimal (5,2)
begin
        declare @PriceEuro decimal (5,2)
        set @PriceEuro =(@priceUSD * .89)
        return @PriceEuro
end 

GO

SELECT TOP (1000) [ProductName], [price], week3.ConvertToEuro(price) as PriceEuro
FROM [AA5100_edmistonml].[Week3].[productprice];

answered Apr 5, 2019 at 11:23

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2019-04-05 11:25:08Z

1

try like below

CREATE FUNCTION week3.ConvertToEuro ( @priceUSD decimal(5,2))
returns decimal (5,2)
as -- add this 
begin
        declare @PriceEuro decimal (5,2)
        set @PriceEuro =(@priceUSD * .89)
        return @PriceEuro

        end 

go -- add this
SELECT TOP (1000) [ProductName]
      ,[price]
      ,week3.ConvertToEuro(price) as PriceEuro
  FROM [AA5100_edmistonml].[Week3].[productprice]

answered Apr 5, 2019 at 11:25

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

1 Comment

GMB Over a year ago

Hey Zaynul... Congratulations for being awarded the sql gold badge today... that's a great achievement, cheers!

Chanukya · Accepted Answer · 2019-04-05 11:25:36Z

1

Try by creating as statement and add go between statements

CREATE FUNCTION week3.ConvertToEuro ( @priceUSD decimal(5,2))
returns decimal (5,2)
as  
begin
        declare @PriceEuro decimal (5,2)
        set @PriceEuro =(@priceUSD * .89)
        return @PriceEuro

        end 

go  
SELECT TOP (1000) [ProductName]
      ,[price]
      ,week3.ConvertToEuro(price) as PriceEuro
  FROM [AA5100_edmistonml].[Week3].[productprice]

answered Apr 5, 2019 at 11:25

Chanukya

5,8911 gold badge24 silver badges39 bronze badges

Comments

Matstar · Accepted Answer · 2024-05-02 01:56:03Z

0

My fix was to put GO on first and last line

GO 
CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN ( SELECT [Value] FROM 
      ( 
        SELECT 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
      ) AS y
    );
GO

answered May 2, 2024 at 1:56

Matstar

4103 silver badges6 bronze badges

Collectives™ on Stack Overflow

Create Function SQL-Server Incorrect syntax

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related