Feature: Saved Queries

[Kenobi-the-2nd]

• MSSQL Extension Version: 1.14.2
• VSCode Version: 1.67.1
• OS Version: Microsoft Windows 10 Pro, Version 10.0.19044 Build 19044

Good evening,

First off, thank-you again for an excellent extension! I would like to propose a feature that would allow users to name queries and save them for opening/running later. The functionality would be similar to the query history, except that the queries could be named and would persist after the application closes. There would be a separate view in the sidebar for this that might look something like the interface in the below image. Also, it would make sense to have a related command (for example, "mssql.runSavedQuery") that would take as input the name of any of the saved queries.

There are several benefits to this feature:

1. Allows naming and saving queries for quick access later.
2. Allows easy organization of multiple versions of the same query.
3. Saves time by allowing user to access all saved queries in one place instead of searching through one or more files.
4. Command to run saved query would allow integration into the Command Pallete, tasks, macros, custom buttons, or keyboard shortcuts for even greater flexibility.

This would be very useful to me and I'm sure to other developers as well. I often find myself running many of the same (or variations of the same) queries on a regular basis, but currently I keep them all in one large file. It has become tedious to scroll or search to the query I want in the file to run it. Having them listed by name in a separate view as I've described would streamline this significantly. And the addition of a command to run a specific saved query would be very helpful--with this I could save the most used queries to shortcuts.

Please let me know if further clarification is needed. I would very much like to see this feature added.

Thanks again!

[Charles-Gagnon]

You can accomplish this by saving the queries into files in your workspace - which then also gets benefits of being able to be shared with other people more easily as well. Is there a reason you think a feature like that belongs in query history? Then when you save the query file you can name it whatever you want.

You mention you have a single file with the queries you run, what if you split that into multiple files for each query instead?

[Kenobi-the-2nd]

Yes, I had thought about splitting up the queries into multiple files. However, this still does not achieve what I am looking for in the Saved Queries feature I've described. With this feature, you can see all of your saved queries in one place. As shown in my concept image, there would be options to open, run, or even copy a saved query using the context menu. The default behavior for double clicking a saved query would be to immediately run it.

Another reason for requesting this feature is so that a mssql.runSavedQuery command (or something similar) might be added to the extension, allowing a user to run any of the saved queries. This command is essential, in my opinion, as it would allow integration of the feature into the Command Pallete, tasks, macros, custom buttons, or keyboard shortcuts. This is my main interest for this feature. Having a command that allows you to access/run your queries makes it easy to automate and/or streamline the workflow so that there is less manual work involved.

I did attempt my own solution using the commands currently exported by the MSSQL extension and a macros extension. The macro is below:

"execute-query": [
    "mssql.connect",
    { "command": "type", "args": { "text": "test_db" } },
    "workbench.action.acceptSelectedQuickOpenItem",
    "mssql.runQuery",
]

I also tried something similar in a task:

{
    "label": "Execute Query",
    "command": "${command:mssql.runQuery}",
    "args": ["test_db"]
}

In both instances the argument passed to the mssql.connect and mssql.runQuery commands is disregarded. Apparently these commands were not written to accept arguments. I tried working around this by utilizing the clipboard copy and paste commands to insert "test_db" into the connection profile input box, but this did not work either.

In short, there seems to be no way that I know of to dynamically run a query using the existing commands or interface. Perhaps instead of the Saved Queries view, an update could be made to the mssql.connect and mssql.runQuery commands so that they take the name of a database profile as an argument. Then I could, for example, create a list of tasks (one SQL file for each) and each file/query would be paired with the appropriate database profile. Then I could map the tasks to specific keybindings, buttons, etc.

[Charles-Gagnon]

Gotcha, thanks for the additional context. I'm doing some work in the query history area here so as part of that I'll take a look at this scenario and see what we can do to make improvements in general.

[Kenobi-the-2nd]

Thank-you, that would be very helpful.

[Kenobi-the-2nd]

@Charles-Gagnon I wanted to follow up on this to see if any updates or improvements were able to be completed.

Thank-you for your time.

[Charles-Gagnon]

Not currently something I'm able to work on unfortunately.