SQL - Trying to add variable into string

Question

I have a store procedure where I pass a path to the file like:

EXEC spMyPathFile         
@PFile = 'C:\TFiles\Paths\Test_1.1_Version.txt'

What I'd like to do it loop through and be able to pass a number of versions of the file like 1.1 and 1.2 etc using:

DECLARE @intLp INT  
DECLARE @a varchar(2)  
SET @intLp = 1 WHILE (@intLp <2) 
BEGIN  IF @intLp = 1  BEGIN
        SET @a = '1.1'
        END
      ELSE IF @intLp = 2
      BEGIN
        SET @a = '1.2'
    END

EXEC spMyPathFile         
@PFile = 'C:\TFiles\Paths\Test_'+@a+'_Version.txt'       
SET @intLp = @intLp + 1 
END

For some reason I get "Incorrect syntax near '+'." which is just before the @a. I'm obviously not joining my variable to my string properly.

Could someone give me an example of how this should look?

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Change

EXEC spMyPathFile         
@PFile = 'C:\TFiles\Paths\Test_'+@a+'_Version.txt'

to

declare @FileName varchar(100) = 'C:\TFiles\Paths\Test_' + @a + '_Version.txt' 
EXEC spMyPathFile         
@PFile = @FileName

Edit:

From MSDN - Specify Parameters

The parameter values supplied with a procedure call must be constants or a variable; a function name cannot be used as a parameter value. Variables can be user-defined or system variables such as @@spid.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 6, 2015 at 4:35

dzhu

8331 gold badge10 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

SQL - Trying to add variable into string

1 Answer 1

Edit:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit:

Comments

Your Answer

Sign up or log in

Post as a guest

Related