locked
SQLCMD Miscellaneous Commands :r RRS feed

  • Question

  • I have a batch file that executes Test.sql. In Test.sql I have following sql statements.

    The question is can I pass parameters to the SQL script that I am calling using command ":r".

    --Test.sql file contains this script 

    IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'NewDB')

    DROP DATABASE NewDB

    GO

    CREATE DATABASE NewDB

    GO

    :On Error exit

    :r c:\Scripts\CREATE_TABLES.sql [parameters...]

     

    Wednesday, October 13, 2010 5:06 PM

Answers

  • Yes, SQLCMD variable values from the calling script can be used in the called one.  The example below passes the database name:

    --Test.sql file contains this script 
    IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'NewDB')
    DROP DATABASE NewDB
    GO
    
    CREATE DATABASE NewDB
    GO
    
    :On Error exit
    :SETVAR DatabaseName "NewDB"
    
    :r c:\Scripts\CREATE_TABLES.sql
    
    
    
    --CREATE_TABLES.sql
    USE $(DatabaseName)
    CREATE TABLE dbo.Foo(
      Bar int NOT NULL);
    GO
    
    
    

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Aniruddha555 Thursday, October 14, 2010 5:16 AM
    Thursday, October 14, 2010 1:31 AM

All replies

  • Hi.

    Try stored procedure.


    karepa
    Thursday, October 14, 2010 1:06 AM
  • Yes, SQLCMD variable values from the calling script can be used in the called one.  The example below passes the database name:

    --Test.sql file contains this script 
    IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'NewDB')
    DROP DATABASE NewDB
    GO
    
    CREATE DATABASE NewDB
    GO
    
    :On Error exit
    :SETVAR DatabaseName "NewDB"
    
    :r c:\Scripts\CREATE_TABLES.sql
    
    
    
    --CREATE_TABLES.sql
    USE $(DatabaseName)
    CREATE TABLE dbo.Foo(
      Bar int NOT NULL);
    GO
    
    
    

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Aniruddha555 Thursday, October 14, 2010 5:16 AM
    Thursday, October 14, 2010 1:31 AM
  • This is cool! Thanks Dan!

    The person who is going to deploy the builds will execute the batch file containing the list of sql files. So I cannot use stored proc here. 

    Thanks for the reply.

    Thursday, October 14, 2010 5:16 AM
  • I forgot to mention that you can pass run-time variable values on the SQLCMD command-line.  This is especially useful when you want to use the same SQL scripts but supply values that may differ depending on the target.  For example:

    SQLCMD -S MyServer -E -i Test1.sql -v DatabaseName="NewDB"

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Thursday, October 14, 2010 10:57 AM
  • Ok. Thank you.
    Thursday, October 14, 2010 7:15 PM