Explicit Param type to pass size, scale

[jimbobmcgee]

Is your feature request related to a problem? Please describe.
It does not appear to be possible to explicitly set the size of input or output parameters, using the standard interfaces. For queries/prepared statements that are run frequently with different-sized varchar/nvarchar/vabinary values, this can lead to multiple plans being cached on server, each differing only by parameter size.

Describe the solution you'd like
I think the go-mssqldb library would need to declare a type Param struct { Value any, Size, Scale uint} (i.e. exposing similar fields to the .NET SqlParameter class).

The type would be handled as a special case, presumably within func (*Stmt) makeParam(driver.Value) or func (*Stmt) makeParamExtra(driver.Value), so it generated the desired parameter literally based on the field values, rather than inferring size, etc. from the length of the value data.

Instances of mssqldb.Param would be passed directly to db.Query, stmt.Execute, etc. as any substituted value would normally be supplied:

import mssql "github.com/microsoft/go-mssqldb"
//...

if stmt, err := db.Prepare("SELECT * FROM sys.databases WHERE name = @p1"); err != nil {
  _ := stmt.QueryRow(mssql.Param{Value:"SHORTNAME", Size:128})
  _ := stmt.QueryRow(mssql.Param{Value:"A_LONGER_NAME", Size:128})
  //...would now generate:
  //  EXEC sp_executesql N'SELECT * FROM sys.databases WHERE name = @p1', N'@p1 nvarchar(128)', @p1=N'SHORTNAME'
  //  EXEC sp_executesql N'SELECT * FROM sys.databases WHERE name = @p1', N'@p1 nvarchar(128)', @p1=N'A_LONGER_NAME'

  _ := stmt.QueryRow("SHORTNAME")
  _ := stmt.QueryRow("A_LONGER_NAME")
  //...would continue to generate, as it previously did:
  //  EXEC sp_executesql N'SELECT * FROM sys.databases WHERE name = @p1', N'@p1 nvarchar(9)', @p1=N'SHORTNAME'
  //  EXEC sp_executesql N'SELECT * FROM sys.databases WHERE name = @p1', N'@p1 nvarchar(13)', @p1=N'A_LONGER_NAME'
}

Describe alternatives you've considered
Not sure if stored procedures might also get some use from this, or if the stmt.makeParam obtains the declared sizes but, in any case, it is not always possible to create procedures within the database under query.

Additional context
This type might also allow for specifying the target size of output parameters, e.g. if Value were set to an instance of sql.Out, which might be more appropriate for issues such as #161.

Whether or not it would need to implement driver.Value is to be determined.

[shueybubbles]

thx for opening an issue!
A corollary to specific parameter sizing is the ability to provide explicit code page/collation specifications for varchar strings.
Some options are listed in #46
We need to come up with something like a Param type to be able to complete the implementation of Always Encrypted as well.