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)

[JoeJordan1]

Thank you for the workaround. I'm just adding a comment to say that this behaviour is also applicable to EXECUTE commands in MS SQL Server, where we can't pass any parameters into a stored procedure or dynamic sql due to SQL Page adding the 'cast(@p as varchar(MAX))' to the parameter first. It would be very helpful to have an option to remove the cast (and just pass the raw value) as a parameter.