SQL Server 2012: how to display the @Var
result inside a Case
statement with literals before and after the @Var
?
A simple query:
Declare @Var1 ...
Declare @Var2....
Select ....
CASE
WHEN ...........
THEN 'Display this sentence ' + @Var1 +'followed by there words'
ELSE 'Display these other words ' +@Var2 'followed by these other words'
END
The error message is
'Display this sentence' is not a declared variable.
I know it isn't! It should be a literal statement, only with the @Var1
result inside the literal statement.
What am I missing?
There's nothing wrong the way you do the concatenation, i think the error is in the logic, do you have IF ELSE
condition ? , maybe the error you encountered is on the two variables @var1
or @var2
, the reason why Display this sentence is not a declared variable is because that is the part of concatenation with the variables.
Tips:
Make sure that the declared variables @var1
and @var2
are inside the BEGIN and END
of the condition, meaning the program reads your declaration before the CASE WHEN
logic is being read.
If you are not sure with the logic, just declare your 2 variables at the top of the script to make sure that it is being read/declared.
Based on the comment:
I misspoke on the error message (did it from memory on another PC). Here is the actual error message: Msg 245, Level 16, State 1, Line 8 Conversion failed when converting the varchar value ''Display these other words ' to data type int. So, the error message is that it thinks the text (to display) is a data type that needs to be converted. Any ideas?
Your issue (which could have been much easier to figure out, had the actual code and error been posted)
Declare @Var1 ...
Is actually Declare @var1 int
.
so your statement:
'Display this sentence ' + @Var1 +'followed by there words '
is failing for:
Conversion failed when converting the varchar value ''Display these other words ' to data type int.
You simply need to convert/cast @Var1
to varchar
for that part of your case statement.
WHEN 'Display this sentence ' + convert(varchar(14), @Var1) +'followed by there words '
The reason you're seeing this behavior (taken from here is:
You need to explicitly convert your parameters to VARCHAR before trying to concatenate them. When SQL Server sees @my_int (@Var1 in your case) + 'X' it thinks you're trying to add the number "X" to @my_int (@Var1) and it can't do that.
Another option is concat.
WHEN concat('Display this sentence ', @Var1, ' followed by there words ')