[RESOLVED]Function in Stored Procedure – to find the month name between two dates

Hi, I am trying the function to the month name between two Dates. If I am directly pass the value it is working fine.

DECLARE @yy varchar(4) = ’2014′;

select a.months+’ – ‘+a.myear [mmyy]

FROM (
VALUES
(1,’January’,@yy),(2,’February’,@yy),(3,’March’,@yy),(4,’April’,@yy),
(5,’May’,@yy),(6,’June’,@yy),(7,’July’,@yy),(8,’August’,@yy),
(9,’September’,@yy),(10,’October’,@yy),(11,’November’,@yy),(12,’December’,@yy)
) a(number,months,myear)
WHERE a.number >= DATEPART(month,’2014/01/01′)
AND a.number <= DATEPART(month,’2014/11/04′)

BUT If I am creating a function passing the parameters (Start Date & End Date), it is giving error.

Pl find the source given below.

SP – Error coming

IF OBJECT_ID(‘GetnMonths’,’FN’) IS NOT NULL
DROP FUNCTION GetnMonths;
GO
CREATE FUNCTION GetnMonths
(
@startdate DATE,
@endate DATE
)
RETURNS VARCHAR(35)
AS
BEGIN
DECLARE @yy varchar(4) = ’2014′;
DECLARE @sql VARCHAR(2000);

select a.months+’ – ‘+a.myear [mmyy]
FROM (
VALUES
(1,’January’,@yy),(2,’February’,@yy),(3,’March’,@yy),(4,’April’,@yy),
(5,’May’,@yy),(6,’June’,@yy),(7,’July’,@yy),(8,’August’,@yy),
(9,’September’,@yy),(10,’October’,@yy),(11,’November’,@yy),(12,’December’,@yy)
) a(number,months,myear)
 WHERE a.number >= DATEPART(month,@startdate)
 AND a.number <= DATEPART(month,@endate)

RETURN mmyy
END
GO

Pl suggest.

You need to read about syntax rules for SPs. 

DECLARE @yy varchar(4) = ’2014′; 

is wrong, must be

DECLARE @yy varchar(4)
SET @yy = ’2014′

————

RETURN mmyy

is wrong because there is no variable mmyy and you cannot return it like that. You need to use DECLARE – syntax.

etc.

Hi Uma Sengoden,

Thanks for your post.

As for your problem, I agree with smirnov. You should pay attention to the SQL syntax rules, we use DECLARE statement to declare variable and use either a SET or SELECT statement to assign value.

DECLARE @yy varchar(4)
SET @yy = '2014'

For more information about how to declare variable and assign value, you could refer to the following link.

Best Regards,

Fei Han

Leave a Reply