Блог

Я создаю таблицу и хочу заполнить эту таблицу данными из функции.Вот как выглядит код для таблицы :

 CREATE TABLE dbo.CustomerReportLogs1 (
    ID int IDENTITY (1,1) NOT NULL,
    CustomerId int NOT NULL,
    validFrom date NOT NULL,
    ValidTo date NOT NULL,
    EmployeeID int NOT NULL,
    CONSTRAINT PK_ID_CustomerReportLogs1 PRIMARY KEY CLUSTERED (
    ID ASC
    ))
    Go
  

Итак, следующим шагом должно быть заполнение таблицы данными из процедуры. Итак, сначала я делаю запрос, чтобы проверить, хорошо ли работает эта функция, и после этого я продолжаю процедуру.Ниже вы можете увидеть, как выглядит запрос

 CREATE OR  ALTER FUNCTION dbo.fn_Home_1 (@CustomerId int, @validFrom date, @ValidTo date, @EmployeeId int)
RETURNS @ResultSet table (CustomerFullName NVARCHAR(100), LocationName NVARCHAR(100),Amount decimal(18,2),Currency NVARCHAR(100),EmployeeId int)
AS
BEGIN
INSERT INTO @ResultSet(CustomerFullName,LocationName,Amount,Currency,EmployeeId)
SELECT  CONCAT(c.FirstName, ' ', c.LastName) AS CustomerFullName, lo.Name as LocationName,acd.Amount,cu.Name as Currency,acc.EmployeeId as Employee_ID
FROM dbo.Customer c
INNER JOIN dbo.Account AS acc ON acc.CurrencyId=c.Id
INNER JOIN dbo.AccountDetails AS acd ON acd.AccountId=acc.Id
INNER JOIN dbo.Currency AS cu ON cu.id=acc.CurrencyId
INNER JOIN dbo.Location as lo ON lo.Id=acd.LocationId
INNER JOIN dbo.Employee AS emp ON emp.ID=acc.EmployeeId
WHERE acc.CustomerId=@CustomerId and acd.TransactionDate between @validFrom and @ValidTo and acc.EmployeeId=@EmployeeId
RETURN
END
GO

SELECT * from dbo.fn_Home_1  (5, '2019.01.01', '2019.12.01',5)
GO
  

Этот запрос дает мне хороший результат, и последний шаг — ввести функцию, которая может заполнить таблицу выше.

 CREATE OR  ALTER FUNCTION dbo.fn_Homework_1 (@CustomerId int, @validFrom date, @ValidTo date, @EmployeeId int)
RETURNS @ResultSet table (CustomerFullName NVARCHAR(100), LocationName NVARCHAR(100),Amount decimal(18,2),Currency NVARCHAR(100),EmployeeId int)
AS
BEGIN
SELECT  CONCAT(c.FirstName, ' ', c.LastName) AS CustomerFullName, lo.Name as LocationName,acd.Amount,cu.Name as Currency,acc.EmployeeId as Employee_ID
            FROM dbo.Customer c
            INNER JOIN dbo.Account AS acc ON acc.CurrencyId=c.Id
            INNER JOIN dbo.AccountDetails AS acd ON acd.AccountId=acc.Id
            INNER JOIN dbo.Currency AS cu ON cu.id=acc.CurrencyId
            INNER JOIN dbo.Location as lo ON lo.Id=acd.LocationId
            INNER JOIN dbo.Employee AS emp ON emp.ID=acc.EmployeeId
            WHERE acc.CustomerId=@CustomerId and acd.TransactionDate between @validFrom and @ValidTo and acc.EmployeeId=@EmployeeId
--DECLARE @validFrom date
SELECT @CustomerId,@validFrom,@ValidTo,@EmployeeId
INSERT INTO dbo.CustomerReportLogs1(CustomerId,validFrom,ValidTo,EmployeeId)
VALUES (@CustomerId,@validFrom,@ValidTo,@EmployeeId)
Return
END
GO

Error message is below:
Msg 444, Level 16, State 2, Procedure fn_HomeworkFour_1, Line 5 [Batch Start Line 3798]
Select statements included within a function cannot return data to a client.
Msg 444, Level 16, State 3, Procedure fn_HomeworkFour_1, Line 14 [Batch Start Line 3798]
Select statements included within a function cannot return data to a client.
Msg 443, Level 16, State 15, Procedure fn_HomeworkFour_1, Line 15 [Batch Start Line 3798]
Invalid use of a side-effecting operator 'INSERT' within a function.
  

Итак, кто-нибудь может мне помочь, как исправить эту ошибку? Выходные столбцы должны быть: CustomerID ,ValidFrom, ValidTo и EmployeeID ?