How to specify the parameter type?

[BlueHtml]

Hello, the SQL in order.sql is as follows:

select *
from Order with(nolock)
where Id=$id

After starting the program using set RUST_LOG=trace && path\to\sqlpage.exe and accessing http://localhost:8080/order.sql?id=1000, I found the generated SQL in the output logs as follows:

    SELECT
      *
    FROM
      Order WITH (nolock)
    WHERE
      Id = CAST(@p1 AS VARCHAR(MAX))

It converts Id to VARCHAR(MAX) type. However, Id is actually of int type. There is an index on Id, but the type mismatch prevents the index from being used.

Is there a way to specify the type of Id as int? This way, the index can be used.

[lovasoa]

Hi !
HTTP URL parameters do not have a type. When you load /order.sql?id=1000, there is no way to know whether the URL parameter id is intended to be a number, or a string that just happens to only contains digits in this case. So SQLPage handles all parameters as strings by default. However, it's easy to convert the parameter to an integer in SQL when you need it. Just replace your SQL query with

select *
from Order
where Id = CAST($id AS INTEGER)

[BlueHtml]

@lovasoa Hello, I manually added CAST and found that SQLPage generates two CASTs.

The SQL is as follows:

select *
from Orders with(nolock)
where Id=CAST($id AS int)

The SQL generated by SQLPage is as follows:

    The SQL statement sent by SQLPage was:
    SELECT * FROM Orders WITH (nolock) WHERE Id = CAST(CAST(@p1 AS VARCHAR(MAX)) AS INT)

When CAST is specified, can it avoid automatically generating another CAST?

[lovasoa]

We could, but I don't think there is any advantage to doing it, other than the queries would look nicer.