-3

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RickyTW
  • 23
  • 3
  • 2
    please post the actual code. It's hard to find the syntax error in a statement when you're given pseudo-code to debug – Kritner Sep 27 '15 at 23:08
  • In SQL Server, `CASE` is **not** a statement - it's an **expression** and as such, it can return one of several possible values - but it cannot be used to control program flow. You'll need to rethink your code to take this into account - `CASE` can only **return** a value - you need to deal with displaying it outside of the `CASE` – marc_s Sep 28 '15 at 04:22

2 Answers2

0

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.

japzdivino
  • 1,736
  • 3
  • 17
  • 25
  • 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? – RickyTW Sep 28 '15 at 13:13
0

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 ')
Community
  • 1
  • 1
Kritner
  • 13,557
  • 10
  • 46
  • 72