Hi everyone,
I am trying to do a function on SQL that executes a procedure that i have already coded. But i'm having the following error: "Invalid use of a side-effecting operator 'INSERT EXEC' within a function."
Can you please help me find the way to solve it?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SBSS_Saldos em Aberto]
(@RECON NVARCHAR(1),
@DtStart DATE,
@DtEnd DATE)
RETURNS @RT_ResultFunction TABLE
(
[CardCode] nvarchar(50), [CardName] nvarchar(200), [DataLançamento] Date, [DataVencimento] Date,
[Ref2] nvarchar(100), [NrDocumento] INT, [Detalhes] nVarchar(200), [Debito] Numeric(19,6), [Credito] Numeric(19,6),
[C/D] Numeric(19,6), [SaldoDevido] Numeric(19,6), [SaldoAcumulado] Numeric(19,6)
)
AS
BEGIN
DECLARE @Temp2 table ([CardCode] nvarchar(50), [CardName] nvarchar(200), [DataLançamento] Date, [DataVencimento] Date,
[Ref2] nvarchar(100), [NrDocumento] INT, [Detalhes] nVarchar(200), [Debito] Numeric(19,6), [Credito] Numeric(19,6),
[C/D] Numeric(19,6), [SaldoDevido] Numeric(19,6), [SaldoAcumulado] Numeric(19,6));
DECLARE @q NVARCHAR(4000);
DECLARE @CardCode NVARCHAR(20);
DECLARE @ErrorDesc NVARCHAR(254);
SET @q = 'EXEC [dbo].[SBSS_ContaCorrente] @CardCode, @RECON, @DtStart, @DtEnd'
BEGIN
DECLARE cCursorOCRD CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT CardCode FROM OCRD
OPEN cCursorOCRD;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM cCursorOCRD INTO @CardCode
IF @@FETCH_STATUS <> 0
BEGIN
BREAK;
END
INSERT @Temp2
EXEC(@q)
END
END
CLOSE cCursorOCRD
DEALLOCATE cCursorOCRD
INSERT @RT_ResultFunction
SELECT * FROM @Temp2
RETURN
END
Thank you,
Anita.