如何在SQL字符串拼接中使用大于号(>)？语法错误排查

Your error stems from two straightforward issues:

1. The > symbol isn’t wrapped in single quotes—SQL interprets it as a comparison operator instead of part of the dynamic string.
2. You’re concatenating integer variables directly with string literals, which can trigger unexpected implicit conversion bugs.

Here’s the corrected code that fixes both problems:

declare @maxWeek int = 53 
declare @weekno int = 4
declare @mysql varchar(max)

set @mysql = 'select CASE WHEN ' + CAST(@weekno AS VARCHAR(10)) + ' > ' + CAST(@maxWeek AS VARCHAR(10)) + ' THEN ' + CAST(@maxWeek AS VARCHAR(10)) + ' ELSE ' + CAST(@weekno AS VARCHAR(10)) + ' END'

exec (@mysql)

Key Fixes Explained: #

• Added single quotes around > to make it a string literal: ' > '
• Used CAST() to convert each integer variable (@weekno, @maxWeek) to VARCHAR before concatenation. This eliminates conversion errors and ensures the numbers are properly inserted into the dynamic query.

Better Practice: Use Parameterized Dynamic SQL #

For cleaner, safer code (and to avoid SQL injection risks), use sp_executesql with parameters instead of hardcoding variable values into the string:

declare @maxWeek int = 53 
declare @weekno int = 4
declare @mysql nvarchar(max)

set @mysql = N'select CASE WHEN @weekno > @maxWeek THEN @maxWeek ELSE @weekno END'

exec sp_executesql @mysql, N'@weekno int, @maxWeek int', @weekno, @maxWeek

This approach keeps your query maintainable, avoids casting headaches, and maintains proper variable scoping.

内容的提问来源于stack exchange，提问作者Robert Bolista