0

I need to use dynamic SQL w/in a function so that I can do things like SELECT * FROM searchAllFields('Pregnant'). I know that it is not possible to use dynamic SQL within a function but I see that this guy has found a workaround Getting an error when executing a dynamic sql within a function (SQL Server)?

But when I try applying his work around on mycode I get A RETURN statement with a return value cannot be used in this context. It seems like I am doing the exact same thing. Why am I getting that error in my code?

ALTER proc [dbo].[searchTerm](@search nvarchar(max))
as
begin
declare @value nvarchar(500),
        @SQLString nvarchar(4000),
        @stmt nvarchar(max)

select @stmt = isnull(@stmt + ' or ', '') + quotename(name) + ' like @search'
from sys.columns as c
where c.[object_id] = object_id('dbo.table')

select @stmt = 'select @value = id from table where ' + @stmt

exec sp_executesql
    @stmt = @stmt,
    @params = N'@search nvarchar(max)',
    @search = @search,
    @value = @value output

    return @value 
end
Community
  • 1
  • 1
bernie2436
  • 22,841
  • 49
  • 151
  • 244
  • The accepted answer in the linked question is using a stored procedure, not a function. – Mikael Eriksson Sep 05 '13 at 05:11
  • @MikaelEriksson good point. I changed mine to a SP but got the same error. – bernie2436 Sep 05 '13 at 13:28
  • How are you using this SP? You can not do a select form a stored procedure. You have to execute it and this procedure does not return a resultset. If you actually tested the SP you have posted here I would guess that you get the error `Must declare the scalar variable "@value".` – Mikael Eriksson Sep 05 '13 at 13:47

1 Answers1

0

Looks like this isn't going to work as your function returns an inline table-valued function.

For this syntax the function definition is followed by AS RETURN ( SELECT ... ) rather than AS BEGIN... RETURN ... END

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208