SQL Server 2012:如何在带有@Var前后的文字的Case语句中显示@Var结果 [英] SQL Server 2012: How to display the @Var result inside a Case statement with literals before and after the @Var

问题描述

SQL Server 2012:如何在Case语句内用@Var前后的文字显示@Var结果?

SQL Server 2012: how to display the @Var result inside a Case statement with literals before and after the @Var?

一个简单的查询:

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

错误消息是

显示此句子"不是声明的变量.

'Display this sentence' is not a declared variable.

我知道不是！它应该是文字声明，只在文字声明中包含@Var1结果.

I know it isn't! It should be a literal statement, only with the @Var1 result inside the literal statement.

我想念什么?

推荐答案

基于评论:

我在错误消息上记错了(它是从另一台PC的内存中提取的).这是实际的错误消息:消息245，级别16，状态1，行8将varchar值显示这些其他字"转换为数据类型int时转换失败.因此，错误消息是它认为文本(要显示)是需要转换的数据类型.有什么想法吗?

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)

声明@ Var1 ...

Declare @Var1 ...

实际上是Declare @var1 int.

所以你的陈述:

'Display this sentence ' + @Var1 +'followed by there words '

失败:

将varchar值显示这些其他字词"转换为数据类型int时，转换失败.

Conversion failed when converting the varchar value ''Display these other words ' to data type int.

您只需要在case语句的该部分将@Var1转换/转换为varchar.

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:

您需要先将参数显式转换为VARCHAR，然后再尝试将其串联.当SQL Server看到@my_int(在您的情况下为@ Var1)+'X'时，它认为您正在尝试将数字"X"添加到@my_int(@ Var1)且无法执行此操作.

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.

另一个选项是 concat .

WHEN concat('Display this sentence ', @Var1, ' followed by there words ')

这篇关于SQL Server 2012:如何在带有@Var前后的文字的Case语句中显示@Var结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！