1

I'm using Full Text Catalog and function CONTAINSTABLE

My Working code is:

SELECT * FROM CONTAINSTABLE(Genus, Name, '"Al*"')

So with this I create custom function like this and it works:

CREATE FUNCTION dbo.GetRank ()
RETURNS @tempRankTable TABLE([key] int, [rank] int)
WITH EXECUTE AS CALLER
AS
BEGIN
     INSERT INTO @tempRankTable SELECT * FROM CONTAINSTABLE(Genus, Name, '"Al*"')

     RETURN;
END

This is also working code:

CREATE FUNCTION dbo.GetRank (@table nvarchar(50), @column nvarchar(50), @searchString nvarchar(4000))
RETURNS @tempRankTable TABLE([key] int, [rank] int)
WITH EXECUTE AS CALLER
AS
BEGIN
     INSERT INTO @tempRankTable SELECT * FROM CONTAINSTABLE(Genus, Name, @searchString)

     RETURN;
END

But this is not working code: enter image description here

I also tried to use EXEC but in function is not allowed: enter image description here

Does anybody know how to pass variable to CONTAINSTABLE for table name and column name?

tonco
  • 1,281
  • 3
  • 16
  • 30
  • isn't @searchstring missing the surrounding apostrophe's or quotes in the last attempt? Write the text to a string write out the string to see if it's malfromed SQL... The 3rd option is the only one I believe could work. `Exec('INSERT INTO@TempRankTable Select * from CONTAINSTABLE('+@table+', ' +@column+', "+@searchstring+'")')` perhaps. – xQbert Dec 22 '15 at 19:08
  • I'm 100% sure that there is no missing apostrophe's or quotes you can see from screenshot that is not underlined. What I see that EXEC is not allowed in functions http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60857 – tonco Dec 22 '15 at 19:18
  • Ugh missed that: http://stackoverflow.com/questions/9607935/call-dynamic-sql-from-function correct. It's a scope problem. Several examples seem to reference using CLR. http://stackoverflow.com/questions/9607935/call-dynamic-sql-from-function – xQbert Dec 22 '15 at 19:24
  • hmm yeah but still don't know how to pass it as parameter - I spend with this whole day, can't find answer – tonco Dec 22 '15 at 19:33
  • 1
    [Here's why](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/032ffe22-6ae4-44e3-9472-5cf5f9c72f42/containstable-variable-usage) I didn't think the first options would work: leaving you with having to use dynamic SQL – xQbert Dec 22 '15 at 19:36

0 Answers0