Monday, March 12, 2012

Is there is away to pass parameter to sql script

Is there is a way to pass parameters to pass parameters to sql script during command line exeution with ISQL or OsqlPlease be more specific with example.|||thanx in advance
simple example:

i have sql script that contains the folowing :
--************
insert into table xxx values (1,2,@.PARAMETER)
--************

i am running this script with isql
my question is how i can change(replace) the parameter @.PARAMETER each time i am running the commad line with isql|||In Windows you can write a batch file and use the parameter that way. For instance, create a file (named execsql.bat for instance) and put these lines in it:

isql.exe -U user_name -P pw -S server -Q "insert into table xxx values (1,2,%1)"

pause

[You need to plug in a real username, password, and server.]

Then you can run the file from a command prompt and pass in a value such as:

execsql.bat SomeParameterValue

The "SomeParameterValue" portion will get substituted into the query.|||thanx , but this is not solving my problem because

the sql is located in sqlfile with 1000 quiries befor and 1000 quireis after . meaning :

sqlfile.sql contains the folowing :

...
...
..
insert into table xxx values (1,2,%1)
..
..
.

and i am runnig it like this :
isql.exe -U user_name -P pw -S server -i sqlfile.sql

meaning i want to pass the parameters from the batch file to the sql file.|||Ahhh. In the documentation of the isql command (Books Online), it says you can use environment variables as parameters in your query. So you might be able to do something in your batch file like:

SET ParamValue = %1
isql.exe -U user_name -P pw -S server -i sqlfile.sql

And then inside sqlfile.sql your statement would read:
...
insert into table xxx values (1,2,%ParamValue%)
...

Does that help?|||Why don't you create a stored procedure out of this script?|||sold to the man with the stored procedure !!

my suggestion exactly|||well stored procedure is brilliant idea , for sure i will use it , but this idea is not solving all the problems
look at the folowing sql that create database on sqlserver machine it needs three parameters
i can create stored procedure to do this task , but the problem is that i need to create this procedure on the MASTER database(i think) , and i dont have permissions to do it.

CREATE DATABASE @.DB_NAME on primary
(Name = eProvisionDb,
FileName = ' @.path\xxxDb.mdf',
size = 50MB,
MaxSize=UNLIMITED,
FileGrowth=10MB)
Log On
(Name = eProvisionLog,
FileName ='@.path\xxxLog.mdf',
size = 20MB,
MaxSize=UNLIMITED,
FileGrowth=5MB)
any way the stored procedure is solving 90% of my problems|||then get someone with permissions to the master database to run the create script in that context.
you can then run the procedure any time you want to.|||Hmmm, I don't think the script will run. It'll have to be dynamically built.|||sure ,
you run it in dynamic sql

No comments:

Post a Comment