Erreur lors de la conversion de nvarchar en int dans un ORDER BY dynamic

Code SQL:

DECLARE @SortOrder nvarchar(max); SET @SortOrder = 'name'; SELECT * FROM [database_name].[schema_name].[table_name] ORDER BY CASE @SortOrder WHEN 'id' THEN id WHEN 'name' THEN name END;

Sortie: Msg 245, niveau 16, état 1, ligne 3 La conversion a échoué lors de la conversion de la valeur nvarchar 'foo' en type de données int.

Quand je place plutôt @SortOrder = 'id', cela fonctionne parfaitement. Quand je fais un nom normal SELECT et ORDER BY, cela fonctionne aussi parfaitement.

Pourquoi essayerait-il de convertir cette valeur de nvarchar en type de données int, et comment puis-je l'empêcher de le faire?

CASE est une expression qui renvoie une seule valeur d'un type de données spécifique. Les valeurs potentielles doivent être compatibles, et puisque vous devez avoir une valeur de nom de foo , qui ne peut pas être convertie en INT , vous obtenez cette erreur. Le moyen le plus simple de résoudre ce problème consiste à séparer vos expressions ORDER BY par type de données:

 ORDER BY CASE @SortOrder WHEN 'id' THEN id END, CASE @SortOrder WHEN 'name' THEN name END;

Une autre alternative (qui peut être meilleure pour la caching de plan tant que vous avez optimize for ad hoc workloads de optimize for ad hoc workloads ) serait SQL dynamic:

 DECLARE @sql NVARCHAR(MAX) = N'SELECT ... ORDER BY ' + QUOTENAME(@SortOrder) + ';'; EXEC sp_executesql @sql;

Vous ne savez pas non plus pourquoi vous donneriez à votre @SortOrder un type de données maximum – aucun nom de colonne ne pourrait jamais être aussi grand.

Toutes les routes d'une instruction case doivent produire le même type de données. Essayez de lancer l'ID à une string comme celle-ci et cela fonctionnera.

 DECLARE @SortOrder nvarchar(max); SET @SortOrder = 'name'; SELECT * FROM [database_name].[schema_name].[table_name] ORDER BY CASE @SortOrder WHEN 'id' THEN cast(id AS NVARCHAR(20)) WHEN 'name' THEN name END;