cannot use `CONTAINS(column, $parameter)` in TSQL (sqlpage adds unsupported CAST as second argument to mssql contains function)

[prhc0612]

What are you building with SQLPage ?

Building a page were a user can enter a "search term" and it queries a table for those terms. The user would be able to create multiple input boxes, and specify "and / or" to build up a full search term.

What is your problem ? A description of the problem, not the solution you are proposing. The problem is caused when SQLpage goes to append the $p1 value from the user input box, into my TSQL query. If I am using TSQL where col1 like '% :input1 %' in my query it works fine, but I am trying to use a full text index, with where contains(col1, :input1) the problem here is that in SQL Server you cannot use cast() in the 2nd parameter of contains.

What are you currently doing ? Since your solution is not implemented in SQLPage currently, what are you doing instead ? Resorting to LIKe for now. but it doesn't allow the power to search that I need.

Describe the solution you'd like When adding an input field, default it to text anyway, no need to then have code trying to input "cast". Or have different input fields, one for numeric (floats) one for strings (nvarchar) so the developer can specify the input box when building the .sql page.

[lovasoa]

Hello and welcome to SQLPage !

I understand your problem. Currently SQLPage adds a CAST around all variables in all cases in order to disambiguate queries for the db engine, but we should be able to remove the casts in some cases.

In the meantime, what you can do is define a database function to perform the search, and just call it from sqlpage:

CREATE FUNCTION dbo.SearchMyTable
(
    @SearchTerm NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM my_table
    WHERE CONTAINS(text_column, @SearchTerm)
)
GO

and then in your sql, replace select * from my_table where contains(text_column, :input1) with SELECT * FROM dbo.SearchMyTable(:input1)