SQL LIKE operation doesn't recognize variable

[sachinshahredmane]

Issue Summary

The following SQL in SqlCommandProvider doesn't recognize @logTblName as a substitution variable.

First, here is the raw SQL that works in SSMS:

SELECT *
FROM MyDataTable dl
WHERE dl.Options like '%"STATE\%';

The values that we are looking at look like serialized JSON.

To Reproduce

Create a simple table with at least one column Options set to nvarchar(max). Set values exactly like follows in this field (including the backslashes, quotes, etc.):

[\"COUNTY\"],[\"STATE\"],[\"COUNTRY\"]

Code:

let cmd = 
   new SqlCommandProvider<"
      SELECT *
      FROM MyDataTable dl
      WHERE dl.Options like '%\"@logTblName\\%';
   ",connectionString>(connectionString)

cmd.Execute(logTblName="STATE")

Error

Error message:

error FS0495: The member or object constructor 'Execute' has no argument or settable return property 'logTblName'. The required signature is SqlCommandProvider<...>.Execute() : Collections.Generic.IEnumerable<SqlCommandProvider<...>.Record>.

Expected behavior

It should recognize @logTblName as a parameter.

What you can do

• [x] I am willing to contribute a PR with a unit test showcasing the issue***
• [x] I am willing to test the bug fix before next release

The *** is because I don't know enough to fix this, but may be able to do so with (a lot of) guidance.

EDIT: Fixed F#.

[smoothdeveloper]

@sachinshahredmane, this isn't achievable, the SQL parser itself wouldn't accept the statement.

What you can do, is have like @logTblName in your statement, and put the patterns (and escape %) in the parameter itself; I think this would fit your need, but please let us know.

[sachinshahredmane]

Ah I see. Will try and get back to you.