Enable use of parameters not variables to enable more complex types

[simonsabin]

Variable substitution is great except its prone to SQL injection attacks as the contents is injected, and also can be troublesome when dealing with types such as values such as multi line(not possible), large values, dates, binary values.

It would be great to be able to specify parameters such as the following.

sqlcmd -vp myjson='somevalue' -Q "exec myProcedure @myjson"  

This could be extend in a similar fashion as #385 to allow for the contents of a file to be passed as a parameter.

sqlcmd -vpf myjson=c:\somejson.json -Q "exec myProcedure @myjson"  

This would make passing multi line values easy, such as JSON files, Certs etc.

This is super important for SQL Azure, given you can't access network shares etc from the server. Thus for getting data in though SQLCMD such as loading data this would be useful.

[shueybubbles]

We need a mechanism like this to support Always Encryption, which I've started working on in the Go driver at github.com/microsoft/go-mssqldb/pull/116.

All encryption from the client has to use parameterized queries. The tricky part is deciding what the data type of the parameter is. How do you propose we encode the data type of the parameter?

[simonsabin]

I've long thought that slcmd mode needs enhancing to allow scripts to be run better from command line and from within SSMS and ADS. If you think about powershell you can parameterise a script which allows a script to be called with parameters.

Choices are either to discover the parameters, or explicitly define the parameters. My experience is that discover of parameters is that its never 100% due to dynamic SQL and other aspects that make parsing the TSQL hard.

I'd opt for the explicit definition of parameters, either

• on command line (a bit ugly and verbose)
• within the TSQL script
• within a separate file

within the tsql script

The definition of the parameters to be used should reside with the script thats going to use them.
[If a TSQL script was decorated with a parameter block within a comment, that could be easily parsed and could be used within ADS and SSMS as well. This would allow you to run the batch and be prompted for parameters or specify them in some UX in the tools, similar to parameters within a notbook.

Benefits is that this allows parameters to be specified per batch within a file, if you've long scripts it means you can avoid clashes of parameters across batches, especially when post deploy scripts become very long.

A comment block named as parameters and then oneline per parameter
/* parameters
@foo int 100
@DATEvalue datetime2 "1/1/2021" dmy
@JSON nvarchar(max)
*/

or a single line like the parameter definition specified with the
/* parameters @foo int, @DATEvalue datetime2, @JSON nvarcha(max) */

within a separate file (related to #387)

Separating values away from the script really helps with executing in different contexts, you can define the data types but really this should only be used for setting values.

[simonsabin]

Another related point, whatever choice is made I would like xevents/profile to output the relevant command such that you can run a parameterised statement from any of the tools.