I create table and I want to fill that table, with data from function.This is how look like code for table :
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
So next step should filling table with data from procedure. So first I make query in order to see does this function work well and after that I continue with procedure.Below you can see how is look like query
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
This query give me good result and final step is to put into function which can populate table above.
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.
So can anybody help me how to fix this error? Output columns should be: CustomerId ,validFrom, ValidTo and EmployeeID ?