在預存程序中直接組裝 HTML `` 標籤,依業務欄位值 (如點擊數 Hits) 決定前端顏色,減少應用層邏輯。雖違反關注點分離,但在舊系統快速改造時可用。
ALTER PROC GetDataByNumCategory
@Carcategory NVARCHAR(50)
AS
DECLARE @sql NVARCHAR(1000), @Num NVARCHAR(20);
DECLARE @Green NVARCHAR(100) = '', @Green1 NVARCHAR(100) = '';
DECLARE @Red NVARCHAR(100) = '', @Red1 NVARCHAR(100) = '';
SET @Num = (SELECT TOP 1 Keyword FROM TaoCarActive); -- 取顯示筆數設定
EXEC('SELECT TOP ' + @Num + '
ID,
CASE WHEN Hits = 0 THEN ''' + @Green + ''' + CarCard + ''' + @Green1 + '''
ELSE ''' + @Red + ''' + CarCard + ''' + @Red1 + ''' END AS CarCard
FROM TaoCarActive
WHERE Carcategory = ''' + @Carcategory + ''' ORDER BY ID');
ALTER PROC GetDataByNumCategory
@Carcategory NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Num INT = (SELECT TOP 1 TRY_CAST(Keyword AS INT) FROM TaoCarActive);
IF @Num IS NULL OR @Num <= 0 SET @Num = 20; -- 預設值
DECLARE @sql NVARCHAR(MAX) = N'
SELECT TOP (@Num)
ID,
CASE WHEN Hits = 0
THEN CONCAT(@Green, CarCard, @Green1)
ELSE CONCAT(@Red, CarCard, @Red1) END AS CarCard
FROM TaoCarActive
WHERE Carcategory = @Carcategory
ORDER BY ID;';
EXEC sp_executesql @sql,
N'@Num INT, @Carcategory NVARCHAR(50), @Green NVARCHAR(100), @Green1 NVARCHAR(100), @Red NVARCHAR(100), @Red1 NVARCHAR(100)',
@Num, @Carcategory,
'', '',
'', '';
END;
sp_executesql 參數化,杜絕 SQL 注入。TRY_CAST 安全轉換顯示筆數,附預設值。CONCAT (SQL 2012+) 自動處理 NULL 串聯,代替 +。SET NOCOUNT ON 減少網路封包。不推薦長期將 HTML 邏輯放在資料庫層,建議:
ID, CarCard, Hits)。Hits 渲染對應 CSS Class。
3. CSS 定義 .hits-zero {color:green;} .hits-nonzero {color:red;},維護性、快取、安全性皆優。