Wrong parameter types in parameterizedSQL

Nobody has claimed this yet.

If you have SQL table with char or varchar datatypes, loopback connector always sends nvarchar as parameter type. At least it should get proper type from model definition, instead of completely ignoring type defined in model and leave guessing of type to mssql package.

For example if properties definition in model is like this:

"properties": {
"Vrsta": {
"type": "String",
"id": true,
"required": true,
"length": 2,
"precision": null,
"scale": null,
"mssql": {
"columnName": "Vrsta",
"dataType": "char",
"dataLength": 2,
"dataPrecision": null,
"dataScale": null,
"nullable": "NO"
},
"_selectable": false
},
"Stevilka": {
"type": "String",
"id": true,
"required": true,
"length": 8,
"precision": null,
"scale": null,
"mssql": {
"columnName": "Stevilka",
"dataType": "char",
"dataLength": 8,
"dataPrecision": null,
"dataScale": null,
"nullable": "NO"
},
"_selectable": false
}
}

parameter type in generated sql should be char instead of nvarchar, because when this happen, SQL server will not use indexes and that dramatically slows down queries.

See: https://lostechies.com/jimmybogard/2012/07/18/troubleshooting-sql-index-performance-on-varchar-columns/

As I see the problem is in this line of loopback-conector-mssql:

[https://github.com/strongloop/loopback-connector-mssql/blob/master/lib/mssql.js#L140](https://github.com/strongloop/loopback-connector-mssql/blob/master/lib/mssql.js#L140)

where guessing of paramater type is left to mssql package instead using it from model definition.

Mssql generates SQL like this:

exec sp_executesql @statement=N'SELECT [Vrsta],[Stevilka] FROM [eNarocanje].[ZahtevekPozicija] WHERE [Stevilka]=@param1 AND [Vrsta]=@param2 ORDER BY [Vrsta],[Stevilka]',@params=N'@param1 nvarchar(8), @param2 nvarchar(2)',@param1=N'16-00039',@param2=N'Z1'

But the correct SQL would be:

exec sp_executesql @statement=N'SELECT [Vrsta],[Stevilka] FROM [eNarocanje].[ZahtevekPozicija] WHERE [Stevilka]=@param1 AND [Vrsta]=@param2 ORDER BY [Vrsta],[Stevilka]',@params=N'@param1 char(8), @param2 char(2)',@param1=N'16-00039',@param2=N'Z1'

Start at lib/mssql.js around line 140, where parameter types are left for the mssql package to infer, and trace how model mssql dataType metadata reaches generated queries. Verify that char and varchar properties produce matching parameter types rather than nvarchar, using the SQL examples in the issue as the completion criterion.

Written by the indexing model from the issue text.