我想在SQL Server中创建一个函数我创建了一个临时表，但我想在sqlserver中为下面的代码创建函数 [英] I want to create a function in SQL Server I created a temp table but I want to create function in sqlserver for this below code

问题描述

  DECLARE   @ StartDate   DATETIME  
， @ EndDate   DATETIME ; 
 
  SELECT  
  @ StartDate  = '  20100101'，
  @ EndDate  = GETDATE（） 
   -    convert（varchar（8），GETDATE（），112）;  
 
;   DT_TempTbl  as  
（
  SELECT  
  @ StartDate   AS  YearMonth 
 
  UNION   ALL  
 
  SELECT  
 DATEADD（月， 1 ，YearMonth）
  FROM  
 DT_TempTbl 
  WHERE  
 YearMonth< =  @EndDate  
）
  SELECT  
  CONVERT （ VARCHAR （ 6 ），YearMonth， 112 ） AS  YearMonth1 
  FR OM  
 DT_TempTbl 
  ORDER   BY  
 YearMonth1 <这是创建功能 [ ^ ] 
 
 
 
你需要做的第一件事是将变量@StartDate和@EndDate移出这个SQL，成为函数的参数......例如
  CREATE   FUNCTION  [dbo]。[fnYourFunction] 
（ 
  @ StartDate   DATETIME ，
  @EndDate   DATETIME  
）
然后你需要定义你想要返回的值...在这种情况下你试图从
 SELECT 
 CONVERT（VARCHAR（6），YearMonth，112）返回结果AS YearMonth1 
因此你可以通过函数定义你的函数输出
 RETURNS @output TABLE（yearmonth VARCHAR（6）
 
 
接下来将SQL包含在函数体中，用 BEGIN ... END 并包含 RETURN 声明。
 
 
 
最后，因为你已经定义了输出表，你只需要将查询中的结果添加到表中，例如
  INSERT   INTO   @ output  
 
 
所以整件事变成
  CREATE   FUNCTION  [dbo]。[fnYourFunction] 
（
  @ StartDate   DATETIME ，
  @EndDate   DATETIME  
）
  RETURNS   @ output   TABLE （yearmonth  VARCHAR （ 6 ）
）
  BEGIN  
 使用 DT_TempTbl 作为 
（
  SELECT   @ StartDate   AS  YearMonth 
 
  UNION   ALL  
 
  SELECT  DATEADD（MONTH， 1 ，YearMonth）
  FROM  DT_TempTbl 
  WHERE  YearMonth< =  @ EndDate  
）
  INSERT   INTO   @ output  
  SELECT   CONVERT （ VARCHAR （ 6 ），YearMonth， 112 ） AS  YearMonth1 
  FROM  DT_TempTbl 
  ORDER   BY  YearMonth1 
 
 返回 
  END  
我使用您的测试数据调用它，如下所示
 选择 * 来自 dbo.fnYourFunction（CAST（'  2010-01 -01'  as   DATETIME ），getdate（））
 
 
 
正如我在对CHill60的回答的评论中提到的，内联表值函数对于这个查询来说是更好的选择。 此博客文章 [ ^ ]比较了内联与多语句TVF的性能。
 
 
 
而不是使用递归公用表表达式，这将导致大日期范围出现问题，您可以使用计数表：
 
  SQL Wizardry第八部分 - 理货表 [ ^ ] 
 
 
 
由于您使用的是SQL 2012，因此可以使用EOMONTH  [ ^ ]和 DATEFROMPARTS  [ ^ ]构建日期，并 FORMAT  [ ^ ]格式化日期。
 
 
 
这样的事情可以解决问题：
 
 
  CREATE   FUNCTION  dbo.fnYourFunction（）
  RETURNS   TABLE  
 < span class =code-keyword> As  
 返回 
（
  WITH  E1（N） As  
（
  SELECT   1   UNION   ALL   SELECT   1   UNION   ALL   SELECT   1   UNION   ALL  
  SELECT   1   UNION   ALL   SELECT   1   UNION  所有  SELECT   1   UNION   ALL  
  SELECT   1   UNION   ALL   SELECT   1   UNION   ALL   SELECT   1   UNION  所有 < span class =code-keyword> SELECT   1  
），
 E2（N）作为（ SELECT   1   FROM  E1 a，E1 b），
 E4（N） As （ SELECT   1   FROM  E2 a，E2 b），
 E（N） As （ SELECT   TOP （ 3660 ）RO W_NUMBER（） OVER （ ORDER   BY （  SELECT   NULL ）） FROM  E4）， 
 
 cteMonths  As  
（
  SELECT  
 D.YearMonth 
  FROM  
 E 
   -   < span class =code-comment>查找下个月的第一天： 
  CROSS   APPLY （ VALUES （DateAdd（day， 1 ，EOMONTH（GetDate（）））） ） As  M（EndDate）
   -   减去指定的月数： 
  CROSS   APPLY （ VALUES （DateAdd（month） ， 1   -  EN，M.EndDate））） As  D（YearMonth）
  WHERE  
 D.YearMonth> = DateFromParts（年（GetDate（）） -   10 ， 1 ， 1 ）
）
  SELECT  
 FORMAT（YearMonth，'  yyyyMM'） AS  YearMonth1 
  FROM  
 cteMonths 
）; 
 
 
 注意：您不能在TVF中使用 ORDER BY 。您需要将其包含在调用代码中：
 
 
  SELECT  
 YearMonth1 
  FROM  
 dbo.fnYourFunction（）
  ORDER   BY  
 YearMonth1 
; 
 
 
 
从下面的代码中生成一个月额外但我想要请到当前月份，任何人都可以帮助我吗
 
 
 
 
  DECLARE   @ StartDate   DATETIME  
