A RETURN statement with a return value cannot be used in this context [closed]

Posted on

Question :

I’m using MS SQL and I’m trying to create a function to use later in a query:

CREATE FUNCTION "DiasLaborales"(
    "@month" INT,
    "@year" INT
)
RETURNS INT
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN 

DECLARE @day INT = 1
DECLARE @varfecha DATETIME 
DECLARE @diaslaborales INT 

SET @diaslaborales = 0 
SET @varfecha = CAST(RTRIM(@year * 10000 + @month * 100 + @day) AS DATETIME)
SET @fechafinal = DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, @varfecha))), DATEADD(MONTH, 1, @varfecha))
WHILE ((@fechafinal + 1) > @varfecha) 
BEGIN 
IF (DATEPART(dw,@varfecha) NOT IN (6,7)) 
BEGIN 
SET @diaslaborales = @diaslaborales +1 
END 
SET @varfecha = @varfecha + 1 
END 
RETURN @diaslaborales 
END 

However, I get this error message:

SQL ERROR (178): Incorrect syntax near ‘@month’ A RETURN statement with a
return value cannot be used in this context.

I don’t understand that message.

Answer :

The problems you have in your function:

  • Take the double quotes off your parameter declarations
  • You’re not declaring @fechafinal anywhere
  • All of the stuff you have between RETURNS INT and BEGIN isn’t valid in SQL Server.

This successfully compiles for me.

CREATE FUNCTION "DiasLaborales" ( @month INT, @year INT )
RETURNS INT
    BEGIN

        DECLARE @day INT = 1;
        DECLARE @varfecha DATETIME;
        DECLARE @diaslaborales INT;
        DECLARE @fechafinal DATETIME;

        SET @diaslaborales = 0;
        SET @varfecha = CAST(RTRIM(@year * 10000 + @month * 100 + @day)
AS      DATETIME);
        SET @fechafinal = DATEADD(DAY, - ( DAY(DATEADD(MONTH, 1, @varfecha))), DATEADD(MONTH, 1, @varfecha));
        WHILE (( @fechafinal + 1 ) > @varfecha )
            BEGIN
                IF ( DATEPART(dw, @varfecha) NOT IN (6, 7))
                    BEGIN
                        SET @diaslaborales = @diaslaborales + 1;
                    END;
                SET @varfecha = @varfecha + 1;
            END;
        RETURN @diaslaborales;
    END;

Just make sure you understand how disastrous Scalar Valued Functions are in SQL Server for performance.

Leave a Reply

Your email address will not be published.