Sql function

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.

This is one of the limitations of user defined functions. See this page for a list of restrictions and limitations on UDF's

Is there some specific reason you want this to be a function rather than a stored procedure? Stored procedures are much less restrictive.

Hi James,

Yes, the end-user wants to do SELECT and a procedure doesn't allow him to do that. So he ask me to change the stored procedure to a function, so they can filter and see what they want.

This isn't possible?

Regards,
Anita.

Unfortunately, you cannot create a UDF because of the restrictions - for example, the restriction on dynamic SQL.
And, you are right in that if you do use a stored procedure, you cannot use it directly in a select.

So you have to look for alternatives. If you are able to open up the stored proc you are trying to execute and recast its logic, it may be possible to avoid the stored procedure and the cursor altogether. But, without examining the stored proc, one cannot tell if that is even a possibility.

There are various workarounds, but each with its own baggage and restrictions. None as simple as a UDF that the user can select from.