Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Unanswered: 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 Osql

  2. #2
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185
    Please be more specific with example.

  3. #3
    Join Date
    Sep 2003
    Posts
    12
    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

  4. #4
    Join Date
    Mar 2004
    Posts
    8
    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.

  5. #5
    Join Date
    Sep 2003
    Posts
    12

    Smile

    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.

  6. #6
    Join Date
    Mar 2004
    Posts
    8
    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?

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Why don't you create a stored procedure out of this script?

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    sold to the man with the stored procedure !!

    my suggestion exactly

  9. #9
    Join Date
    Sep 2003
    Posts
    12
    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

  10. #10
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hmmm, I don't think the script will run. It'll have to be dynamically built.

  12. #12
    Join Date
    Sep 2003
    Posts
    12
    sure ,
    you run it in dynamic sql

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •