Snippets and preprocessor

Some examples where preprocessed stuff could come in handy in snippets:

#1 I use snippets not only to add code to the editor but to do some routine tasks as well, eg. searching for locks, counting the no. of rows in tables, calculating table/index space requirements, database space usages, etc. and displaying those in a result tab, all conveniently using snippet keywords + typing some parameters before the keyword or passed through the $PROMPT()$ macro. These all work marvelously and I've been using them for ages. Some of those snippets have quite some deal in common with each other and only differing slightly. But sometimes that small difference is proving so difficult to translate into code (or results in such a vast number of characters arbitrarily put together) that it is easier to clone the snippet and alter its copy. This inevitably leads to having the same code in multiple snippets and changing/maintaining that tends to be a nuisance. Now, if that "common part" could be simply replaced with #include, and the variable definition could use a $PROMPT$ to set its value...

#2 MySQL has session variables that keep their value during the connection, meaning that I can use a $$...$$ macro to calculate something I will use in a later $$...$$ macro, instead of putting the whole shebang into a single monstrous $$...$$ macro, which immediately loses all the advantages of displaying syntax highlight. However, that's exactly what happens with SQL Server, due to lack of the ability to use global variables. While the User Guide calls them defined variables (or macros), I'm afraid this might not work as (I guess) they are more defined constants than variables. Though I haven't tried yet I wouldn't expect setting values to those variables to work. Nevertheless, this could become useful as well.

#3 On the other hand, MySQL does not allow using control structures (IF...THEN...ELSE) outside of stored programs meaning that certain things are quite tricky to achieve, and not only in the snippets (eg. see Unit Tests). The #if and #ifdef directives could do wonders here, too.

Thank you. I understand now. You're correct you cannot change the "variables" dynamically. The are kind of static. You can redefine them though within a script. But cannot do it using a snippet. The sequence of processing is like
1. Static script text processing by preprocessor.
2. Script execution starting, dynamic code snippets executed.
3. The final script is executed.

Last edited by SysOp on Tue Feb 05, 2019 3:37 am; edited 1 time in total

I see. Nevertheless, they could still prove useful in snippets. I just can't seem to be able to make them work, not even in SE. I copied the examples but they refuse to yield. This one, for example:

	Code:
	--#define _COUNT_ 5 PRINT 'This will print 5' PRINT 'And this will print ' + CAST(_COUNT_ AS VARCHAR(3)) + ' too'

only produces an error message:

	Quote:
	Executing selected queries... Please wait or close this tab to cancel execution. Msg 128, Level 15, State 1, The name "_COUNT_" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. The command(s) completed with errors (22.20 ms).

I'm sorry for the typo in the previous messages, I meant to write "cannot change" instead of "can change"

For the preprocessor to kick in, you need to activate it first. It's basically implemented as a plugin which needs to be loaded into the editor. You can use Ctrl+Shift+Space, SQL: P keystroke to load it.

Ohh, I see now. If that pane SQL: Preprocessor is not active then it doesn't work, regardless of the Active checkbox being checked. It doesn't work in snippets either. They seem not to be aware that pane being open in the editor.

EDIT: the same applies to Unit Tests as well :(

And I read that without the typo :) I suppose it wouldn't have made sense otherwise. Strange, strange human brain.

Great information.Helpful