Processing Method
-
There are a couple of reasons you may want to take a SQL query from a program and remove the parameterisation from the query. For example, you want to test the query in SSMS or you want to edit the query. You could do this to replace AddWithValue parameters with Fully Typed by reverting and then simply Process. If the query has a lot of parameters and or it's part of a long batch of queries you may want to remove the parameters so the query is easier to understand. You will make a choice about this and either revert the query and complete your edit and then process the Query to parameterise again or you can edit in place. When you paste a query into the editor the tables, views and aliases are automatically extracted and listed in the Tables and Alisase list. At this point you have a lot of information disclosed about the query. As you work on editing the query autocomplete will operate and this will ensure that only the columns for a specified aliase can be selected or columns for specified table or tables belonging to a specified Namespace or schema name. This makes life a lot less confusing and your edit will be relatively trivial. Try it out and see for yourself, occassionally, this process won't give a perfect result but manual edits are usually trivial.
-
As an option, the results command bar button called SqlParser can be used to parse the pure SQL part of your code. Highlight the commandtext for the query then press the SqlParser button to see a report on the query syntax. False positives are possible here use your programmer skill to identify the validity of any errors detected. To copy the query preparatory to pasting back into the source code click the Copy button.
The Find Replace button will open a search dialogue. You can use this to search the query text of course however it's real job is to replace innappropriately named parameters. For eg. a date parameter could be named @01/01/2019. The Replace functionality in the dialogue can edit all instances of this parameter name to @DateVal, for example.
Editing SQL Queries
fixSQLeditor makes SQL edits faster with no typo's and less tedium. At a glance you see all tables and aliases, you use fixSQLeditor autocomplete to bring all meta data information you need for your edit to the cursor. Everything you need is in the Editor when you need it.
-
Paste the query into the editor pane all tables, aliases and views will appear in the Table and Aliases list.
-
When the Tables and Aliases list has assembled all the tables and aliases used in the query Auto Complete is enabled. You can now edit the query using fixSQLeditor autocomplete menu's for database meta data. highlight all instances of a table in the editor, expand Tables and dbl click columns to add them at the cursor.
-
Support for User Defined Types is available for use in parameter creation when they are registered in the database. When fixSQLeditor loads all Schema's and UDT's are identified and available. UDT's will appear in the Tables and Aliases list however they will have their actual underlying data type and length listed as well to assist in development.
-
When your editing a query that has a lot of non SQL code that you want to keep together with the commandText (eg an If statement that encloses several queries) just copy it all into the editor then either use Manual Conversion to produce the paramaterised query or highlight the non sql text and press F8. The text will have a light red back color which indicates that it is excluded from processing. Auto processing will exclude code that appears before the beginning of SQL script however non sql that follows the query may be a problem if it isn't manually excluded.