Click here to Skip to main content
15,665,480 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have a problem w<pre><pre>-- create FUNCTION [dbo].[GET_MONTH]
--(
--    @MONTH_NAME VARCHAR(max), @SPLIT_MOTNH VARCHAR(MAX)
--    )
--RETURNS VARCHAR(max)
--AS
--BEGIN
DECLARE @MONTH_NAME VARCHAR(max);
DECLARE @SPLIT_MOTNH VARCHAR(max);

SET @MONTH_NAME = 'Nov';   -- on value  'Dec' , 'Jan'  return NULL, why
SET @SPLIT_MOTNH = 'MAR';
if((@MONTH_NAME != '....' AND @SPLIT_MOTNH!='....')or (@MONTH_NAME != '' AND @SPLIT_MOTNH!='') or (@MONTH_NAME != '..' AND @SPLIT_MOTNH!='..') )
begin
    DECLARE @MAIN VARCHAR(max);
    DECLARE @Names VARCHAR(max);
    DECLARE @1Names VARCHAR(max);
    DECLARE @RETURN VARCHAR(max);
    DECLARE @FINAL VARCHAR(max);
    declare @NoOfMonth int;
    IF(@MONTH_NAME = 'AD /Reg')
    BEGIN
    SET @MONTH_NAME='MAR';
    End   
    IF(@MONTH_NAME != 'AD /Reg')
    BEGIN
    SELECT @Names =  COALESCE(@Names + ', ', '') + [MName] FROM [CalendarMonth] where Mno > 
    MONTH(isnull(CONVERT(VARCHAR,RIGHT(RTRIM(@MONTH_NAME), 3)),'APR')+ ' 1 2014')     
    SELECT @1Names =  COALESCE(@1Names + ', ', '') + [MName] FROM [CalendarMonth] where Mno < 
    MONTH(isnull(CONVERT(VARCHAR,RIGHT(RTRIM(@MONTH_NAME), 3)),'APR')+ ' 1 2014') AND Mno < 4   
       
    SELECT @RETURN = @Names +' , '+ @1Names;
    END
   
    ELSE
    BEGIN
    SELECT @Names = 'APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB, MAR'
      
    END
     SELECT @FINAL = SUBSTRING(@RETURN, 0, Charindex(@SPLIT_MOTNH,@RETURN))  + @SPLIT_MOTNH
     
     select @NoOfMonth=  MONTH(isnull(CONVERT(VARCHAR,RIGHT(RTRIM(@FINAL), 3)),'APR')+ ' 1 2014')  /3      
     
     SELECT  @FINAL   
 End     
 -- else
 --   select @FINAL='0'
      
    -- return @FINAL

 --END
 
 --SELECT  dbo.GET_MONTH('Oct','FEB')
ith sql server 2008 user defind function

What I have tried:

-- create FUNCTION [dbo].[GET_MONTH]
--(
--    @MONTH_NAME VARCHAR(max), @SPLIT_MOTNH VARCHAR(MAX)
--    )
--RETURNS VARCHAR(max)
--AS
--BEGIN
DECLARE @MONTH_NAME VARCHAR(max);
DECLARE @SPLIT_MOTNH VARCHAR(max);

SET @MONTH_NAME = 'Nov';   -- on value  'Dec' , 'Jan'  return NULL, why
SET @SPLIT_MOTNH = 'MAR';
if((@MONTH_NAME != '....' AND @SPLIT_MOTNH!='....')or (@MONTH_NAME != '' AND @SPLIT_MOTNH!='') or (@MONTH_NAME != '..' AND @SPLIT_MOTNH!='..') )
begin
    DECLARE @MAIN VARCHAR(max);
    DECLARE @Names VARCHAR(max);
    DECLARE @1Names VARCHAR(max);
    DECLARE @RETURN VARCHAR(max);
    DECLARE @FINAL VARCHAR(max);
    declare @NoOfMonth int;
    IF(@MONTH_NAME = 'AD /Reg')
    BEGIN
    SET @MONTH_NAME='MAR';
    End   
    IF(@MONTH_NAME != 'AD /Reg')
    BEGIN
    SELECT @Names =  COALESCE(@Names + ', ', '') + [MName] FROM [CalendarMonth] where Mno > 
    MONTH(isnull(CONVERT(VARCHAR,RIGHT(RTRIM(@MONTH_NAME), 3)),'APR')+ ' 1 2014')     
    SELECT @1Names =  COALESCE(@1Names + ', ', '') + [MName] FROM [CalendarMonth] where Mno < 
    MONTH(isnull(CONVERT(VARCHAR,RIGHT(RTRIM(@MONTH_NAME), 3)),'APR')+ ' 1 2014') AND Mno < 4   
       
    SELECT @RETURN = @Names +' , '+ @1Names;
    END
   
    ELSE
    BEGIN
    SELECT @Names = 'APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB, MAR'
      
    END
     SELECT @FINAL = SUBSTRING(@RETURN, 0, Charindex(@SPLIT_MOTNH,@RETURN))  + @SPLIT_MOTNH
     
     select @NoOfMonth=  MONTH(isnull(CONVERT(VARCHAR,RIGHT(RTRIM(@FINAL), 3)),'APR')+ ' 1 2014')  /3      
     
     SELECT  @FINAL   
 End     
 -- else
 --   select @FINAL='0'
      
    -- return @FINAL

 --END
 
 --SELECT  dbo.GET_MONTH('Oct','FEB')
 
--------------------------CalendarMonth table-------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CalendarMonth](
    [MName] [varchar](9) NULL,
    [Mno] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [Mno] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'JAN', 1)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'FEB', 2)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'MAR', 3)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'APR', 4)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'MAY', 5)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'JUN', 6)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'JUL', 7)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'AUG', 8)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'SEP', 9)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'OCT', 10)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'NOV', 11)
INSERT [dbo].[CalendarMonth] ([MName], [Mno]) VALUES (N'DEC', 12)
Posted
Updated 15-Jan-17 6:51am
Comments
Tomas Takac 15-Jan-17 8:37am    
And what exactly is the problem?
Wendelius 15-Jan-17 9:03am    
If you get an error, post the error details. If you have a logical problem, post details about it and so on.

1 solution

If I understand correctly what you're trying, you're fetching all thew month names until the split month. The starting month doesn't seem to be included in the set.

If this is correct, I think you could simplify the T-SQL code.

Consider the following. As far as I can see, it satisfies the requirements:
SQL
DECLARE @MONTH_NAME VARCHAR(max);
DECLARE @SPLIT_MONTH VARCHAR(max);

SET @MONTH_NAME = 'Dec';   
SET @SPLIT_MONTH = 'MAR';

BEGIN
   DECLARE @Names VARCHAR(max);
   DECLARE @Date DATE;

   SET @Date = CONVERT(date, '01 ' + @MONTH_NAME + ' 00', 6);
   SET @Names = '';
   WHILE 1=1 BEGIN
      IF @Names != '' BEGIN
	     SET @Names = @Names + ', ';
	  END;
	  SET @Date = DATEADD(month, 1, @date);
      SET @Names = @Names + LEFT( DATENAME(month, @date), 3);
	  IF LEFT( DATENAME(month, @date), 3) = @SPLIT_MONTH BEGIN
	     BREAK;
	  END;
	END

    SELECT @Names;
END;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900