I have a DOS batch file I want to use to invoke a TSQL program. I want to pass the names of the databases to use. This seems to work. I want to pass the PREFIXES for the names of the tables I want to work with. So for test tables I want to pass the name of a prefix to use the test table.
set svr=myserver
rem set db=myTESTdatabasename
set db=mydatabasename
rem set tp=TEST
set tp=
sqlcmd -S %svr% -d somename -i test01.sql
test01.sql looks like this:
use $(db)
go
select top 10 * into $(db).dbo.$(tp)dsttbl from $(db).dbo.$(tp)srctbl
It works fine for the test stuff, but for the real stuff, I just want to set the value of tp to null so that it will use the real table name and not the bogus table name.
The reason I'm doing this is because I don't know the names of everything that will be used on the actual databases. I'm trying to make it generic so I don't have to do a bunch of search replaces on what will be a very large sql program (the real sql program is already hundreds of lines).
In the test case, this would resolve to
select top 10 * into myTESTdatabasename.dbo.TESTdsttbl from myTESTdatabasename.dbo.TESTsrctbl
For the production runs, it should resolve to
select top 10 * into mydatabasename.dbo.dsttbl from mydatabasename.dbo.srctbl
The problem seems that it doesn't like null values for $(tp), or perhaps that it's getting an undefined variable.
I experimented some with the syntax and as Preet Sangha pointed out you should use the /V command line option.
The reason is that setting a variable to the empty string in a batch script undefines it.
If you want to set the database name in the top of the batch file you can still use set, like this:
set db_to_use=
Then you can use this (undefined) variable in the sqlcmd using the /V option:
sqlcmd -S %svr% -d somename -v db="%db_to_use%" -i test01.sql
...or you can just set the value directly in the sqlcmd line:
sqlcmd -S %svr% -d somename -v db="" -i test01.sql