Visual Studio: use SQLCMD variables to change DB types at deploy time

Question

We have a DB project in Visual Studio and it produces a .dacpac file. We then use this dacpac with sqlcommand.exe to deploy our database. Our requirement is to be able to change column types at deployment time, much like we can change the database name with the sqlcommand.exe switches. For example:

:setvar CHARTYPE "VARCHAR"

ALTER TABLE tempTable ADD newColumn $(CHARTYPE)(200);
--or
DECLARE @tempVar $(CHARTYPE)(200);

This SQL works perfectly via SSMS however trying to do this via Visual Studio is proving difficult.

I've created a SqlCmd variable in the project properties and I'm using it in a stored procedure body:

DECLARE @TempVar $(CHARTYPE)(100)

But this doesn't work. My DB project won't build and gives me a syntax error at @TempVar.

If I use the SqlCmd variable in quotes '$(CHARTYPE)' it's fine and appears as a string literal 'VARCHAR'.

I have turned on SqlCmd mode when building the project and it hasn't made a difference. How can I get Visual Studio to realise I'm using the SqlCmd variables?

Answer 1

It's not possible to use SqlCmd variables at arbitrary points in a stored procedure. However, they can be used in a pre- or post-deployment script.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/da4bdb11-fe42-49db-bb8d-288dd1bb72a2/sqlcmd-vars-in-create-table-script?forum=ssdt