I need to register every time a SELECT is run on my system, if the user is logged in, this process would be done inside my ERP that allows this type of Customization, however I tried within SELECT to insert a function to call a procedure that does an insert in a table to do this type of control, below the function that I created:
CREATE FUNCTION FNCCONTROLEACESSO (@CODUSU INT , @TELA NVARCHAR(MAX))
RETURNS INT
AS
BEGIN
DECLARE @ResultVar INT
EXEC CONTROLEACESSO @CODUSU,@TELA
RETURN @ResultVar
END
GO
The code below and the Procedure that inserts it into my table
ALTER PROCEDURE CONTROLEACESSO (@CODUSULOG INT , @NOMETELA NVARCHAR(200))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @CONT INT
SET @CONT = (SELECT TOP(1) CASE WHEN MAX(UNICO) > 0 THEN MAX(UNICO) + 1 ELSE 1 END AS UNICO FROM MGE_TESTE.SANKHYA.AD_CTACESSOTELA )
INSERT INTO AD_CTACESSOTELA (UNICO, CODUSU,NOMETELA,DTREGISTRO) VALUES (@CONT,@CODUSULOG,@NOMETELA,GETDATE())
END
GO
But when I try to run this by presenting this error here
Msg 557, Nível 16, Estado 2, Procedimento FNCCONTROLEACESSO, Linha 13 [Linha de Início do Lote 2]
Only functions and some extended stored procedures can be executed from within a function.
I did some research and discovered that you can not run procedures inside the select or within functions, I would like a help or idea of how I could solve this problem.