我有SQL Server 2008用户defind功能的问题 [英] I have a problem with SQL server 2008 user defind function
本文介绍了我有SQL Server 2008用户defind功能的问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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屋!
查看全文