SQL Server：在 case 语句中将 UniqueIdentifier 转换为字符串

发布于 2024-11-15 06:35:43 字数 662 浏览 2 评论 0原文

我们有一个日志表，其中有一个消息列，有时会有异常堆栈跟踪。我有一些标准来确定消息是否具有此内容。我们不想向客户显示这些消息，而是显示如下消息：

发生内部错误。联系我们带参考代码 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

其中 xxx 等是表中的 guid 列。我正在这样编写存储过程：

declare @exceptionCriteria nvarchar(50)
select @exceptionCriteria = '%<enter criteria etc>%'

select LogDate,
       case
       when Message like @exceptionCriteria
       then 'Internal Error Occured. Reference Code: ' + str(RequestID)
       else Message
       end
  from UpdateQueue

RequestID 是 SQL Server 中的 Guid 数据类型，此处不会转换为字符串。我看过一些关于如何将 Guid 转换为字符串的代码，但它是多行的，我认为它不适用于 case 语句。有什么想法吗？

原文

We have a log table that has a message column that sometimes has an exception stack trace. I have some criteria that determines if the message has this. We do not want to show these messages to the customer but instead have a message like:

Internal Error Occured. Contact US
with reference code
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

where xxx etc is a guid column in the table. I am writing stored proc like this:

declare @exceptionCriteria nvarchar(50)
select @exceptionCriteria = '%<enter criteria etc>%'

select LogDate,
       case
       when Message like @exceptionCriteria
       then 'Internal Error Occured. Reference Code: ' + str(RequestID)
       else Message
       end
  from UpdateQueue

RequestID is a Guid datatype in SQL Server and does not convert to string here. I've seen some code on how to convert a Guid to string, but it is multi-lined and I don't think it would work in a case statement. Any ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最终幸福 2024-11-22 06:35:43

我想我找到了答案：

convert(nvarchar(36), RequestID)

这是我找到此信息的链接：

http:// /msdn.microsoft.com/en-us/library/ms187928.aspx

I think I found the answer:

convert(nvarchar(36), RequestID)

Here's the link where I found this info:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

回复收藏 0 原文

顾铮苏瑾 2024-11-22 06:35:43

可以在此处使用转换函数，但 36 个字符足以保存唯一标识符值：

convert(nvarchar(36), requestID) as requestID

编辑：是的，如注释中所述，char 或 nchar，或者任何可以正确操作 ASCII 字符表的函数都可以执行诡计。然后，我的借口是，我通常在多语言/多字母环境中工作，并且规则是始终使用 nvarchar。抱歉，这就是我做事的简单方式。而且，如果有一天，某些数据库软件开始生成具有非 ASCII 元素的唯一标识符，我就会做好准备。

It is possible to use the convert function here, but 36 characters are enough to hold the unique identifier value:

convert(nvarchar(36), requestID) as requestID

Edit: yes, as noted in the comments, char, or nchar, or any function that can properly manipulate ASCII character tables would do the trick. Then, my excuse is that I usually work in a multilingual/multialphabet environment, and the rule is to go for nvarchar, always. That's my no-brainer way of doing things, sorry. And, if one of these days, some database software starts to generate unique identifier with non-ASCII elements, I will be ready.

回复收藏 0 原文