时间戳

首页 > 解决方案 > 在虚拟表错误中使用 Sql 函数

sql - 在虚拟表错误中使用 Sql 函数

问题描述

        CREATE FUNCTION dbo.Get_LogoFaturaIskonto (
        @InvoiceRef     int = 0,
        @ParentlnRef    int = 0,
        @Rownumber      int = 0
    )
    RETURNS decimal(18,2)
    AS
    BEGIN
        DECLARE @VALUE decimal(18,2)
        WITH NEWTABLE AS 
        (SELECT ROW_NUMBER() OVER(ORDER BY LOGICALREF ASC) AS ROWNUMBER#
      ,DISCPER,LOGICALREF,INVOICEREF,PARENTLNREF FROM dbo.LG_002_01_STLINE 
    WHERE 
       INVOICEREF = @InvoiceRef AND PARENTLNREF = @ParentlnRef AND LINETYPE = 2)
        SELECT @VALUE = (SELECT * FROM NEWTABLE WHERE ROWNUMBER = @Rownumber)
        RETURN ISNULL(@VALUE,0)
    END

错误信息

消息 319，级别 15，状态 1，过程 Get_LogoFaturaIskonto，第 11 行关键字“with”附近的语法不正确。如果此语句是公用表表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前面的语句必须以分号结束。

标签： sqlsql-server

解决方案

至少缺少分号：

CREATE FUNCTION dbo.Get_LogoFaturaIskonto (
    @InvoiceRef     int = 0,
    @ParentlnRef    int = 0,
    @Rownumber      int = 0
)
RETURNS decimal(18,2)
AS
BEGIN
    DECLARE @VALUE decimal(18,2);

    WITH NEWTABLE AS 
    (SELECT ROW_NUMBER() OVER(ORDER BY LOGICALREF ASC) AS ROWNUMBER#
  ,DISCPER,LOGICALREF,INVOICEREF,PARENTLNREF FROM dbo.LG_002_01_STLINE 
WHERE 
   INVOICEREF = @InvoiceRef AND PARENTLNREF = @ParentlnRef AND LINETYPE = 2)
    SELECT @VALUE = (SELECT * FROM NEWTABLE WHERE ROWNUMBER = @Rownumber)
    RETURN ISNULL(@VALUE,0)
END

推荐阅读