1

I have created a complex query that references a specific date (year and month to report on) on numerous parts of the script. What would be the best artifact to have to only change 1 line of code and have that cascade to all my script each time a new reference date is needed?

I understand that there are issues declaring and using a variable in the same query due to complexities in the execution plan. Also I do not have access to the database other than to query so I cannot use Stored Procedures or Functions, and can only depend on client side script.

Any suggestions or guidance would be welcome. To clarify I am not asking for specific code or to review my code (as it is functional) but rather to offer high level guidance on what would be the best solution to the problem presented. Regards,

nbayly
  • 123
  • Declare a @constant at the top of the script. – Robert Harvey Dec 13 '16 at 20:19
  • Isn't that the same as declaring a variable? Would that not be unreliable because of difficulty in predicting if it will assign that value before proceeding with the rest of the script? – nbayly Dec 13 '16 at 20:21
  • What does that mean? Will you be hard-coding the value into the script, or using a parameter? – Robert Harvey Dec 13 '16 at 20:21
  • I'm referencing whats discussed in this post: http://stackoverflow.com/questions/16715504/mysql-define-a-variable-within-select-and-use-it-within-the-same-select where the accepted answer states that you should never assign a value and read that value on the same statement. If I declare the constant at the top would that be considered the same statement? Or a different statement on the same .sql? – nbayly Dec 13 '16 at 20:30
  • 1
    That post is talking about declaring and using a variable *in the same SQL statement,* which sounds like a really bad idea to me. I am suggesting that you declare your variable at the top of the script, and then use that variable in all subsequent SQL statements in your script. Your variable will be set at the top of the script, and remain set for the duration of the script. Presumably, your statements will all be executed in the order that you write them in your script. – Robert Harvey Dec 13 '16 at 20:36
  • Robert is suggesting to load first the date. Put the date into a global variable within the script. Apply the variable to the next statements – Laiv Dec 13 '16 at 20:38
  • Thanks for the clarification. Making more sense now. Would a global variable or a session variable be preferable here or would there be no difference between them? – nbayly Dec 13 '16 at 20:41
  • 1
    Look at this example: http://stackoverflow.com/a/11196250 – Robert Harvey Dec 13 '16 at 20:42

0 Answers0