CODEWITHSUNDEEP
sql-serversql-server-2008function
Mathematics
Mathematics

Reputation: 7628

Issue creating function with a "with"

I am trying to create this function(variable names changed so allow naming mistakes as when tested original without function works fine),

 CREATE FUNCTION [dbo].[GetQuality](@FruitID VARCHAR(200))
RETURNS varchar(200)
AS
BEGIN
DECLARE @Result varchar(200)

SET @Result = (

WITH 
latest AS
(
    SELECT * FROM (SELECT TOP 1 * FROM Fruits_Crate WHERE FruitID like @FruitID ORDER BY ExpiryDate DESC) a
), 
result AS
(
    SELECT 
        latest.ExpiryDate as LatestExpiryDate, latest.Size as LatestSize, latest.Weight as LatestWeight,
        previous.ExpiryDate as PreviousExpiryDate, previous.Size as PreviousSize, previous.Weight as PreviousWeight,
        CASE SIGN((latest.Weight * latest.Size) - (previous.Weight * previous.Size))
            WHEN 1 THEN 'Increased'
            WHEN 0 THEN 'Static'
            WHEN -1 THEN 'Decreased'
            ELSE 'No Previous Data'
        END AS Movement 
    FROM (SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Fruits_Crate WHERE FruitID like @FruitID ORDER BY ExpiryDate DESC) x  ORDER BY ExpiryDate) previous
    FULL OUTER JOIN latest ON previous.FruitID = latest.FruitID
)

SELECT result.Movement AS FruitMovement from result;
)

RETURN @Result

END

Error

Msg 156, Level 15, State 1, Procedure GetQuality, Line 10 Incorrect syntax near the keyword 'WITH'.

Msg 319, Level 15, State 1, Procedure GetQuality, Line 10 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Procedure GetQuality, Line 14 Incorrect syntax near ','. Msg 102, Level 15, State 1, Procedure GetQuality, Line 31 Incorrect syntax near ')'.

Upvotes: 1

Views: 49

Answers (1)

Leon Bambrick
Leon Bambrick

Reputation: 26306

I think you can move the assignment down, and put a semi colon before the with, and all should be good.

CREATE FUNCTION [dbo].[GetQuality](@FruitID VARCHAR(200))
RETURNS varchar(200)
AS
BEGIN
DECLARE @Result varchar(200);

WITH 
latest AS
(
    SELECT * FROM (SELECT TOP 1 * FROM Fruits_Crate WHERE FruitID like @FruitID ORDER BY ExpiryDate DESC) a
), 
result AS
(
    SELECT 
        latest.ExpiryDate as LatestExpiryDate, latest.Size as LatestSize, latest.Weight as LatestWeight,
        previous.ExpiryDate as PreviousExpiryDate, previous.Size as PreviousSize, previous.Weight as PreviousWeight,
        CASE SIGN((latest.Weight * latest.Size) - (previous.Weight * previous.Size))
            WHEN 1 THEN 'Increased'
            WHEN 0 THEN 'Static'
            WHEN -1 THEN 'Decreased'
            ELSE 'No Previous Data'
        END AS Movement 
    FROM (SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Fruits_Crate WHERE FruitID like @FruitID ORDER BY ExpiryDate DESC) x  ORDER BY ExpiryDate) previous
    FULL OUTER JOIN latest ON previous.FruitID = latest.FruitID
)

SELECT @RESULT = result.Movement AS FruitMovement from result;


RETURN @Result

END

Upvotes: 4

Related Questions