passing user defined parameter input in batch file to SQL file

Question

I want to know how it can be achieve I have a file A.sql. A.sql has insert command with WHERE clause and update command with WHERE clause.

SQL file:

INSERT INTO Table1 SELECT columns FROM table2 WHERE col1 BETWEEN 'Value1' AND 'Value2'

UPDATE table1 SET (coluname) = (SELECT colname FROM table2 WHERE some_col = 'Value3')
WHERE EXISTS(SELECT 1 FROM table2 WHERE some_col ='Value3')

User define Value1, Value2, Value3 while running batch file.

Batch file:

SET PRO=A.sql
sqlplus %Cond%@%HOMEDIR%sql/%PRO% >>%HOMEDIR%log/%log_file%

geisterfurz007 · Accepted Answer · 2017-01-10 10:59:56Z

1

You can use passed values to the *.sql-file using &1 &2 ... &n.

Now depending on how the parameters are set by the user:

Using set /p param1= for user input during "runtime":

sqlplus %Cond%@%HOMEDIR%sql/%PRO% %param1% %param2% <etc> >>%HOMEDIR%log/%log_file%

If given as parameter use "%~1" "%~2" <etc>. Where %1stands for the first argument and ~ removes potential surrounding quotes.

You can as well modify those parameters and save them to a variable for later use:

set /a p1=%~1-1
set /a p2=%~2-1
sqlplus %Cond%@%HOMEDIR%sql/%PRO% %p1% %p2% <etc> >>%HOMEDIR%log/%log_file%

/a shows that an arithmetic operation beeing done so that 1 is subtracted. For B.sql as asked for in the comment.

edited Jan 10, 2017 at 10:59

answered Jan 9, 2017 at 6:37

geisterfurz007

5,9246 gold badges37 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rawaard Over a year ago

I want to pass param1, param2 value to another B.sql file. But values passed to B.sql file. If param1= 5 , param2= 6 IN A.sql variable &1== 5 and variable &2 ==6. But in B.sql I want &1 = param1-1(5-1) == 4 and param2-1(6-1) == 5

Rawaard Over a year ago

user input value of param1 variable as 5, it will be passed to A.sql, so variable &1 in sql file will have value =5, same param2 value =6. I want to use the same value enter by the user at runtime and modify the param1,param2 values and pass it to another sql file B.sql.

Rawaard Over a year ago

in B.sql ... i can use WHERE id = (&1)-1 ??

geisterfurz007 Over a year ago

Not sure about that but leave me a bit time for an edit.

geisterfurz007 Over a year ago

Why don't you just simply try it out?! I tested with sqlcmd and it seemed to be working.... But notice that you should do research before asking one question after another. You asked how to use params, and I told you. The question with B.sql was off-topic already! I will not answer all you follow-up-questions as well!

|

Collectives™ on Stack Overflow

passing user defined parameter input in batch file to SQL file

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related