在SQL Server中为不早于14的日期创建用户定义的函数

Question

不知道如何创建这个UDF。我已经通读了现有的问题，但只是不确定语法。

我想把这个查询保存为视图，但不能，因为我已经有声明了

DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
SELECT @ENDDATE = CAST(CONVERT(CHAR(10), GETDATE(), 101)+' 00:00:00' AS DATETIME)
SELECT @STARTDATE = DATEADD(D, -1, @ENDDATE)--'-7'
SELECT @ENDDATE = CAST(CONVERT(CHAR(10), GETDATE(), 101)+' 23:59:59' AS DATETIME)
SELECT
    LTRIM(CIJ.ORDERACCOUNT) 'ACCOUNT'
    ,LTRIM(CIJ.SALESID) 'TOMKIN ORDER #'
    ,LTRIM(CIJ.INVOICEID) 'TOMKIN INVOICE #'
    ,CIJ.PURCHASEORDER 'YOUR ORDER #'
FROM 
    CUSTINVOICEJOUR CIJ
WHERE 
    CIJ.INVOICEDATE >= @STARTDATE AND CIJ.INVOICEDATE <= @ENDDATE
ORDER BY 
    CIJ.INVOICEDATE,CIJ.INVOICEID

如有任何帮助，我们将不胜感激

Answer 1

以下是转换为视图的查询。请注意，不能将参数传递到视图中，也不能指定顺序。

CREATE VIEW Makdaddy ([ACCOUNT], [TOMKIN ORDER #], [TOMKIN INVOICE #], [YOUR ORDER #])
AS
SELECT
     LTRIM(CIJ.ORDERACCOUNT) 'ACCOUNT'
   , LTRIM(CIJ.SALESID) 'TOMKIN ORDER #'
   , LTRIM(CIJ.INVOICEID) 'TOMKIN INVOICE #'
   , CIJ.PURCHASEORDER 'YOUR ORDER #'
FROM CUSTINVOICEJOUR CIJ
WHERE CIJ.INVOICEDATE >= DATEADD(day,-14,GETDATE())
  AND CIJ.INVOICEDATE <= GETDATE()

调用视图时，可以对列进行排序并执行其他筛选。

SELECT *
FROM Makdaddt
ORDER BY [ACCOUNT]

这是您转换为udf的查询。这可能是一个更好的方法，因为你的开始日期可能会改变。

CREATE FUNCTION dbo.Makdaddy (@StartDate DATETIME)
RETURNS TABLE
AS
RETURN 
(
  SELECT
       LTRIM(CIJ.ORDERACCOUNT) 'ACCOUNT'
     , LTRIM(CIJ.SALESID) 'TOMKIN ORDER #'
     ,  LTRIM(CIJ.INVOICEID) 'TOMKIN INVOICE #'
     , CIJ.PURCHASEORDER 'YOUR ORDER #'
  FROM CUSTINVOICEJOUR CIJ
  WHERE CIJ.INVOICEDATE >= @StartDate
    AND CIJ.INVOICEDATE <= GETDATE()
);