CODEWITHSUNDEEP
sql-servert-sqlstored-functions
Melissa
Melissa

Reputation: 21

Create Function SQL-Server Incorrect syntax

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]

Upvotes: 1

Views: 784

Answers (4)

Matstar
Matstar

Reputation: 410

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

Upvotes: 0

Chanukya
Chanukya

Reputation: 5873

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]

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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]

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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];

Upvotes: 2

Related Questions