Search code examples
sql-serversql-server-2012sqlcmd

SQL Joining Strings for a path referencing external file

I'm trying to join some strings together to define a path - for example, given $(name) = "PATH", I want :r .\PathOne\PATH.sql. The query fails at the first :r due to Syntax Error. If I hardcode the paths, and leave $(name) in the conditionals, it works as expected. It's just the string construction for the path that's failing for some reason.

 IF '$(name)' LIKE 'TEST%'
 BEGIN
   :r .\PathOne\'$(name)'.sql
 END
 IF '$(name)' NOT LIKE 'TEST%'
 BEGIN
   :r .\PathTwo\'$(name)'.sql
 END

How do I go about joining strings for a path in SQL? Naming the files directly works.

Solution

  • Enclose the hardcoded Parts of the path inside a double quote and your variables without any space next to hardcoded part for Constructing a path, For example

    :setvar filename "test"
:setvar root "D:\temp"
:r  $(root)"\test\"$(filename)".sql"

    will result in path like

    D:\temp\test\test.sql

    To resolve your problem try something like this

    :r ".\PathOne\"$(name)".sql"

    Hope this helps