How do I pass the same input to all the sql files only once at the start?

Question

hi

I am calling 4-5 scripts from a file at once. But I need to give only one input to the file in the first sql that I am calling.

That input will be the input for all the other sql files I have called after the first one.

Is there any way to do that?

please help.

Answer 1

Do you mean:

query2 is based on the result of query1, query3 is based on the result of query2 etc...

If so, you can use views to

create view view1 as select * from table1;
create view view2 as select * from view2;
create view view3 as select * from view3;
create view view4 as select * from view4;
select * from view4

Of course you have to add the where clause yourself.

See for more on views http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Answer 2

I think you can achieve what you want by using the sqlcmd utility and scripting variables. The last link states that you can also use environment variables.

Answer 3

no START fbm.sql START fba.sql START fei.sql START fbe.sql START fae.sql START tfat.sql START ins_FBH.sql is the code.

in fbm.sql i have an input like bill id = '&1'. also i have the same input of bill id in other sql's.

but whn i run the master sql it will run fbm.sql and ask me for the bill id input. suppose i give it as 'ABC' and again after completing this fbm.sql it will ask me the input for bill id again for fba.sql which i dont want to give again n again. wat i want is that this fba.sql and other corresponding sql's should take the input bill id as 'ABC' without me entering it.

Answer 4

Have you thought about using a stored procedure for this. It does depend on having version 5.0 (or later) of MySQL of course. But this allows you to define variables and to use them within the procedure, very flexible and great fun to use! Caveat, have not tested this myself, my experience has been with Oracle PL/SQL but concepts are similar.

Then you can do stuff like this (from the MySQL newsletter at:

http://www.mysql.com/news-and-events/newsletter/2004-01/a0000000297.html

DELIMITER // [1]

CREATE PROCEDURE payment [2]
(payment_amount DECIMAL(6,2),
payment_seller_id INT)
BEGIN
DECLARE n DECIMAL(6,2);
SET n = payment_amount - 1.00;
INSERT INTO Moneys VALUES (n, CURRENT_DATE);
IF payment_amount > 1.00 THEN
UPDATE Sellers
SET commission = commission + 1.00
WHERE seller_id = payment_seller_id;
END IF;
END;
//