时间戳

首页 > 解决方案 > 将 SQL 过程转换为函数

sql-server - 将 SQL 过程转换为函数

问题描述

create function fiyathesapla(@StartDate datetime2(5),@EndDate datetime2(5),@kodu nvarchar(max))
returns table
as
return (

WITH theDates AS
     (SELECT @StartDate as theDate
      UNION ALL
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE DATEADD(day, 1, theDate) <= @EndDate
     )
SELECT SUM( v_period.fiyat-((v_period.fiyat/100)*v_period.indirim))  FROM theDates,v_period  WHERE tarih1<=theDates.theDate and tarih2>=theDates.theDate  and  vkodu=@kodu
)
go

- -错误代码 - -

消息 4514，级别 16，状态 1，过程 fiyathesapla，第 6 行 [批处理开始第 0 行] CREATE FUNCTION 失败，因为未为第 1 列指定列名。

尝试创建函数时出现此错误。它有助于通过逐天计算两个输入的日期来计算价格。但是当我创建存储过程时它可以工作。我想创建一个函数，我该如何编辑它。

---正常工作的程序代码---

USE [villapaket1]
GO
/****** Object:  StoredProcedure [dbo].[FN_fiyat]    Script Date: 30.09.2020 16:18:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[FN_fiyat] @StartDate datetime2(5),@EndDate datetime2(5),@kodu nvarchar(max)
AS

WITH theDates AS
    (SELECT @StartDate as theDate
     UNION ALL
     SELECT DATEADD(day, 1, theDate)
       FROM theDates
      WHERE DATEADD(day, 1, theDate) <= @EndDate
    )
SELECT SUM( v_period.fiyat-((v_period.fiyat/100)*v_period.indirim))  FROM theDates,v_period  WHERE tarih1<=theDates.theDate and tarih2>=theDates.theDate  and  vkodu=@kodu

标签： sql-servertsqlstored-functions

解决方案

看起来数据库引擎告诉您SUM( v_period.fiyat-((v_period.fiyat/100)*v_period.indirim))（=最终选择的第一列）需要一个别名。不需要在函数外部显示结果，但需要返回具有有效列名的表。

在下面和这个小提琴中使用小数据集和简单（但相似）的功能进行问题再现。

样本数据

create table MyTable
(
  id int
);

insert into MyTable (id) values
(100), (101), (102);

选择

with cte as
(
  select id-100 as NewId from MyTable
)
select sum(cte.NewId) --> no column alias, works fine (result table has unnamed column)
from cte;

功能

create function MyFunction()
returns table
as return
(
  with cte as
  (
    select id-100 as NewId from MyTable
  )
  select sum(cte.NewId) --> no column alias, does NOT work
  from cte
);

CREATE FUNCTION 失败，因为没有为列 1 指定列名。

更新到工作功能：

create function MyFunction()
returns table
as return
(
  with cte as
  (
    select id-100 as NewId from MyTable
  )
  select sum(cte.NewId) as IdSum --> with column alias
  from cte
);

推荐阅读