Variables - used as Constants - in Rotations SQL (for clarity and portability)

[DavidEsp]

As I've just discovered / confirmed by experiment - it is possible to define SQL code variables - that can be used in effect like constants - at the top of the SQL code, then reference those in the SQL code - instead of "funny numbers" (e.g. for subcategories or genres). One could for example have standard sets of them, to be routinely pasted-in to any such SQL code.  Could store such sets in a text document or (better) a spreadsheet (e.g. with columns for type of use-case/situation e.g. instances of various station formats).

WHY:
Removes a layer of obfuscation (when presenting or passing-on such code) and removes a layer of "memory challenge" for the code-writer.
Also, this approach could also make such code more portable.  When copying/moving SQL to another instance (and possibly version) of RadioDJ (and its associated database), it might sometimes be the case (as I have encountered) that the "funny numbers" need to be changed.  With the variables approach, if you had a bunch of SQL-Rotations to me moved, you'd only need to edit those variables - no need to hunt through the detailed "workhorse" SQL-code.  Quicker, less stressful and more reliable.

HOW:
Example trivial Rotation SQL:

Code Select Expand

SET @'const_subcat_1960s' = '31';
SELECT `ID` FROM `songs` WHERE `id_subcat`= @'const_subcat_1960s' LIMIT 2Note that the single-quote must be placed after the "@" symbol.

BTW - the only reason for the "LIMIT 2" was for convenience when (initially) testing in the Rotations-SQL editing window.  It can of course be deleted.  And while in my test-instance of RadioDJ it was the case that the "1960's" subcategory had the index-value 31, other people's instances may have different index-values for their subcategories.

QUESTION:
How reliable is this?  RadioDJ obviously han handle variables, but does it "like" them? Like does their use introduce any significant delay or not, in executing the SQL.  I doubt it, but the only way to know is through "battle-testing".

CTA:
Any replies welcome.  Any views or alternatives regarding my stated "WHYs"?  Anyone already doing this?  Anyone like to test it for themselves - maybe on complex and time-critical SQL-based Rotations?
I look forward to the possibility of learning/sharing more through any responses.

[stevewa]

It does not affect performance, it is all SQL engine based, so it won't hurt RadioDJ at all.

You can consider a different approach to assigning variables using select statements.

Code Select Expand

SET @'subcat_name' = "2000s";

SELECT @'id_subcat':= ID FROM SubCategory WHERE NAME LIKE @'subcat_name' ;

SELECT ID, id_subcat, Artist, Title FROM Songs WHERE id_subcat = @'id_subcat' ;

That way the variable(s) appear at the top of your statement, easy to change, and the code is portable between systems, without the need to set or memorize id values of categories and subcategories.

[DavidEsp]

Groovy!  Leads the way to generic/template Rotation (and other) SQL code, conceivably portable both to different music formats as well as different databases, without having to ever manually determine/discover the "funny number" IDs.  Thank you stevewa.