Dynamic View name in Table valued function

Question

I'm passing View name as parameter in a Table Valued Function, and I want to fetch some data from that view by building a dynamic SQL and executing it by sp_executesql(). when try to execute the function, I get the error: Only functions and extended stored procedures can be executed from within a function.

DBMS: SQL Server 2005

any workarounds?

set @SQLString  = N'select @Desc = Description from '+ @TableName + ' where Code = @Code;'
execute sp_executesql @SQLString, N'@Code nvarchar(500), @Desc nvarchar(500) OUTPUT', @Code = @Code, @Desc=@Desc OUTPUT;

Answer 1

Well, you could wrap the dynamic SQL in an extended stored procedure. That would work, but I'd (strongly) advise against doing it.

SQL Server requires user-defined functions to be deterministic (with the exception of the aforementioned extended stored procedures) -- i.e. the results of the function should be uniformly predictable from the input parameters. Since stored procedures can access data from anywhere, use random numbers, etc., SQL Server will not allow you to use them inside a function.

There are other approaches you can use, such as prepopulating a table variable with your data, modifying your schema, and so forth, that will depend on your performance requirements and how you have the schema set up.

Answer 2

no unless you want to do a loopback query by calling an extended proc like xp_cmdshell

something like this, modify to fit your needs

CREATE FUNCTION fnBla(@id int)
RETURNS int
AS
BEGIN
DECLARE @SQL varchar(500)
SELECT @SQL='osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL
RETURN @id
END

Just so that you know I would not do this this way since you are creating a loopback query and not executing the safest code any reason you can't use a proc instead of a function?