SQL Server为何跳过IF语句？版本分支查询报错问询

你遇到的问题不是IF语句没被读取，而是SQL Server会对整个批处理中的所有代码进行预编译——哪怕某个分支在运行时根本不会执行，编译阶段就会检查所有分支里引用的对象（比如列）是否存在。所以在低版本SQL Server（2005/2008/2012）上，编译时会扫描到target_recovery_time_in_seconds这些不存在的列，直接抛出错误，根本没机会进入IF的判断逻辑。

解决思路是把两个分支的查询逻辑分别封装成动态SQL字符串，只有当版本条件满足时，才会拼接并执行对应的SQL语句，这样编译时只会处理当前分支的代码，不会触发不存在列的错误。

以下是调整后的代码：

DECLARE @SQL NVARCHAR(MAX)

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),2) LIKE '1[2-9]'
BEGIN
    SET @SQL = N'
    SELECT @@servername AS [Server] 
        , d.name AS [Database] 
        , CONVERT(char(10), d.create_date, 121) AS [Created] 
        , sp.name AS [Owner] 
        , d.recovery_model_desc AS [Recovery] 
        , CASE d.state_desc WHEN ''OFFLINE'' THEN ''***OFFLINE***'' ELSE d.state_desc END AS [Status] 
        , d.user_access_desc AS [Access] 
        , CASE d.is_read_only WHEN 0 THEN ''READ_WRITE'' WHEN 1 THEN ''READ_ONLY'' END AS Updateability 
        , CASE d.is_fulltext_enabled WHEN 0 THEN '''' WHEN 1 THEN ''YES'' END AS [FullText] 
        , CASE d.is_auto_create_stats_on WHEN 0 THEN '''' WHEN 1 THEN ''YES'' END AS [CreateStats] 
        , CASE d.is_auto_update_stats_on WHEN 0 THEN '''' WHEN 1 THEN ''YES'' END AS [UpdateStats] 
        , CASE d.page_verify_option WHEN 0 THEN ''***NONE***'' WHEN 1 THEN ''***TORN PAGE DETECTION***'' ELSE d.page_verify_option_desc END AS [Page Verify] 
        , d.compatibility_level AS [Level] 
        , d.target_recovery_time_in_seconds as ckp_s 
        , d.log_reuse_wait_desc AS [Log Wait] 
        , d.collation_name AS [Collation] 
        , CASE d.is_read_committed_snapshot_on WHEN 0 THEN '''' WHEN 1 THEN ''YES'' END AS [RCS] 
        , CASE d.snapshot_isolation_state WHEN 0 THEN '''' WHEN 1 THEN ''YES'' END AS [SI] 
        , CASE d.is_query_store_on WHEN 0 THEN ''NO'' WHEN 1 THEN ''YES'' END AS QS 
        , CASE d.is_auto_close_on WHEN 0 THEN '''' WHEN 1 THEN ''***YES***'' END AS [AutoClose] 
        , CASE d.is_auto_shrink_on WHEN 0 THEN '''' WHEN 1 THEN ''***YES***'' END AS [AutoShrink] 
        , d.delayed_durability_desc as [Durability] 
    FROM sys.databases AS d 
    LEFT JOIN sys.server_principals AS sp ON sp.sid = d.owner_sid 
    WHERE d.database_id > 4 
    ORDER BY d.name;'
END
ELSE
BEGIN
    SET @SQL = N'
    SELECT @@servername AS [Server] 
        , d.name AS [Database] 
        , CONVERT(char(10), d.create_date, 121) AS [Created] 
        , sp.name AS [Owner] 
        , d.recovery_model_desc AS [Recovery] 
        , CASE d.state_desc WHEN ''OFFLINE'' THEN ''***OFFLINE***'' ELSE d.state_desc END AS [Status] 
        , d.user_access_desc AS [Access] 
        , CASE d.is_read_only WHEN 0 THEN ''READ_WRITE'' WHEN 1 THEN ''READ_ONLY'' END AS Updateability 
        , CASE d.is_fulltext_enabled WHEN 0 THEN '''' WHEN 1 THEN ''YES'' END AS [FullText] 
        , CASE d.is_auto_create_stats_on WHEN 0 THEN '''' WHEN 1 THEN ''YES'' END AS [CreateStats] 
        , CASE d.is_auto_update_stats_on WHEN 0 THEN '''' WHEN 1 THEN ''YES'' END AS [UpdateStats] 
        , CASE d.page_verify_option WHEN 0 THEN ''***NONE***'' WHEN 1 THEN ''***TORN PAGE DETECTION***'' ELSE d.page_verify_option_desc END AS [Page Verify] 
        , d.compatibility_level AS [Level] 
        , ''N/A'' AS ckp_s 
        , d.log_reuse_wait_desc AS [Log Wait] 
        , d.collation_name AS [Collation] 
        , CASE d.is_read_committed_snapshot_on WHEN 0 THEN '''' WHEN 1 THEN ''YES'' END AS [RCS] 
        , CASE d.snapshot_isolation_state WHEN 0 THEN '''' WHEN 1 THEN ''YES'' END AS [SI] 
        , ''N/A'' AS QS 
        , CASE d.is_auto_close_on WHEN 0 THEN '''' WHEN 1 THEN ''***YES***'' END AS [AutoClose] 
        , CASE d.is_auto_shrink_on WHEN 0 THEN '''' WHEN 1 THEN ''***YES***'' END AS [AutoShrink] 
        , ''N/A'' AS [Durability] 
    FROM sys.databases AS d 
    LEFT JOIN sys.server_principals AS sp ON sp.sid = d.owner_sid 
    WHERE d.database_id > 4 
    ORDER BY d.name;'
END

EXEC sp_executesql @SQL

• 注意动态SQL里的单引号需要转义（用两个单引号''代替一个'），避免语法错误。
• 用sp_executesql执行动态SQL比直接用EXEC()更安全，也支持参数化（这里虽然没用到参数，但这是最佳实践）。

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