Filling the table with function

313 Views Asked by At

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 ?

0

There are 0 best solutions below