Use dynamic SQL statement at SQL Server 2008 in tablevalue function

Question

Is there any way to create a custom function that returns a table using a SQL statement that is created dynamically?

So far I tried things like

ALTER function [dbo].[Test2] (@Mandant smallint)
RETURNS
@Ergebnis TABLE ([Kundennummer] int,[Suchbegriff] nvarchar(20))
AS
BEGIN
    DECLARE @curTable cursor
    DECLARE @str nvarchar(1024)
    DECLARE @Kundennummer int;
    DECLARE @Suchbegriff nvarchar(20);
    SET @str = 'set @curTable = CURSOR FOR SELECT KdNr, Match FROM ADR_030 where KdNr <> 0; OPEN @curTable' 
    EXEC sp_executesql @str, N'@curTable cursor OUTPUT', @curTable OUTPUT 
    fetch next from @curTable into @Kundennummer, @Suchbegriff;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        insert into @Ergebnis values (@Kundennummer, @Suchbegriff)
        FETCH NEXT FROM @curTable into @Kundennummer, @Suchbegriff;
    END;
    close @curTable;
    deallocate @curTable;   
return
end

but just getting an error when executing the function.

Hope someone can help me here.

Thanks,

Chris

possible duplicate of [Call dynamic SQL from function](http://stackoverflow.com/questions/9607935/call-dynamic-sql-from-function); also see [Executing dynamic SQL in a SQLServer 2005 function](http://stackoverflow.com/questions/150552) — mellamokb, Apr 04 '13 at 18:24

score 0 · Answer 1 · answered Apr 04 '13 at 18:20

0

You can use CLR for that. However, there is no way to do this in plain T-SQL.

answered Apr 04 '13 at 18:20

Sebastian Meine

11,260
29
41

1

At least not in a function. It would be possible in a stored procedure, for instance. – mellamokb Apr 04 '13 at 18:25

Use dynamic SQL statement at SQL Server 2008 in tablevalue function

1 Answers1