CODEWITHSUNDEEP
sqlsql-serveruser-defined-functionssql-function
Amjath
Amjath

Reputation: 55

How to return Table in Multi-Statement Table valued function

So I have Multi-Statement Table valued function has input and output parameters like this

    CREATE FUNCTION fn_SearchByText 
(
    @AdTitle varchar(100)
)
RETURNS @output TABLE(AdID varchar(20), AdTitle varchar(50), BrandName varchar(20), 
ModelName varchar(20), ModelType varchar(20), ModelYear int, Describe varchar(350),
Price int, PostedDate datetime
)

And after few lines of code I have statement as mentioned below PS:@like is a declared variable in from the code

SET @query ='select AdID, AdTitle, BrandName, ModelName, ModelType, ModelYear,
    Describe, Price, PostedDate from Ads where AdTitle is not null '+ @like;
    exec sp_executesql @query;

So How can I return table from the last statement.

Please help

Upvotes: 1

Views: 1042

Answers (1)

dean
dean

Reputation: 10098

Sorry, but you can't execute a dynamic SQL from inside a TVF. Make it a stored procedure, it will allow for this (although not so elegantly).

Upvotes: 3

Related Questions