I have a problem with SQL server 2008 user defind function

Question

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 15-Jan-17 2:17am

Member 11346278

Updated 15-Jan-17 6:51am

Add a Solution

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

Add a Solution

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

Wendelius · Answer 1 · 2017-01-15T06:51:00

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;

I have a problem with SQL server 2008 user defind function

1 solution

Solution 1

Add your solution here

Preview 0

I have a problem with SQL server 2008 user defind function

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us