In SQL Server Management Studio\SQLCMD mode, can I use variables WITHIN a set of statements

Question

Hello,

I'm trying to make some of my setup scripts more readable and less prone to error. Here's the type of code I have now in my SQLCMD scripts, which I run from management studio:

!!bcp "select * from Database1..Table1 where CreateDate > '7/11/2010'" queryout C:\junk\Table1.tab -n  -SServerName1 -UTestUser -PTestPassword
!!bcp "select * from Database1..Table2 where CreateDate > '7/11/2010'" queryout C:\junk\Table2.tab -n  -SServerName1 -UTestUser -PTestPassword

Instad of needing to hardcode the date in each statement, I'd like to be able to do something like this:

declare @Date1 datetime
select  @Date1 =  '7/11/2010'

!!bcp "select * from Database1..Table1 where CreateDate > @Date1" queryout C:\junk\BCPData\Table1.tab -n  -SServerName1 -UTestUser -PTestPassword
!!bcp "select * from Database1..Table2 where CreateDate > @Date1" queryout C:\junk\BCPData\Table2.tab -n  -SServerName1 -UTestUser -PTestPassword

Notice that in the second set of code, the date is no longer hardcoded.

Is there a way to do this, or are there good alternatives?

thanks for any ideas! Sylvia

Answer 1

I'm sure you can't do this with DECLAREd SQL variables, however check out this link for guidance on using scripting variables within SQLCMD - which I would expect to apply to SQLCMD mode as well.

Answer 2

Does this work? (Bit of a guess based on here)

:setvar date "7/11/2010"
!!bcp "select * from Database1..Table1 where CreateDate > '$(date)'" queryout C:\junk\Table1.tab -n  -SServerName1 -UTestUser -PTestPassword
!!bcp "select * from Database1..Table2 where CreateDate > '$(date)'" queryout C:\junk\Table2.tab -n  -SServerName1 -UTestUser -PTestPassword

Edit

Yep. The following works for me

:setvar date "07/07/2008"
!!bcp "select * from master.sys.objects where Create_Date > '$(date)'" queryout C:\master.tab -n  -S(local) -T