我有SQL Server 2008用户defind功能的问题 [英] I have a problem with SQL server 2008 user defind function

问题描述

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用户定义功能



我是什么尝试过：

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)

推荐答案

如果我理解正确的你我正在尝试，你将获得所有月份名称，直到分月。开始的月份似乎没有包含在集合中。



如果这是正确的，我认为你可以简化T-SQL代码。



请考虑以下事项。据我所见，它满足要求：

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:

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;

这篇关于我有SQL Server 2008用户defind功能的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！