1

I have this Function:

CREATE FUNCTION [dbo].[fn_xxx]
    (
    @Nome VARCHAR(100),
    @Descricao VARCHAR(500)
    )
RETURNS @tbDados TABLE
    (
        Qtd INT
    )
AS
BEGIN
    DECLARE @CMD AS NVARCHAR(MAX);
    DECLARE @Tabela VARCHAR(100);
    DECLARE @CampoDescricao VARCHAR(100);

    --- *** Identifica os campos e tabelas 
    SET @Tabela = 'tbl' + @Nome; 
    SET @CampoDescricao = 'Desc' + @Nome;

    SET @CMD = '
    INSERT INTO @tbDados (Qtd) 
    SELECT @Qtd = COUNT(*) 
    FROM ' + @Tabela + ' 
    WHERE ' + @CampoDescricao + ' = ''' + replace(@Descricao, '''', '`') + ''';';

    EXEC sp_executesql @CMD;

    RETURN 
END 

I'm trying to help developers here, but with no success.

When we execute it ( select dbo.fn_xxx ('processador','teste')) it give us this error:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_xxx", or the name is ambiguous.

I've been searching through the internet, and found some results, but it was all about the code.

can someone help me how to figure out this error in this function?

Thanks

EDIT:

If i execute it with:

SELECT * FROM [EasyTablet].[dbo].[fn_xxx] (
    'processador','teste'
  )
GO

It shows me this error:

Only functions and some extended stored procedures can be executed from within a function.

  • Hello...I think the error is because they're calling SP_EXECUTESQL inside the Function. Am I right? – Racer SQL Feb 27 '15 at 18:44
  • 2
    You can't use dynamic SQL inside a function (well, aside from this awful hack). The main reason is that functions can only return data and can't have side effects. There is no way for SQL Server to prevent you from passing an insert or an update to sp_executesql (as an aside, that looks like a SQL injection attack vector no matter how you end up implementing it). – Aaron Bertrand Feb 27 '15 at 18:45
  • Thank you very much. I'm telling the programmers to make a Proc, instead a Function. – Racer SQL Feb 27 '15 at 18:48
4

You can't use dynamic SQL inside a function (well, aside from this awful hack). The main reason is that functions can only return data and can't have side effects. There is no way for SQL Server to prevent you from passing an insert or an update to sp_executesql (as an aside, that looks like a SQL injection attack vector no matter how you end up implementing it).

It's unclear to me why you're doing this as a multi-statement TVF in the first place. You're returning one column and one row. Why not use a stored procedure with an output parameter?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.