如何在SQL Server中使用case进行转换？ [英] How do I use convert with using of case in SQL server?

问题描述

我有一个SQL语句（请参阅您尝试过的内容）。我想在使用案例之前将Preis转换为nvarchar。



我尝试过它

I have a SQL Statement (see What you have tried). I would like to convert Preis to nvarchar before using the case.

I tried it with

select Kategorie, modell, bezeichnung, convert(nvarchar(20),(preis),0) as Preis =
case
        when '{state:RepEuro}' != 'Smart Repair' then Preis
        else '140'
    end 
from tblminderwerte
where id = '{state:MinderwertSelect}'

但这导致一个错误。什么是正确的方式？



我尝试过：

But this results in an error. What would be the Right way?

What I have tried:

select Kategorie, modell, bezeichnung, Preis =
case
        when '{state:RepEuro}' != 'Smart Repair' then Preis
        else '140'
    end 
from tblminderwerte
where id = '{state:MinderwertSelect}'

推荐答案

值CONVERTed是声明中的第二个元素。

对于你的实例，看起来有不同的方法来解决CASE和amp;的组合。 CONVERT

1.您可以将整个case语句作为转换值

2.您可以将转换后的值作为需要转换的案例值。



这里的概念证明让我相信你会想要第一个选项。

The value to be CONVERTed is the second element within the statement.
For your instance there looks to be different different methods to solve the combination of CASE & CONVERT
1. You can place the entire case statement as the converts value
2. You can place the converted value as the case value requiring conversion.

Proof of concept here leads me to believe that you will want the first option.

DECLARE @Temp TABLE (CaseEvaluation BIT, preis MONEY)

INSERT @temp 
VALUES (1, 123.45 ), (0, 543.21)

SELECT 
    Preis1 =
        CONVERT (
            NVARCHAR(20),
		    CASE
		        WHEN (CaseEvaluation = 1) THEN Preis
			    ELSE '140'
            END,
		  0
	   ),
    Preis2 =
        CASE
           WHEN (CaseEvaluation = 1) THEN  CONVERT (NVARCHAR(20), Preis, 0)
		   ELSE '140'
        END

FROM @Temp
--  Case   Preis1  Preis2
--  ------ ------  ------
--  True   123.45  123.45
--  False  140.00  140

这是值得的;你可以在这个例子中使用CAST。



如果这个查询是由外部应用程序调用的，它是数字/金钱，我建议你不要使用转换，以便应用程序可以轻松使用显示的值并允许正确的区域显示。

For what's it is worth; you could probably use CAST in this instance.

If this query is being called by an external application and it is numeric/money, I would recommend returning the values without the convert, so that the application can easily work with the values presented and allow for proper regional display.

我完全不确定你在那里做什么 - 你的比较总是suceed因为两个字符串永远不会匹配。

但是如果你想要做的是将数值转换为NVARCHAR那么它是微不足道的：

I'm not at all sure what you are trying to do there - your comparison will always suceed because the two strings will never match.
But if what you are trying to do is convert a numeric value to a NVARCHAR then it's trivial:

select Kategorie, modell, bezeichnung, 
    case
        when '{state:RepEuro}' != 'Smart Repair' then convert(nvarchar(20),(preis),0)
        else '140'
    end AS Preis
from tblminderwerte
where id = '{state:MinderwertSelect}'

但我有点担心你是从你的应用程序显示代码，并且有参数sub继续进行，用您应用中的变量内容替换 {state：RepEuro} 。

如果是这样，那就不要这样做了！永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。总是使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

But I'm a little worried that you are showing code from your app, and there is parameter substitution going on, replacing {state:RepEuro} with the variable content in your app.
If so, then don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

哪个SQL看作三个单独的命令：

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

完全有效的SELECT

A perfectly valid SELECT

DROP TABLE MyTable;

完全有效的删除表格通讯和

A perfectly valid "delete the table" command

--'

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期做备份，不是吗？

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

这篇关于如何在SQL Server中使用case进行转换？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！