Can we add variable names in mysql queries?

Question

consider a table.

id    count       dates
1      10       2016-11-12
2      20       2016-11-12
3      20       2016-11-12
4      20       2016-11-13
5      20       2016-11-13
6      20       2016-11-14

i am creating a test.sql file which contains

select * from table_name where dates = 2016-11-12;

if i have to find rows for date 2016-11-13 i have to change where condition in my .sql file.

what i want is can i declare a variable name at the top of my .sql or outside my file

which has variable DateSelect and i only change that and date in my .sql changes automatically.

for eg.

inside test.sql

declare DateSelect = 2016-11-12

select * from table_name where dates = DateSelect;

and then i can change DateSelect to whatever i want.

i came across this documentation, but i couldn't understand it fully as i am new to mysql.Variable substitution in SQL statements Any ideas how do i achieve that, thanks in advance

score 1 · Answer 1 · edited May 23 '17 at 12:08

1

Yes you can by wrapping your query in a stored procedure like

create procedure usp_sql(@datevar datetime)
as
begin
select * from table_name where dates = @datevar;
end

Then you can call it like

call usp_sql('2016-11-12');

Per your latest comment, see this existing post Pass parameter to MySQL script command line

edited May 23 '17 at 12:08

Community

1
1

answered Dec 13 '16 at 12:55

Rahul

76,197
13
71
125

:And can i also give multiple arguments inside usp_sql(__) – Shubham R Dec 13 '16 at 12:57
@SRingne, yes absolutely you can ... as many you want – Rahul Dec 13 '16 at 12:58
if i understand you completely , i should save this code as test.sql so how would use call usp_sql() ? – Shubham R Dec 13 '16 at 13:01
@SRingne, how you are trying to call the sql file? – Rahul Dec 13 '16 at 13:02
i would be running it via `mysql -u username -p database_name < test.sql` in command line – Shubham R Dec 13 '16 at 13:03
@SRingne, see edit in answer and the linked post – Rahul Dec 13 '16 at 13:19
i got another simpler solution [http://dev.mysql.com/doc/refman/5.7/en/user-variables.html](from this link) – Shubham R Dec 13 '16 at 13:21
@SRingne, that probably won't work with sql files. test it once. – Rahul Dec 13 '16 at 13:25

score 0 · Answer 2 · answered Dec 13 '16 at 13:25

0

You may use internal vars using WbVar assigns. You just have to assign the value to a variable you want and put that variable in the query. It will take the value you assigned. I hope it helps.

answered Dec 13 '16 at 13:25

Ankush Rathi

622
1
6
26

Can we add variable names in mysql queries?

2 Answers2