， @ EndDate   DATETIME ; 
 
  SELECT  
  @ StartDate  = '  20100101'，
  @ EndDate  = GETDATE（） 
   -    convert（varchar（8），GETDATE（），112）;  
 
;   DT_TempTbl  as  
（
  SELECT  
  @ StartDate   AS  YearMonth 
 
  UNION   ALL  
 
  SELECT  
 DATEADD（月， 1 ，YearMonth）
  FROM  
 DT_TempTbl 
  WHERE  
 YearMonth< =  @EndDate  
）
  SELECT  
  CONVERT （ VARCHAR （ 6 ），YearMonth， 112 ） AS  YearMonth1 
  FROM  
 DT_TempTbl 
  ORDER   BY  
 YearMonth1 
 
 
 
DECLARE @StartDate DATETIME
       ,@EndDate DATETIME;

SELECT 
    @StartDate ='20100101',
    @EndDate = GETDATE()
      --convert(varchar(8), GETDATE(),112); 

;With DT_TempTbl as
(
   SELECT 
       @StartDate AS YearMonth

   UNION ALL

   SELECT 
       DATEADD(MONTH, 1, YearMonth) 
   FROM 
       DT_TempTbl
   WHERE 
       YearMonth <= @EndDate
)
SELECT
    CONVERT(VARCHAR(6), YearMonth, 112) AS YearMonth1 
FROM 
    DT_TempTbl 
ORDER BY
    YearMonth1
解决方案
Here is the reference material for CREATE FUNCTION[^]



The first thing you need to do is move your variables @StartDate and @EndDate out of this SQL to become parameters to the function ... e.g. 
CREATE FUNCTION [dbo].[fnYourFunction] 
( 
    @StartDate DATETIME, 
    @EndDate DATETIME
)
Then you need to define the values you want to return ... in this case you're trying to return the results from 
SELECT
    CONVERT(VARCHAR(6), YearMonth, 112) AS YearMonth1
so your output from the function can be defined by 
RETURNS @output TABLE(yearmonth VARCHAR(6)
 

Next include your SQL into the body of the function by surrounding it by BEGIN ... END and including an RETURN statement.



Finally, because you have defined the output table, you just need to add the results from the query into the table e.g. 
INSERT INTO @output


So the whole thing becomes 
CREATE FUNCTION [dbo].[fnYourFunction] 
( 
    @StartDate DATETIME, 
    @EndDate DATETIME
) 
RETURNS @output TABLE(yearmonth VARCHAR(6) 
) 
BEGIN
	With DT_TempTbl as
	(
	   SELECT @StartDate AS YearMonth
 
	   UNION ALL

 	   SELECT DATEADD(MONTH, 1, YearMonth) 
	   FROM DT_TempTbl
	   WHERE  YearMonth <= @EndDate
	)
	INSERT INTO @output
	SELECT CONVERT(VARCHAR(6), YearMonth, 112) AS YearMonth1 
	FROM DT_TempTbl 
	ORDER BY YearMonth1

	RETURN
END
I called it using your test data as follows 
select * from dbo.fnYourFunction(CAST('2010-01-01' as DATETIME), getdate())

As I mentioned in the comments to CHill60's answer, an inline table-valued function would be a better choice for this query. This blog post[^] has a comparison of the performance of inline versus multi-statement TVFs.



Rather than using a recursive common table expression, which will have problems with large date ranges, you can use a tally table:

SQL Wizardry Part Eight - Tally Tables[^]



Since you're using SQL 2012, you can use EOMONTH[^] and DATEFROMPARTS[^] to build the dates, and FORMAT[^] to format the date.



Something like this should do the trick:

CREATE FUNCTION dbo.fnYourFunction()
RETURNS TABLE
As
Return
(
    WITH E1(N) As
    (
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    ),
    E2(N) As (SELECT 1 FROM E1 a, E1 b),
    E4(N) As (SELECT 1 FROM E2 a, E2 b),
    E(N) As (SELECT TOP (3660) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4),

    cteMonths As
    (
        SELECT
            D.YearMonth
        FROM
            E
            -- Find the first day of next month:
            CROSS APPLY (VALUES (DateAdd(day, 1, EOMONTH(GetDate())))) As M (EndDate)
            -- Subtract the specified number of months:
            CROSS APPLY (VALUES (DateAdd(month, 1 - E.N, M.EndDate))) As D (YearMonth)
        WHERE
            D.YearMonth >= DateFromParts(Year(GetDate()) - 10, 1, 1)
    )
    SELECT
        FORMAT(YearMonth, 'yyyyMM') AS YearMonth1
    FROM
        cteMonths 
);


NB: You can't use ORDER BY within a TVF. You need to include it in the calling code:

SELECT 
    YearMonth1 
FROM 
    dbo.fnYourFunction() 
ORDER BY 
    YearMonth1
;

from the below code it generating the one month extra but i want to be to current month only can any one help me on this please



DECLARE @StartDate DATETIME
       ,@EndDate DATETIME;
 
SELECT 
    @StartDate ='20100101',
    @EndDate = GETDATE()
      --convert(varchar(8), GETDATE(),112); 

;With DT_TempTbl as
(
   SELECT 
       @StartDate AS YearMonth
 
   UNION ALL
 
   SELECT 
       DATEADD(MONTH, 1, YearMonth) 
   FROM 
       DT_TempTbl
   WHERE 
       YearMonth <= @EndDate
)
SELECT
    CONVERT(VARCHAR(6), YearMonth, 112) AS YearMonth1 
FROM 
    DT_TempTbl 
ORDER BY
    YearMonth1


                        
这篇关于我想在SQL Server中创建一个函数我创建了一个临时表，但我想在sqlserver中为下面的代码创建函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！