Incorrect Syntax Near '+' - SQL/TSQL

Question

Trying to do this (in SQL Server Management Studio)

DECLARE @fun as int
SET @fun = 40
PRINT 'String' + 'Hello!'

EXEC msdb.dbo.sp_senddbmail
    @profile_name = 'Some working profile',
    @recipients = '[email protected]',
    @subject =  'String' + 'Hello!',
    @body = 'Test email sendout'

Getting this error :

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '+'.

Notice the first concatenation works, second doesn't.

The steps I've taken:

Retyped it all out (so its not a copy paste error). Tried changing it to ('String' + 'Hello!') and get error Incorrect syntax near '('. So I'm feeling a bit lost.

EDIT 2: I've changed the example because the same error happens when its two strings, no casts involved

Solution 1

Values passed to stored procedures parameters (in T-SQL) must be, umm, "single values" (can't think of the technical term just now). You cannot use code like:

@subject = A + B + C

you can only have code like

@subject = A

So in this case, you'd need something like:

DECLARE
  @fun      int
 ,@Subject  nvarchar(255)

SET @fun = 40

SET @Subject = 'String' + 'Hello!'

EXEC msdb.dbo.sp_send_dbmail  --  Added the second underscore...
    @profile_name = 'Some working profile',
    @recipients = '[email protected]',
    @subject = @Subject,
    @body = 'Test email sendout'

Solution 2

try this, convert int value as varchar then concat with another varchar value

DECLARE @fun as int
SET @fun = 40
PRINT convert(varchar(10), @fun) + 'Hello!'

EXEC msdb.dbo.sp_senddbmail
@profile_name = 'Some working profile',
@recipients = '[email protected]',
@subject =  convert(varchar(10), @fun) + 'Hello!',
@body = 'Test email sendout'

Comments

[Jono]

Both same answer, but the issue I'm having is that it is the concat that is giving me issues - not the cast (or lack of) read comment above.

[HaveNoDisplayName]

what exatly error you get? and in your question, you talking about wraping in brackets , where?

[HaveNoDisplayName]

your this line PRINT @fun + 'Hello!' gives then error

[Jono]

I changed the question because everyone was fixating on the casting (which was not the heart of the issue). I get the same error with the code above it, seems to be more obscure of an issue.

[Jono]

The term is expression, and you are right, thank you! I had just figured it out and was just coming back to write it but you beat me to the punch!