SQL Joining Strings for a path referencing external file

Question

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.

Are you sure you set your query window to SQLCMD mode? WHere did you define the name variable? — Aaron Bertrand
– Aaron Bertrand, Commented Jun 6, 2016 at 23:11
@AaronBertrand Yes it is - this is defined and works elsewhere, just not in the string construction. — RobVious
– RobVious, Commented Jun 6, 2016 at 23:36
@AaronBertrand - this flow works as I want it to if I hardcode the paths. All I need is help constructing the paths with the string - even the IF '$(name)' works just as I want it to. I just can't get the string paths working. — RobVious
– RobVious, Commented Jun 7, 2016 at 18:14

krish · Accepted Answer · 2016-06-14 06:39:39Z

4

+50

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

answered Jun 14, 2016 at 6:39

krish

8817 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

square_particle · Accepted Answer · 2016-06-14 03:30:42Z

1

I know that it may not sound intuitive, but try putting the path in double-quotes, like below. This works on my machine. Without the double-quotes, I get the same failure as you.

:setvar name "test"

if '$(name)' like 'test%'
begin
    :r "c:\temp\test1\"$(name).sql
end
else if '$(name)' not like 'test%'
begin
    :r "c:\temp\test2\"$(name).sql
end

answered Jun 14, 2016 at 3:30

square_particle

5242 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL Joining Strings for a path referencing external file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related