I understand that MySQL allows you to store SQL commands in a .sql file and execute it from the MySQL command prompt using:
mysql> source my_queries.sql
What’s the approach to send parameters into this script? Let’s say I have a script that fetches all workers from a specific company division, and I want to provide the division ID as a parameter each time I run it.
I’m not looking to execute these through bash scripts. These are basic queries that I execute directly from the MySQL command prompt. I’m getting tired of typing them repeatedly, but creating full bash scripts seems like too much for such simple tasks.
i totally get you! set a session var like @division_id at the start, then use it in ur WHERE clause. it’s not perfect but does the job without the hassle of bash scripts.
One effective method I’ve found is to create a template script that includes placeholder comments for parameters. For instance, I maintain a file named worker_query_template.sql where I include a comment like -- DIVISION_ID: REPLACE_ME at the beginning, followed by the actual query with a hardcoded division ID. When I need to run the script, I simply copy the template, replace the placeholder with the desired ID, and source the updated file. This approach takes mere seconds and eliminates the need for declaring variables each time. While it does generate temporary files, it significantly streamlines the process for frequently used queries with varying parameters.
You can do this by combining user-defined variables with the source command. Set a variable at the MySQL prompt first - like SET @dept_id = 5; - then reference it in your queries.sql file. Your script would just have SELECT * FROM workers WHERE division_id = @dept_id;. This keeps everything in MySQL without needing external scripts. I’ve used this for months and it works great for simple parameterized queries. Only downside is remembering to set your variables before each run, but it’s way better than constantly rewriting queries.