Dynamic tables from UDF in SQL Server

Question

how can i decide this problem?

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[GetDataById] ()
RETURNS INT
AS
BEGIN
  DECLARE @query NVARCHAR(500)
  DECLARE @j INT
  SET @query=N'select * from catalog'
  EXEC sp_executesql @query
  RETURN @j
END

When I try to exec this one: select dbo.GetDataById()

I get an error message:

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

Answer 1

You can't use dynamically-created SQL from within a function. You have to use a stored procedure for that.

But in your case I don't see why you even put your query into a variable anyway.

Answer 2

From this post by Erland Sommarskog, SQL Server MVP :

you cannot use dynamic SQL from used-defined functions written in T-SQL. This is because you are not permitted do anything in a UDF that could change the database state (as the UDF may be invoked as part of a query). Since you can do anything from dynamic SQL, including updates, it is obvious why dynamic SQL is not permitted.