locked
SQLCMD Miscellaneous Commands :r RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi.

    Try stored procedure.

    karepa
    Thursday, October 14, 2010 1:06 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    Ok. Thank you.
    Thursday, October 14, 2010 7:15 PM