[sql-server] SQL Server: converting UniqueIdentifier to string in a case statement

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?

This question is related to sql-server guid case

The answer is


In my opinion, uniqueidentifier / GUID is neither a varchar nor an nvarchar but a char(36). Therefore I use:

CAST(xyz AS char(36))

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

Instead of Str(RequestID), try convert(varchar(38), RequestID)


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to guid

What is the default value for Guid? A TypeScript GUID class? Generate a UUID on iOS from Swift How to set null to a GUID property Check if Nullable Guid is empty in c# How to create a GUID in Excel? Guid.NewGuid() vs. new Guid() What are the best practices for using a GUID as a primary key, specifically regarding performance? Guid is all 0's (zeros)? How to test valid UUID/GUID?

Examples related to case

PostgreSQL CASE ... END with multiple conditions SQL Server: use CASE with LIKE SQL Server IIF vs CASE SELECT using 'CASE' in SQL SELECT query with CASE condition and SUM() GROUP BY + CASE statement SQL use CASE statement in WHERE IN clause SQL Server - Case Statement where to place CASE WHEN column IS NULL in this query Regarding Java switch statements - using return and omitting breaks in each case