拼接查询中IIF/CASE使用问题及标量值函数变量排查

Alright, let's dig into why your scalar function's query is spitting out unexpected results—string concatenation and variable handling quirks in SQL Server are super common culprits here. First, let's clean up your code snippet to make debugging easier:

DECLARE @useCompTitle BIT 
SET @useCompTitle = (SELECT z.useComplianceTitle FROM dbo.tblConfiguration z WHERE z.id = 1)

DECLARE @Output as VARCHAR(MAX) = '';
DECLARE @ReturnValue as VARCHAR(MAX)='';

SELECT @Output = @Output + '<tr><td style="vertical-align:top">' + p.ProcessNumber + ...
-- Rest of your concatenation logic here

Let's break down the most likely issues and fix them step by step:

• NULL values breaking your concatenation
  This is the #1 cause of wonky results in SQL string拼接. If any field you're concatenating (like p.ProcessNumber) is NULL, the entire @Output variable becomes NULL instantly—SQL treats any string + NULL as NULL. Fix this by wrapping every nullable field in ISNULL() or COALESCE() to default to an empty string:

  SELECT @Output = @Output + '<tr><td style="vertical-align:top">' + 
      ISNULL(p.ProcessNumber, '') + '</td>' + ...
  

• Unused or unhandled @useCompTitle variable
  You're fetching this BIT variable but not using it in your snippet—if it's supposed to toggle parts of your output (like including a compliance title), you need to add a conditional check with CASE:

  SELECT @Output = @Output + CASE WHEN @useCompTitle = 1 
      THEN '<td>' + ISNULL(p.ComplianceTitle, '') + '</td>' 
      ELSE '' END + ...
  

  Also, if the tblConfiguration row with id=1 doesn't exist, @useCompTitle will be NULL. Add a default to avoid unexpected behavior:

  SET @useCompTitle = ISNULL((SELECT z.useComplianceTitle FROM dbo.tblConfiguration z WHERE z.id = 1), 0)
  

• Implicit behavior of SELECT @var = @var + ...
  If your query returns 0 rows, @Output stays empty (which is fine), but if you're relying on row order for concatenation, SQL doesn't guarantee order unless you add an ORDER BY clause. For example:

  SELECT @Output = @Output + '<tr>...</tr>'
  FROM dbo.YourTable p
  ORDER BY p.ProcessNumber -- Explicit order to ensure consistent output
  

• Consider using STRING_AGG for simpler, faster concatenation (SQL Server 2017+)
  If you're on a newer SQL Server version, STRING_AGG eliminates the need for loop/accumulator variables entirely, and handles NULLs more cleanly:

  SET @Output = (
      SELECT STRING_AGG(
          '<tr><td style="vertical-align:top">' + ISNULL(p.ProcessNumber, '') + '</td></tr>',
          ''
      ) WITHIN GROUP (ORDER BY p.ProcessNumber) -- Add order if needed
      FROM dbo.YourTable p
  )
  

Quick debug tip: Test each part of your concatenation separately. Run SELECT ISNULL(p.ProcessNumber, '') FROM dbo.YourTable to check for unexpected NULLs, and verify @useCompTitle is getting the value you expect with SELECT @useCompTitle after setting it.

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