需要有关动态sql转义字符的帮助

Question

查询应返回一个结果，其中table='alrt'
但它在转义报价时出错

declare @dbname nvarchar(max)  
declare @query nvarchar(max)  
set @dbname = 'alrt'  
set @query ='SELECT OBJECT_NAME(object_id) AS DatabaseName, last_user_update,convert(nvarchar(max),last_user_seek),last_user_scan,last_user_lookup,last_system_update FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID ('+'''Frontoffrob'''+') AND OBJECT_ID=OBJECT_ID('+@dbname+')'
exec (@query)

错误结果

Msg 207, Level 16, State 1, Line 1 Invalid column name 'alrt'.

。
。
以下查询正在工作
SELECT OBJECT_NAME(object_id) AS DatabaseName,convert(nvarchar(max),last_user_scan)as SCAN FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID ('Frontoffrob') AND OBJECT_ID=OBJECT_ID ('ALRT')

输出：

+--数据库名称--+-----Last_user_SCAN----+
|__ALRT

Answer 1

在WHERE子句中使用以下内容：

'SELECT ... AND OBJECT_ID=OBJECT_ID('''+@dbname+''')'

实际上，您的查询应该是这样的：

set @query =
'SELECT 
    OBJECT_NAME(object_id) AS DatabaseName, 
    last_user_update,
    convert(nvarchar(max),last_user_seek),
    last_user_scan,
    last_user_lookup,
    last_system_update 
FROM sys.dm_db_index_usage_stats 
WHERE database_id = DB_ID (''Frontoffrob'') 
AND 
OBJECT_ID=OBJECT_ID(''' + @dbname+''')'