Why doesn't this simple concatenation work?

Question

Why doesn't this simple concatenation work?

SqlServerCampbell

Old Hand

Points: 384
More actions
July 31, 2017 at 12:10 pm

#327830

Hi,
I'm trying to assign a concatenated string to a variable, in 2008 R2. I've tried both Concat() and the + operator, but receive syntax errors. E.g.,
DECLARE @s_FilePath nvarchar(255) = '' ;
@s_FilePath = 'what ' + 'the?' ;
returns an odbc error, I have no idea why:
Execute: ODBC Error (102): SQLExecDirectW Client: Server: Error line: 2 Error level: 15 Message State: 1 SQL State: 42000 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@s_FilePath'.; DECLARE @s_FilePath nvarchar(255) ; @s_FilePath = N'First , ' + N'Second' ;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

SqlServerCampbell - Monday, July 31, 2017 12:10 PM
Hi,
I'm trying to assign a concatenated string to a variable, in 2008 R2. I've tried both Concat() and the + operator, but receive syntax errors. E.g.,
DECLARE @s_FilePath nvarchar(255) = '' ;
@s_FilePath = 'what ' + 'the?' ;
returns an odbc error, I have no idea why:
Execute: ODBC Error (102): SQLExecDirectW Client: Server: Error line: 2 Error level: 15 Message State: 1 SQL State: 42000 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@s_FilePath'.; DECLARE @s_FilePath nvarchar(255) ; @s_FilePath = N'First , ' + N'Second' ;

On the second line, you need to use SET:

SET @s_FilePath = 'what ' + 'the?' ;

Sue

Lowell SSC Guru Points: 323518 More actions · Answer 2

you are missing the SET statement, it looks like to me.

Could you try this format instead?
DECLARE @s_FilePath nvarchar(255) = '' ;
SET @s_FilePath = 'what ' + 'the?' ;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SqlServerCampbell Old Hand Points: 384 More actions · Answer 3

Lowell - Monday, July 31, 2017 12:16 PM
you are missing the SET statement, it looks like to me.
Could you try this format instead?
DECLARE @s_FilePath nvarchar(255) = '' ;
SET @s_FilePath = 'what ' + 'the?' ;

That's the ticket. Thanks Lowell and Sue.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

CONCAT errors in SQL Server 2008 because it wasn't added to TSQL until 2012:
https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql